Generating a unique ID number for 3 tables

[Aiescer_Ibabao]

I have this code:

Sequence(1, 100000).FormulaMap(
RandomInteger(1000000000000000, 999999999999999999)).First()
.Filter
( Not
( ListCombine(
[View of Homeowners Insurance Survey].[Client ID],
[Clients (Manually Added)].[Client ID],
[Trusted Independent Insurance Agents].[Agent ID]
).Contains(CurrentValue)))

Error says= “Circular Reference”
Meaning I cannot scan current table for duplicates? I need to scan all three tables.

How do I make it so it also scans the current table for duplicates including the other two tables:

Also when I adjust the unique ID column width, it regenerates a new set of numbers for all tables. How do I stop that from happening when I adjust the column width?

[Pablo_DV]

Hi @Aiescer_Ibabao ,

That looks like a really expensive and complex formula for something simple as getting a unique id.

From your screenshot I see that the 3 tables are people, so I would just combine them into one and just use the built in rowID() function.

How this helps,

Pablo

[Aiescer_Ibabao]

Doesn’t rowID changes when a row is deleted?
I’m trying to avoid that from happening.

But with RandomInteger() a brand new set of numbers is generated everytime the column width is adjusted, so I need to find work around that.

[Pablo_DV]

@Aiescer_Ibabao

Doesn’t rowID changes when a row is deleted?

No it doesn’t. It stays the same.

You could also use this formula to get the true unique identifier of the row: split(thisRow+"","/").Last()

@Aiescer_Ibabao

But with RandomInteger() a brand new set of numbers is generated everytime the column width is adjusted, so I need to find work around that.

setting updateContinuosly=false will fix that

Nevertheless, I really would encourage you to consider combining the 3 tables into 1. In general the less tables you have, the better, even if you have some fields that are only relevant to some category of people (for example ‘home owners’) your future self will thank you.

Hope this helps,

Pablo

[Aiescer_Ibabao]

The first (client) table is fetched/synced from a survey using Typeform, I can’t change info or add to that table’s row, so I added a 2nd table for manually added clients. Then a third table for all agents to organize their information, check their leads etc. . .
Is there any way I can optimize my doc?

Also, is the true unique identifier unique across all tables currently exist and will exist on coda?

I first thought RowIDs reset. Thank you Pablo_DV.

[Pablo_DV]

I would do the following:

• Create a single table for basic ‘People’ data
  • Type field: ‘Client’, ‘Agent’, etc.
  • Source field: ‘Typeform’, ‘Manual’, etc. Default to ‘Manual’.
  • All the fields you need for the different types (only people data, other business objects should have their separate tables)
• Create an automation to add the clients from the Typeform table to your People table.
• Create a table for ‘Leads’
  • Agent field: Relation linking to ‘People’ but making only those with type ‘Agent’ selectable.
  • Client field: Same but with ‘Client’ types.
  • Any other fields you might need

@Aiescer_Ibabao

Also, is the true unique identifier unique across all tables currently exist and will exist on coda?

No, only within the table. For example if you duplicate a table these UIDs will also be duplicated.

But if you use the simpler formula thisRow+'' it will display the unique id of the table and the unique id of the row separated by a /. The whole string then really is a unique id for any row within a document.