I have a question that maybe someone can help...

[Phillip_Panfili]

I have a question that maybe someone can help me with.

I have various different tables; they are listed below. Main CapGown Obligations Prom

All of these tables are linked together on the Main table, by FullName column.

My question is, how would I go about making sure all the tables stay in sync?

In other words, if someone were to change a name in the Main table; what would be the best process to go about making sure the name changes in the other tables, so that all the tables stay in Sync?

Right now this is the major drawback I am having within my app.

I can’t figure out the best practice, to keep all tables in sync and connected.

I have tried doing things within the Google Sheet itself, but none of them work.

So now, if someone changes a name in the Main table, the other tables just lose the reference, and I have to manually go in and adjust them.

If I can automate this process, everything would be perfect.

[RezaRaoofi]

As you have realized by now, in a relational database you should not use Name or columns that can be changed in the future to set up relationship between tables.

Use a unique key column (e.g. UNIQUEID() function as initial value), then Ref column will use that, and it will solve you current issue.

For more details I recommend reading this article:

intercom.help - What is a Key? What is a Key? intercom.help

[RezaRaoofi]

Also you should avoid repeating columns like Full Name in all tables; I recommend reading this article too:

intercom.help - Data: The Essentials

Data: The Essentials intercom.help

[Phillip_Panfili]

@RezaRaoofi This is a great idea, but then it still leads me to my next problem.

Which is how to generate the unique key on its own?

So lets say I add the Column Unique to my table.

And then I cant use a formula to generate the ID, because I cant have formulas in the key column.

So I am back to where I started.

[RezaRaoofi]

@Phillip_Panfili Correct, but you do not have to enter UNIQUE() in App Formula, as I mentioned above you can enter it in Initial Value. You can even make it a hidden column to keep user interface clean.

[Phillip_Panfili]

@RezaRaoofi Fantastic,

I never used the Inital Value before.

Thank you.

But now that leads me to my next question, perhaps you can shed some light on.

Say someone adds a name to my Main table, how can I get that name, to transfer over to the secondary referenced tables?

Whats the best practice for something like that?

[RezaRaoofi]

@Phillip_Panfili you would use Dereference method.

Check this out:

https://help.appsheet.com/expressions/expression-types/dereference-expressions https://help.appsheet.com/expressions/expression-types/dereference-expressions