I have a parent table, each record of which represents a set of 'n', identicle Widgets (let's say). The value of 'n' is a numeric field within the parent table entitled 'Denominator', which determines how many widgets will ultimately be produced within the set. Over time, the widgets that go to make up the set will be produced, one at a time. Each time one of the widgets is completed, a user will need to add a child record to the parent record, representing a particular widget within the set. The widget will be uniquely identified by both a unique serial number and also its production sequence number within the set.
The end result I am trying to achieve is that when I add the first child record to a particular parent set record, a numeric field is set to 1 within the child record. When a second child record is added for the same parent set record, the numeric field within the second child record will incremented by one to 2 and so on, with child record No. 3 up to No. 'n'.
If possible, I also want to prevent the entry of more child records than is defined by 'n' within the parent record, but that is almost a side issue, for which I think I may already have one or two approaches that could possibly provide a solution, once I have cracked the first problem.
My main problem is knowing how to approach the creation of this child count field within the child records?
So far, my efforts in trying to achieve this at the table level, with auto-increment fields, equation fields and/or using a child 'Count' field within the parent record have failed, because each time the child record count increases, the Count field within parent record goes up and the formulas within the previously created child record fields then re-calculate, using the new Count total within the parent!
I just re-visited this post to update it, having solved the problem for myself, before seeing your comment! I'd decided to park the problem and configured the the child record count field as a manual entry number. To assist the user, I'd set the Display Field on the Parent table to include the child record count, so the user could work out the next number they should enter for Widget number within the set. However, after having done this, I suddently realised my previous mistake had been not to take advantage of the Form Record Rules facility on the Add page. Exactly as you suggested, I reinstated my formula field on the parent, which I'd called Next Set No. This stores the result of the child record count + 1. I then set up a Record Rule on the Child Add Form, so that for every new record added, it would update the Set No. numeric field on the child record to the value of Next Set No. from the parent. This way, the value of Set No, on the child record becomes isolated from any future changes to the child count on the parent, which was previously the problem when I was trying to acheive this solely at the table level, using equation fields involving the child record count. Many thanks for your response, which endorsed for me the fact that this was the correct way to go!
Your approach using a Count of child records in the parent is the right way to go. The only thing you should change in your method of operation, is the formula calculating the ID on the child. Instead of calculating the formula at the chid level, add a formula at the parent level, calculating the current count plus 1. Then, when creating the next child, set this child's id to the parent calculated field. Doing so, will not link a child record to future changes in the current count of children.
I had a tough time deciphering what was going on here in this solution, so I thought I’d chime in with what I did to create child numbering based on parent record. In my case I’m counting the number of change orders related to a job#. Please let me know if you have any feedback on this solution.
Change Order table fields:
CO# = Number Field
Job table fields:
Max CO# = SUM MAX = Change Order (Job)>CO#
Next CO# = Equation Field = {Max CO#}+1
Add Change Order Form Record Rule
Update this record
CO# is blank
CO# to a connected value Jobs>Next CO#
Change Order record ID Text Formula
CO-{Change Order Job.Job#}-{CO#}-{CO Name}
Result where the Job # is 555
CO-555-1-Change Something
CO-555-2-Change Something Else