Cliffnotes- need to keep 2 tables a pure 1:1 relationship using a key (user name preferred) knowing that the 1st table changing is due to external influence and the app itself will not add nor delete any rows.
Looking for some advice for a simple overall schema. We are starting with a "user" table that is generated from a CRM. Eventually we would like to see our app sync directly with said CRM's database so, for today, I am setting the app up to use a sheet to simulate this (for future ease if/when change is made to point directly to CRM). The app itself is basically meant to add a bunch of semi-pertinent attributes (lets call these "details") to each user (using them to determine suitability, etc). These details are only being used by the app and actually have a life span thus they will never need to be retained long term. The app will not add nor delete any rows. So the subsequent "details" table is created in a Sheet with a direct 1:1 with the user table, with only needing a key value to connect the 2 tables. The obvious choice is user name or user email. As I learn more about AS and understanding the importance of the key, I may become more comfortable but let's be honest, using a key of user name is very readable/friendly. It also makes the use of system generated View Ref (Name) more functional (this is always the key, no?)
The catch here is that the user table may change over time and as it is regenerated, it is always sorted alphabetically. Keeping the details table in sync to always have a 1:1 matching row with the user table is easy from a Sheets perspective, via use of a formula in details (=UsersTable!A:A) referencing back to users (via the user name as the key). Thus my dilemma in AS as the key can not contain a formula.
In summary, I am guessing the solution to my dilemma is to just introduce a unique key as a new column and then I am free to use my formula(s) at will and thus, I will need to learn the various nuances to deal with various functions (ORDERBY(), etc) that require the key. With that being said (and please do correct where I'm mistaken) - are there any other methods to accomplish maintaining a 1:1 users:details relationship, remembering that the app itself, will not add nor delete any rows?
LinkedIn
Twitter
