Because I would like to be able to use either to identify the device an event is related to I built relationships between both the Asset Tag and Serial Number fields.
This doesn't seem to be working as I expected. When creating a new event for a device I input its Asset Tag number, but the serial number does not come over from the Devices Table.
How is the input field in the Event table set up? How does it try to look things up from the Devices table?
More importantly, are your serial numbers and/or asset tags absolutely unique?
I would use a hidden primary key in your devices table as the identifier, and just display the related serial # & asset tag as a related field through the relationship. Setting up two primary keys is confusing and easy to break, especially when some records don't contain a value (by definition, making that field no longer a primary key).
This was also my inclination. I've struggled with getting tables to communicate at times. Without more details, I would point you toward field lookup calculations: Defining lookups. Throw in a Window Refresh trigger if you're not seeing the update. I've also tried this data sharing technique that works well: CampSoftware | FileMaker Related Field Updater / Storing Unstored Fields
Concur. As a general principle, it's a bad idea to allow users access to edit relational keys. Make the key something only you can control.
It was set up with a one to many relationship between both Asset Tag and Serial Number fields. Both should have only unique values. The Device Table was the primary key and Device Events was the Foreign.
Based on advice here and elsewhere, I restructured the relationships to include a DeviceID field that could be used to link devices to events independently of the Asset Tag or Serial Number.
Now I just need to learn more about scripting to be able to make it work with this new arrangement.
Thanks for the input. I took a look at the info on Lookups compared to relationships. What would be the advantage of a lookup in this scenario? I can clearly see the advantage in an invoicing situation where prices might change over time. Would using a lookup in this scenario limit the potential for damage to the data from user error or is there something else I'm missing?
Assuming your Asset Tags are a separate table, then use a linking file with its own UUID.
Asset Tag File ID
Item File Serial Number
The Linking file becomes the control file and works with both values or only one value.
It can also link to just one file in the same way and solve your problem, mostly.
IF your item file has both values entered into it, the use a UUID of the record for your identifier and not the two fields.