LinkedIn
Twitter
As I use more of the appsheet capabilities, the one thing I seem to struggle with a lot is properly setting up table-to-table references, especially when I have a table key that is not the label. My most recent example is as follows, and I am wondering if you can spot what I am getting wrongโฆ
Although my app has a lot more tables than listed, below is the primary setup:
Table: ACTIVITIES
key = [RecordID]
label = [Title]
Table: ACTION ITEMS
key = [ActionID] (with REF to Activities table and Formula = [RecordID])
label = [ActionTitle]
I then added a table called ACCOUNT CARDS. The Account column in the ACTIVITIES table used be an ENUM entry, and now I wanted to add and actual ACCOUNT CARDS table as I need additional data linked to the ACCOUNT CARDS record. So I added the table and hoped appsheet would set up the appropriate key, label, and REF to the ACTIVITIES table, but that did not happen.
Table: ACCOUNT CARDS
key = [RecordKey ]
label = [Account]
I now have the tables and slices and views in place for ACCOUNTS, but in my ACTIVITIES view, grouped by Account, shows a reference problem and I am not sure how to solve this. Looks like this:
If I go to edit the Account field I can select a value from the ENUM list, but the field gets filled with key value (BB0D9C71) instead of the Account ENUM (which is set as the label). So the relationship seems right, but the value saved to the field is wrong. If I try to make the [Account] column the key, it wonโt let me and defaults back to the [RecordKey] field.
ACTIVITIES Detail View:
Resulting Sheet Entry: (key being entered instead of label, which I assume is why I am getting the yellow triangle reference warning)
I donโt know if the ACCOUNT CARDS table needs a REF type virtual column back to the ACTIVITIES table, or I have a problem with the ACCOUNT CARDS table key and label columns.
I hope I am explaining this clearly. I keep re-reading the appsheet help documents, but canโt seem to detect what I am doing wrong. Any help or pointing to examples appreciated.
Solved
1 ACCEPTED SOLUTION
Just thought I would share the conclusion to this story in case it prevents others from making the same silly mistakes I do!
Symptom: Child records showing yellow triangle warning for key column [Account].
Cause: I was using a google sheet array formula to calculate a combined record key that I wanted to also use as the label. On table regeneration, that formula would get written as a Spreadsheet formula in the appsheet column. Appsheet would then detect that this formula could be re-calculated, resulting in a change to the record key โ a big no-no! Since this risks breaking parent/child relationships, appsheet flags this as an error and tries to prevent me from using this as the key.
Solution:
-
Remove the array formula from gsheet that was calculating the combined key. (cut and paste Values Only to maintain current record relationships.
-
Set [Account] initial value to the calculated key I actually wanted. In this case:
[Entity]&" / "&[Function]&IF(ISBLANK([Area]),""," / "&[Area]))
note: Spreadsheet Formula property must be empty. -
To ensure a unique key field, I added a Valid IF condition just for safety.
NOT(IN([_THIS], SELECT(Account Cards[Account], NOT(IN([Account], LIST([_THISROW].[Account])))))) -
Set [Account] column as Type = Text, Key = Y, Editable = NO, Label = Y, Require = Y
-
Create virtual column with REF_ROWS formula to provide related records list in the views.
[Related Activities] Type = LIST, with REF_ROWS("Activities", "Account")as the formula.
note: I could have also added a proper unique key column in the gsheet (like AccountID), but I needed the gsheet records to show a human readable Account (not just random digits) and also be the key.
Now all is good again in AppsheetVille!
Soโฆ new rule for meโฆ If you need a calculated key - do it in Appsheet and not gSheets. Hope this helps someone else prevent a little pain. 
