Ref Column Value becomes NULL in SQL Server after Sync (when added via default +Add, Is Part Of? = FALSE)

[pbalerio]

Hello AppSheet Community,

I’ve encountered a strange issue when adding related records using the default +Add button associated with a Ref_Rows virtual column, specifically with a SQL Server data source. I hope someone might have insights or confirm if this is a known issue.

Setup:

• Parent Table: Avances de contrato (Contract Advances) - Key: Row_ID (nvarchar(50))
• Child Table: Multas por avance (Penalties/Discounts)
• Relationship: Multas por avance has a column [Avance] of type Ref pointing to Avances de contrato.
• Database: Microsoft SQL Server (Azure SQL in my case, Foreign Keys were removed for testing).
• Column Type: Both Avances de contrato.[Row_ID] (Key) and Multas por avance.[Avance] (Ref column in AppSheet, FK column in SQL) are defined as nvarchar(50) in the SQL database.
• Configuration:
  • The [Avance] Ref column in Multas por avance has Is Part Of? = FALSE.
  • Reset on edit? = FALSE.
  • No Initial Value, App Formula, Spreadsheet Formula, Valid_If, or Suggested Value is set for the [Avance] column.
  • No Security Filter is applied to the Multas por avance table.
  • No Triggers exist on the Multas_por_avance table in the SQL database.
  • My plan is Enterprise plus

Problem:

1. From the Detail view of a parent Avances de contrato record, I click the default +Add button (associated with the Related Multas por avance Ref_Rows column) to add a new child Multas por avance record.
2. The form for Multas por avance opens correctly. Crucially, the Label of the parent Avances de contrato record is correctly displayed in the [Avance] Ref field, indicating AppSheet knows the parent context.
3. I fill in other details and click “Save”.
4. Locally (before sync), the app behaves correctly: the form closes, and the newly added Multas por avance record appears in the related list within the parent Avances de contrato detail view. The pending sync indicator appears.
5. The app synchronizes, and the sync history reports “Success”.
6. Issue: A few seconds after the sync completes, the newly added Multas por avance record disappears from the related list in the parent detail view.
7. Checking the SQL Server database directly, the row was inserted into the Multas_por_avance table, but the [Avance] column (the foreign key reference) is NULL.

Workaround Found:

If I change the setting Is Part Of? to TRUE @the definition of the [Avance]column, within AppSheet, the problem disappears. This means, adding a new record using the same default +Add button, the [Avance] column value is correctly recorded, with the parent’s key (Row_ID) in the SQL Server database after synchronization.

Question:

Why does the reference value fail to persist to the SQL Server database (resulting in NULL) when using the +Add action for a relationship where Is Part Of? = FALSE, even though the reference is correctly identified locally and the sync reports success? Setting Is Part Of? = TRUE forces the correct behavior, which suggests a potential inconsistency or bug in how AppSheet handles persisting non-“part of” Ref column values via the default add mechanism specifically with the SQL Server connector.

Has anyone else experienced similar behavior with SQL Server? Is this a known limitation or a potential bug?

PS: I did not try to unmark is part of? (did not go back)

Thanks for any insights!

[Shourya_Tomar]

Hello @pbalerio,

Welcome to the community! My recommendation is to get in touch with the support team.

To do so, click on this link, scroll down to the bottom of the page and you will find the link to Contact us and go through the following steps:

Step 1: What can we help with?
Step 2: Resources
Step 3: Contact Options

Once you reach the third step, you will be given the option to “Chat” with a team member or “Email” them.

Our support specialists can take a closer look into the issue and help you resolve the problem.

I hope this helps!

[pbalerio]

Thank you @Shourya_Tomar.

I will do that.