New Bug Encountered: Foreign key not populated when ref row is added

[mchaston1]

Hello,

I have created a simple app that has a “Meals” table in Google Sheets and an “Additions” table in a second tab in the same sheet. The “Additions” table has its own Key column which is initialized with UNIQUEID() and a “Meal” Ref column that refers to the “Meals” table. Note that the “Meal” Ref column is not shown.

When using the unpublished app, when I use the “Add” link to add a new “Addition” to a “Meal”, more often than not, the new “Addition” appears in the list of referenced rows, but when I press the sync button, the row vanishes. Looking at the data, the “Meal” column is not populated.

Any ideas why this may be happening and how to fix it?

Thanks

Miles

[Derek3]

Hi Miles,

Do you have “is a part of” turned on for the “Meal” Ref column in the “Additions” table?

When you turn “is a part of” on, it allows you to add new line items to that table (“Additions” table in this case) while completing the referenced form (“Meals” table in this case).

From inside the form view for “Meals”, when you click “Add”, it will bring you to the form view for the “Additions” table. After completing the “Additions” form, you click “Save” and it brings you back to the “Meals” form. From there, you can click “Add” again or continue with the rest of the form. At the end of the “Meals” form, you click “Save” and the changes will sync back to the data source.

Important: The items you add to the “Additions” table will not be synced back to the data source until you click “Save” on the parent table’s form (“Meals” in this case).

So my first guess is that you may be pressing the sync button before completing the “Meals” form.

Another good thing to check is the referenced table for your “Meals” Ref column.
*Go to Data > Columns and click to expand the “Additions” table.
*Click the edit pencil next to the “Meals” column
*Check that “Source table” is set to “Meals”

Cheers,
Derek

[mchaston1]

Hi Derek,

Thanks for the prompt suggestions.

Populating the “Meal” column in the “Additions” table used to work, but it does not seem to work now. I have now enabled the “is a part of” setting for the “Additions” table and confirmed that the “Meal” Ref column has its source set to “Meal”, but this still does not seem to work.

Might it matter that I have manually edited the sheet’s data (i.e. does AppSheet try and remember state about the underlying spreadsheet and it may be getting confused)?

Miles

[Steve]

mchaston:

Might it matter that I have manually edited the sheet’s data (i.e. does AppSheet try and remember state about the underlying spreadsheet and it may be getting confused)?

Unlikely.

Please post screenshots of the column lists (from Data >> Columns in the app editor) for the two tables. Let’s start there.

[mchaston1]

Attached are some images for you to look at.
As I am a new user this will take three posts…
Might it make sense to share the app with you and Derek?

Meals columns:

[mchaston1]

Additions columns:

[mchaston1]

Meal Ref column details:

[Steve]

How are you adding an Additions row when it doesn’t work as expected?

[mchaston1]

Yes. Using the “Add” link under “Related Additions”. The sequence is:

1. Select a Meal
2. Click the “Add” link.
3. Specify a name for the Addition and press “Save”. This returns me to the Meal.
4. The Addition shows up in the list of “Related Additions”, but not in the Google Sheet.
5. Press the sync (top right) button.
6. When the Meal refreshes, the new entry is not in the “Related Additions” list, but there is a row in the spreadsheet, but its Meal value is not populated.

An additional possibly important point:
The “Meal” sheet is in the same Google Sheet as the “Additions” sheet, but a different tab.

[Steve]

Please post a screenshot of the complete App formula expression for the Related Additions column.

mchaston:

The “Meal” sheet is in the same Google Sheet as the “Additions” sheet, but a different tab.

That’s typical. Not a problem.

[mchaston1]

App formula expression for the Related Additions column:

[Steve]

That looks correct.

Is there a Spreadsheet formula configured for the Meal column in the Additions table?

Is Editable? set to ON for the Meal column in the Additions table?

Have you added any actions (via Behavior >> Actions) to your app? Or made changes to any of the system-generated actions?

Do you have any workflows (Behavior >> Workflows) or reports (Behavior >> Reports)?

[mchaston1]

Steve:

Is there a Spreadsheet formula configured for the Meal column in the Additions table?

There is no “formula” values for any field in the “Additions” table.
The “Key” column has an initial value formula of UNIQUEID().

Steve:

Is Editable? set to ON for the Meal column in the Additions table?

Ah ha! That was it. I set Editable? to ON and it started working!
I probably turned this off at some point, because I did not want an “Addition” to move between “Meals” (before I set Is a part of? to ON).

Steve:

Have you added any actions (via Behavior >> Actions ) to your app? Or made changes to any of the system-generated actions?

None added.

Steve:

Do you have any workflows ( Behavior >> Workflows ) or reports ( Behavior >> Reports )?

None added.

Thanks for all of your help Steve.

How would a developer identify this issue without this kind of support?

Miles

[Derek3]

I’m glad you were able to find the culprit. Steve is in fact an AppSheet Jedi!

There is a team of people whose goal is to make the AppSheet learning curve as easy as possible, but sometimes you’ll find things like this that take some experimentation.

Everyone has their favorite resource. I always like to start from a working example of something similar. For that, there is a collection of free sample apps you can copy to your account. I’ll often compare what I’m working on to the sample app and try to find the differences. And of course your community is here as resource as well.