AnsweredAssumed Answered

Enforce One-to-One Relationship

Question asked by user26705 on Apr 23, 2016
Latest reply on Apr 24, 2016 by user26705

I have a parent table with several child tables all related by DeviceID.  I would like to enforce that for each parent there is only one child record in each table.  For example if I have Device001 in the parent, then the child will only have Device001. 


Before you refer me to create a unique index on the child...which I can issue is that it is not very user friendly.  I don't want the user to ever get an error stating that they tried to enter a record that already exists.  I would prefer to only allow the Layout to perform the following logic at the layout level:


When the layout opens...


IF a record exists for the DeviceID show that record and allow the user to modify it but not delete it.


IF a record does not exist for the Device ID - then allow the user to enter a new record.


The user should never browse the records or even know that there are other records for other Device ID.


I have been playing with the "Layout_OnEnter" trigger - but can't seem to figure out how to get the DeviceID from the parent table and then enforce the above logic.


Hopefully this made sense and I appreciate any guidance on how-to make this happen or pointers to samples of this working.