Auto-creating/updating sequentially numbered records based on field contents

[btmeacham]

I'm trying to automate the creation of records in a table based on data from a related table.

 

In this database, each Component has any number of Carriers. The Component has a unique number, six digits followed by a dash and an additional digit, such as 123456-1. In the Component table, the Carriers field is set to hold the number of Carriers, as entered by the user. Once this field is completed, I would like the script to automatically create a sequentially numbered set of records for the Carriers in the Carriers table. The Carrier numbering scheme is based on the Component number, and reflects the number of Carriers entered in the Carriers field in the Component table. For example, if the Carrier were numbered 123456-1 and I entered "6" in the Carriers field in the Component table, 6 Carrier records, numbered 123456-1-1, 123456-1-2...123456-1-6 would be created.

 

I think I have the basic idea for how to script this, but I'm stuck. I know I need to set variables for the Component number and the number of Carriers in the Component table, then move to the Carriers table and create the number of records to match the $$CarriersNumber variable I set, but I'm having trouble figuring out how to do this.

 

As an additional requirement, I need the number of Carrier records to maintain its connection to the value of the Carriers field in the Component record. If the user enters 6 and the script creates the records, but the user later learns there are actually 7 (or 5) Carriers, I'd like the script to be able to create or delete the related record as it goes. This probably won't happen often, but if it does, the script's ability to maintain the relationship between the Carrier field data and the number of associated Carrier records would be useful.

 

Thanks for any help with this challenge.

[eos]

Use the carrierID as parentID in the Components table, and employ an additional incremental field. This way all Component records keep the connection to their Carrier parent, which allows you to simply count them; to display the complete ComponentID. just create a calc field that concatenates both fields as a string in the desired format.

 

Now in your script capture the carrier_pk and the number of new records in two variables ($, not $$); use a loop to create the new records, where each new record receives the carrierID as a foreign key and the current loop counter as value in the increment field. Drop out of the loop when your increment counter = number of desired new records.

 

As for your additional requirement: deleting should not be a problem (e.g. in a portal, since you can create a relationship via carrierID pk to fk); adding additional components means that your script needs to work with an offset to calculate the increment value; i.e. the increment max or the count (depending on your business logic) of the existing components for the carrier.

[btmeacham]

Thanks for the help. This all makes sense, and I'm seeing if I can get it to work. I have a few questions (and I think my Components vs. Carriers terminology might have gotten flipped; the Component is the parent, with multiple Carriers underneath it, in this setup):

 

I've captured the Carrier_pk and the number of new records desired as two variables. I have a loop set up to create a new record populated with the Carrier_pk data. I've also created an increment field, and have set the loop to stop when the increment field value = the number of Carriers specified in the Component record.  Can you show me what you mean about the next step, where each new record receives the "current loop counter" in the increment field? I think I'm close, but I haven't figured this bit out. Thanks.

[eos]

and I think my Components vs. Carriers terminology might have gotten flipped; the Component is the parent, with multiple Carriers underneath it

 

Now I'm confused … but never mind, once you know how this works, you can easily adapt it to whatever configuration you actually have.

AddAndIncrement_eos.fp7.zip

[btmeacham]

Thanks very much. I'll check out your example.

[btmeacham]

Thanks again. Your example was invaluable, and I've got the setup working just the way I'd intended. Thanks so much for your help.

[btmeacham]

One more quick question, eos, if you have a second: I've integrated this solution into my database, and it's all be going swimmingly, but I ran into a hiccup this morning: for components with only one carrier, the script gets stuck in a loop and continues generating new carrier record after new carrier record until you hit escape to stop the script. I've fiddled around a little but can't find a solution that allows for the creation of just one carrier.
 
Here's the script as it is now:

#make sure it's not empty, and a number
#a size check may be in order, too
If [ IsEmpty ( Component::Carriers ) or IsEmpty ( GetAsNumber ( Component::Carriers ) ) ]
Show Custom Dialog [ Title: "No number specified"; Message: "You need to specify a valid number of new components to create";
Default Button: “OK”, Commit: “Yes” ]
Exit Script [ ]
End If
#Really add?
If [ IsValid ( Carrier::ComponentID_fk ) ]
Show Custom Dialog [ Title: "Add new components?"; Message: "Add new components to the existing ones and increment?";
Default Button: “Yes” , Commit: “Yes” ; Button 2: “Cancel” , Commit: “No” ]
If [ Get ( LastMessageChoice ) = 2 ]
Exit Script [ ]
End If
End If
#
Set Variable [ $carrierID ; Value:Component::ComponentID_pk ]
#offset is either zero or the number of already existing components for this carrier
Set Variable [ $offset ; Value:Count ( Carrier::ComponentID_fk ) ]
Go to Layout [ “Carrier” (Carrier) ]
Loop
Set Variable [ $counter ; Value:$counter + 1 ]
Set Variable [ $newcarriers; Value:Component::Carriers ]
New Record/Request
Set Field [ Carrier::ComponentID_fk ; $carrierID ]
Set Field [ Carrier::Increment ; $counter + $offset ]
Exit Loop If [ $counter = $newcarriers ]
End Loop
Go to Layout [ original layout ]

Thanks again for your help. I demo'ed the database to my colleagues yesterday and got a round of applause (this quirk notwithstanding). The fact that it works at all is mostly due to your help, which I very much appreciate.
 

[eos]

Thanks again for your help. I demo'ed the database to my colleagues yesterday and got a round of applause (this quirk notwithstanding). The fact that it works at all is mostly due to your help, which I very much appreciate.

 

You're welcome. Glad your colleagues liked it (and are easily impressible …) 

 

I'm having a bit trouble getting back into that scenario. Why don't you just post your file (with some sanitized sample data) and let me look at it? I can see some rough edges in your script, but no infiniteloopiness, at least none that would only hit for one new record, so there must be another factor.

[btmeacham]

Thanks. I've uploaded a stripped-down database with the Title, Component, and Carrier tables. I have a few sample records in there, and the buttons for "Add a component" and "Add Carriers" work - except when you've specified "1" as the number of carriers, at which point it goes into infinite loop. Thanks for taking a look.

Database_Sample.zip

[eos]

No problem. Please read my comments in the pertinent script.

 

btw, if you're developing (even semi-)seriously, I recommend getting FM Advanced; Debugger and Data Viewer are simply invaluable.

Database_Sample_corrected.fmp12.zip

[btmeacham]

Thanks! That did the trick, of course. And I'll see what I can do about getting Advanced installed. I didn't realize I'd be getting this serious, this quickly, but now it seems I have, and I could use the help. Thanks again.