Forum Discussion
Yes - the general idea is that you create a formula-text field in your child table that builds the presumptive identifier and then on the form you use a form rule that pushes the value of your formula into the key field. So your formula would be something like:
[Position Number] & "-" & Year(Today()) & "-" & ([Current Year Children] + 1)
The missing part is that you'll need to create a summary field between your parent and child that counts the # of records created this year so far. You then add that back as a lookup field into your child. So lets say in 2023, you've created (2) children so far that exist in QB. When you make this formula then - the formula would have the value of 2 represented by [Current Year Children] - so add 1 to that in your string and you get 3.
Then in your form rule you essentially make a rule that says when Key Field does not equal your formula and Date Created is blank - change the value of Key field to the formula. Uncheck the box that says to fire only on change and it keeps changing if you reset the parent as a failsafe. You need the date created piece to make sure that it doesn't try resetting once you've saved.
------------------------------
Chayce Duncan
------------------------------