Solved: Forcing one-to-one database relationships - Page 2

candicepelser · 12-02-2019 05:18 AM

Hi There,

Does Appsheet have an in-built elegant way to enforce a one-to-one data relationship constraint (to follow the database design)?

I have searched long and hard and it seems people are hacking a solution by setting the Foreign Key field in the child table (Appsheet settings) as the Primary Key for that table.

Works, but seems inelegant with possible confusion re table design down the line.
Is that the only way?

We also tried data validations -
This one worked for creation of new records:
NOT(IN([_THIS],dbo.tablename[tablename_id]))
but unfortunately it also prevents you from editing an existing record of course…

Perhaps someone else has a cracked a better data validation formula that can also work on editing a record?

Or I’ve missed the right way to enforce database table relationships in Appsheet?

Many Thanks!

Suvrutt_Gurjar

Hi @candicepelser,

As per my understanding, your valid_if approach is correct and just that you are facing a problem that you cannot edit the existing child record due to valid_if expression you are using.

The expression suggested in the article below removes the existing record from the valid_if constraint and thus it allows the existing record to be edited for other values except adding another child record with the same parent key.

Please use the expression suggested in the article below under section " Preventing Duplicate Field Values" and use the exression suggested in the valid_if of the reference column that references parent table.

I tested it on a small test app and it works. Hope its helps.

View solution in original post