References, dependencies, and duplicates issue.

[BaselMakhoul]

Hello everyone,

I have an AppSheet DB for RFQs and Offers Comparison.

DB has 5 tables, with many dependencies (Ref Type column).

• The common problems I am countering are:

1. I want the RFQ_No column in RFQ_Items table to not show doplications from RFQ_No column in RFQ_Details tables as the 1st is a reference to the second.
2. I want the RFQ_Rev_No column in RFQ_Items table to only show numbers related to the previously chosen RFQ_No Column in RFQ_Items. RFQ_Rev_No is also a Ref type to the same column name in RFQ_Details table.

• Solutions I tried:

1. Virtual Column: but it is not editable as it gets its value from a formula
2. Slices: my AppSheet does not show this option and I still does not know why.
3. Formulas like (Unique, Filter, Display Name) but does not work. 

Please help with this issue

[Gustavo_Eduardo]

Have you tried entering the expression in the valid if field of the RFQ_items field?:

SELECT(RFQ_Details[RFQ_No], TRUE, TRUE)

The last true is to ensure unique values.

In the valid if of RFQ_Rev_No

SELECT(RFQ_Details[RFQ_Rev_No], [RFQ_No] = [_THISROW].[RFQ_No])

[BaselMakhoul]

Thank you, Gustavo. It works perfectly, but I keep getting this validation warning (yellow triangle) beside the RFQ_No, and when finishing the data entry, it gives an error message that it has failed to sync with the DB. If you could please advise with this, I would be really grateful.

[Gustavo_Eduardo]

The issue could be due to two main aspects:

1. The yellow warning (⚠️):

This icon usually appears when the selected value in the dropdown is not found in the data source at that moment.

Another thing that occurs to me is that you, by placing a select, force a result that is not being found in the table referenced in the column. Choose the correct reference and the problem will disappear instantly.

Check References Documentation

Possible causes:

• Your SELECT() expression is not returning values. Check if SELECT(RFQ_Details[RFQ_No], TRUE, TRUE) actually retrieves data.
• The RFQ_No field in the RFQ_Items table is not of type Ref. Ensure that RFQ_No in RFQ_Items is set as a Ref to RFQ_Details.
• The data source is not synced. Try manually syncing before entering data.

2. Sync failure with the database:

This might happen because the data being saved is invalid based on the database structure.

Try checking the following:

• If RFQ_No is a required field in the database, make sure it is never blank before saving.
• Verify that RFQ_No in RFQ_Details has unique values and is correctly related to RFQ_Items.
• If using AppSheet Database instead of Google Sheets, ensure that the RFQ_No column in RFQ_Items has the same data type configuration as in RFQ_Details.

Try these steps and let me know if the issue persists. 

[BaselMakhoul]

I have checked it all, everything is set. May I share with you the DB and the AppSheet.

[Gustavo_Eduardo]

Yes, absolutely. Or, if you prefer, create a copy of your app and grant me access to that copy so we don’t touch the original app. Please send me a private message so I can share the email with you.

[BaselMakhoul]

<PII Removed by Staff>
this is my email, please send me a message to recognize yours.
Unfortunately, I could not find a way to send you privately.

[Gustavo_Eduardo]

Emails are not allowed on this thread. You need to do it privately. Anyway I try to send you a message and I don't know why it won't let me! What a weird thing

The ref type column (where you display values) must refer to the table that contains the records you want to display. Also make sure that it points to the id and that the label is the value you want to display in the dropdown.

Make sure that the select() is also bringing values ​​referring to that table.

[BaselMakhoul]

I am a new user and not aware of this rule. sorry.

I do not know how to send a private message on this community.
However, I will try what you have mentioned and let you know.

[Gustavo_Eduardo · Accepted solution]

Imagine that in your app you are looking for records from a table.

If the reference is to a table 1 but the formula returns a column that is not the id of that table then the warning triangle appears.

For example, look at the following table:

If in the formula you call a field of the table with SELECT() but it is not an id of the table, (for example in this case let's suppose that you call the label) with SELECT( Table[label], true, true) then a yellow triangle will appear because although it finds records, no label matches the row id. To solve the problem, SELECT( Table[id], true)

Now, if you want to restrict the selection to unique records, you'd better create a slice that brings the unique records. How do you do that?

Well, you create a slice by tapping the + button next to the table in the editor. As the slice formula you write the following:

[id] = MINROW("Table","_RowNumber", condition)

Then in the valid if you could point, instead of the table, to the slice and this will bring the filtered records to be selected.

[BaselMakhoul]

Thank you, Gustavo, it worked finally, I used this statement:
"IN([_THIS], SELECT(RFQ_Items[RFQ_Item_ID], [RFQ_No_RFQ_Rev] = [_THISROW].[RFQ_No_RFQ_Rev]))"

[Gustavo_Eduardo]

I'm glad! Great!