I can't seem to figure this one out...
My records have a field with a text/number in them starting with the year, such as: 2017-Foo01297 They autogenerate and increment by 1.
This is the key field of the main table. In a related file, this unique record from the main table can be activated more than once (in other words, many to one relationship). Therefor I would like a field in this related table that adds run numbers to the end of the main table's identifier, such as: 2017-Foo01297-001, and 2017-Foo01297-002. The next record in the main database would be: 2017-Foo01298 and therefore the key field field in the related table would be 2017-Foo01298-001 followed by 2017-Foo01298-002. These records will always be created with a script from the main table (through the use of a button and visible in a portal).
I was trying to use the serial number auto-enter along with the field information from the current tables record but it will not let me use the field in that calculation. I'm stumped.
Any help is appreciated. Thanks.