I need to script creation of multiple child records based on data in a key field entered in the parent record. I have thought this through and have a strategy that I know will work but just wondering if anybody knows a better way.
Here are the parameters:
- Child records are options related to the parent.
- A key field (not primary key) in the parent determines which options relate to the parent (simple 1 to many relationship)
- There can be many related options, some are mandatory and some are not.
- By scripting creation of mandatory records I can force the user to enter the required data.
- The user needs to be able to also select additional non-mandatory options.
- I don't want to hard code which options are mandatory for which matching key as this will be a pain to maintain later when options change.
So here is my current plan (better plans welcome :-):
- Create a mandatory option table with only 3 fields, 1) ID (not necessary here but habit), 2) ID_ParentKey, 3) ID_Manditory_Option.
- Populate this table to enable relating ID_ParentKey to as many ID_Mandatory_Option records as necessary.
- Create a relationship: Parent::ID_ParentKey = MandatoryOptions::ID_ParentKey
- Create a value list of ID_Mandatory_Option from this new table showing only related records from the Parent.
- Use ValueListItems ( fileName ; valueList ) to set a $ScriptVariable and then count, loop, subtract count, create child records, etc. via a script triggered by the ID_ParentKey field in the parent record. I anticipate having to do this on an off screen window. (and set the parent record primary key in the child records of course)
- Show the new records in a portal on the parent and allow creation/deletion of child records, but script the delete button to halt or hide if the child record ID_Mandatory_Option matches the mandatory value list items.
Is that about as streamlined as I am going to get? I contemplated using magickey to create the child records instead of a different window and may still go that route, still by the value list and script though.