Forcing one-to-one database relationships

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!

So you’re trying to make it so you can only create 1 subtable entry?

Yes

I’ve done this for relating Google Form responses to data in another table, where I wanted to ensure a 1 to 1 relationship.

Try using a workflow to add a record to the second table, using the same key as the first table. Don’t allow the users to add records to the second table. Then every new record in table 1 will create a matching record in table 2.

[EDIT] Forgot to mention–
…then create a virtual column for the Ref in Table 2. The Ref column should be in LIST format, but you only want the one record with a matching key.
LIST([Key]) or {[Key]}

So one way I have done something similar is to have 2 list columns pointing at the subtable

The top one only shows when [Related Tables] is blank. It basically is the default and just points at the entire subtable.

The bottom list column only shows when [Related Tables] is not blank. Rather than pointing at the table, it points to a slice set to allow Updates Only

Just name both list columns the same and it looks good to the user. But doesn’t really tick the elegant option you’re looking for. Hope it helps though

Simon@1minManager.com

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.

1 Like

So your valid_if expression in the reference column in child table will be something like

NOT(IN([_THIS], SELECT(Child Table [Reference Column], NOT(IN([Key Column of Child Table], LIST([_THISROW].[Key Column of Child Table]))))))

1 Like

Thanks@ 1minManager,
Appreciate the input.
As you mentioned however, it may work but isn’t elegant - which practically means there’s more complexity down the line.

Just a side note in generally… if two or more users are adding a child record at the same time, it will mess your one-to-one relationship because then you will have two child records for the parent.

1 Like

Thanks @Suvrutt_Gurjar , your suggestion is the most elegant & practical. With the added benefit that one can give the user guidance using the error message.

It is surprising that AppSheet - the appbuilder which is designed with the data model in mind… doesn’t have this as an in-built functionality.

Even better if Appsheet could just pick up such constraints from the Database itself - an obvious choice for future functionality to consider.

Very much appreciate your help!

1 Like

Thanks @Aleksi,

Good point. In that case… What do you suggest instead?

It depends what kind of process you have. Are you adding parent and child records at the same time or are you first adding the parent and after hours or days, you will add the child?

First the parent is added, then the child (could be a few minutes or many days later)

Who can add the child, doesn’t need to be the same user? It can be whoever?

Yes that’s correct. The real-world situation is this:

A Turtle Nest Monitor on patrol at night, spots a nesting turtle and records details of the Turtle & the Nest she lays…
Nest = Parent Record

Many days later the nest begins to hatch, and whichever Nest Monitor is on duty, will record the details of the Hatching (Child Record)

There can only ever be one Hatching record for a Nest record. And a hatching can only be captured after the Nest has been captured, because it is associated with that Nest.

[I know… turtle nesting app - what an awesome project to work on! :slight_smile: )

2 Likes

I would just set the behaviour expression of the add action to something like SUM([Related Hatchings][IDColumn]) < 1. This would remove the ability to add more child values effectively creating a 1 to 1 relationship. Since it seems like there would just be one nest monitor acting on a single nest site at a time I dont think there would be a conflict of two people adding a child at the same time.

1 Like

As Heino mentioned, it sounds that it’s probably not the issue. Though as a precaution you could create a scheduled report to send an email if duplicate records are added.

How many columns do you have in each table? If the tables aren’t too large, perhaps you could merge them, putting all data in a single row. Then use Slices to split the data into Nest/Hatchling.

You can also use a Virtual Column Ref to relate the record back to itself, and display the Hatchling slice as if it were a child record using this method:
https://community.appsheet.com/t/detail-views-space-saving-trick-to-display-columns-horizontally/14497

2 Likes

100% for it being in the same table for data capture. You could even run slices to split them out for different forms… and then you could even double back and use the add a row action feature to create the child record… (All without the user being the wiser.)

2 Likes

@GreenFlux @Grant_Stead

Making a child table part of the parent table - just to suit the app-builder, is a bad idea, if… a) you expect your data set to grow substantially and performance is going to become more and more important, and b) when you’re not sure the app-builder is going to serve you longer term as you grow…

From my 25 years of IT application building experience - breaking good database design principles is a recipe for suffering down the line.

@Aleksi - Appsheet has a great proposition in the market of app-builders, it seems to be unique in following a sort of ‘data-model-up’ design. Which saves a huge amount of time on app development!
I’m not sure what your long term strategy is over there at Appsheet, but this one seems like a no-brainer given AppSheet’s current design. Have the ability to set relationship constraints between tables in Appsheet - in the same way that one does in a DB.

Thanks again everyone for your time and support on this!