Hi Knack Community,
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!
Any assistance would be most gratefully received.