Table Key, Label, REF confusion

[Mike_A]

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.

[Reza2]

Yes, I believe if each Account can have one or more Activities, then in Activities table you will need a Ref column that refers to Account’s table. What is the column that has that warning triangle next to it? Is it already a Ref column? If yes, its current data from the past may not exist in the Accounts table.

[Mike_A]

Thanks @RezaRaoofi - I have a REF in the ACTIVITIES table that points to the ACCOUNT CARDS table. The warning triangle is next to the [Account] column from a slice that pulls from the ACTIVITIES table.

When I go to edit the [Account] column in a detail, the dropdown works (not sure why I am getting the image and the Account value yet), but selecting from the list returns the key rather than the label I defined. That is why I am trying to check if I am not setting up the key and label definitions correctly. Also, I manually corrected all the [Account] data in the two tables to be sure they matched.

[Reza2]

Why did you use Slice for the Ref? I am guessing the triangle might have to do with the Slice condition. If you need to show a selective list of the Referenced table, I would set the Ref column to the actual Accounts table, and then instead of Slice, use a filter in Valid_If expression.

As for stored value, always the key value of referenced table should be stored in underlying table; so this behavior is correct.

[Mike_A]

Thanks @Reza. That makes more sense to me. Let me check the layout and see if I can resolve it.

[Mike_A]

@Reza - So… I was mistaken. The [Account] column in the ACTIVITIES table is a REF type, that points to the ACCOUNT CARDS table (not the slice). So that’s correct.

But… the views with the yellow triangle point to a slice that I was using to only show only “active” [Accounts]. I changed the view to point to the ACTIVITIES table instead of the slice, and I still get error triangles on the [Account] field. So… it does not seem to be caused by the slice.

Will keep looking for the cause…

[Mike_A · 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:

1. Remove the array formula from gsheet that was calculating the combined key. (cut and paste Values Only to maintain current record relationships.

2. 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.

3. 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]))))))

4. Set [Account] column as Type = Text, Key = Y, Editable = NO, Label = Y, Require = Y

5. 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.