Create multiple records based on selected options?

[lightningad]

I have been trying to find an answer to my problem for ages and have had some really good help on the Filemaker.com forum, but I am hoping i might find a different answer here, that is more in tune with what i want to achieve!

 

I need to create a database that generates and stores information about tv commercials that i make. The most crucial thing i need to do is generate new "clock numbers" for each ad. This is a unique 15 digit code in the format ABC/DEFG123/020 - ABC = ad agency code, DEFG = product identifier, 123 = serial number between 001-999, 020 = the ads duration in seconds.

 

I have figured out how to generate a new code for each new record, and its been working fine for a few years now. 

 

My latest idea is that i want to reduce the amount of data entry needed to create these records. As each ad has many variations, i want to create Campaign table into which I enter the generic data once (such as agency code / product identifier / duration / actor details etc),

 

then have a selection of options to pick multiple variation types ( i tag each ad for specific stores that sell the product).

 

Then i want to hit one button and have FMP generate a new record for each option, and in that record generate the new clock number, keep a record of the option name and create several unpopulated fields that can be used at a later date to store approval documents, transmission dates/stations and perhaps a video of the finished ad.

 

I also need to see each clock number and its option name in a window on the main Campaign page.

 

The main question is How do i generate multiple records from selections with a single click?

 

So far the answers have said i need to do each one individually, but that just seems too long winded and inefficient to me.

 

thanks in advance

Adam

[Wim Decorte]

As long as you can outline the different permutations of records that need to be created based on the selected options then it can be scripted.

 

In essence the script would collect your initial input and the script would go through a sort of "decision tree" to loop through those options and create records.

The hard work is not in creating the script but clearly specifying all the different options and what records need to be created based on those.

 

The one thing that is off in your explanation is:

 

 

and create several unpopulated fields that can be used at a later date 

 

Scripts can create records but not fields.  I'm guessing that's not really what you meant...

The script can pre-populate fields or leave them blank.

[lightningad]

Thanks Wim, very encouraging.

 

so assuming i had 10 options in total, but only wanted to select 4 to generate my current batch of clock numbers, how would i create the options to select, then get a script to recognise the selected ones from which to work?

 

so if its a dumb question, i am really in the dark on scripting!

 

as for your last point, i see what you mean. i was rather unclear (and forgot that i dont need to create everything in one go...those files could be dropped into a related table at  a later date!)

regards

Adam

[Wim Decorte]

Can you give some real life examples?

 

Is this a structure where you start with picking one option out of a possible say 3, and that drives what you can select next and then that determines what you can select after that?

[lightningad]

Hi Wim,

sorry for the late reply...I only just saw your response. Don't know why my email kept it hidden for so long!

 

 

The clocknumbers are in the format ABC/DEFG123/020  and are created as follows:-

 

ABC    =    The code letters for the ad agency (in my case usually "COL" but not always)

DEFG =    Product code letters relating to the product being advertised. I tend to base these on the products name.

                        I tend to have this field auto fill so i can re-use codes if needed

 

123     =    This is a numeric serial number between 001 and 999. 

020     =    The duration of the advert in seconds

 

The combined clocknumber (ABC/DEFG123/020) must be unique. I need to include a check that ensures the overall combination has not been generated previously.  I cannot deviate from this format either as its specific to broadcasters requirements.

 

Each ad that i make usually has four or five versions, with a graphic ("Tag") on the end of the ad showing which store the product is available at. So i would need to generate a new clock number for every version of the advert.

 

My thinking is that when creating a new Campaign record, i have three fields that should remain constant in the clocknumbers - Agency, Product Code, Duration

 

These should be entered into fields as one of the first steps.

Then there should be a Radio Button listing all the possible "Tags" (If a radio button is not the right one to choose i'm open to being corrected!)

 

Upon clicking any option on the radio button, a script should be triggered which reads the three constant fields, finds the last used serial code and picks the next one sequentially, runs the calculation to generate a new clocknumber,  then adds the name of the option chosen to a related field.  These are then displayed alongside each other in the portal window.

 

A click on the next option repeats the process.

 

I would enter COL in my agency field, TEST in the product field, 010 in the duration field

then with four clicks on a selection panel, my portal would display:-

 

COL/TEST789/010     Generic

COL/TEST790/010     Argos

COL/TEST791/010     Smyths

COL/TEST792/010     TRU

 

I have been figuring out Conditional Value Lists and have been able to get much of my other ideas for this database sorted, its the scripting that i have no idea where to start!

 

regards

Adam

[comment]

Upon clicking any option on the radio button, a script should be triggered which reads the three constant fields, finds the last used serial code and picks the next one sequentially, runs the calculation to generate a new clocknumber,  then adds the name of the option chosen to a related field.  These are then displayed alongside each other in the portal window.

 

IMHO, a better user interface would use a checkbox field for the multiple "tags" and a dedicated "Create" button to create a record for each checked tag (this is assuming there will be only one record for each checked tag).

 

All this should be quite trivial, until we come to the "unique" requirement. With only three digits to work with, this is far from trivial - in fact, it might become downright impossible if you ever have a product with more than 999 ads of the same duration. So the script needs to test the proposed clock number for uniqueness before creating (or committing) a new ad record, and if the test fails, bump the serial number up (or select a random one) and repeat the test until it passes.

[lightningad]

Thanks - i had not realised the difference between radio and check boxes was either a single or multiple choice. I like the single create record button, as i am really trying to get away from the multiple times i need to repeat things currently.

 

As for the "unique" its really not that big a problem when you consider i can change both the agency code and product code if required, plus i would probably quit before having to make 999 versions of the same advert!  

 

I think i managed to write a check into the original database i made 6 years ago, so i will be trying to figure out what i did and adapt where necessary.

 

As for "trivial" - it probably is for someone with experience, but for a complete novice like me its far from trivial....hence my asking for help with the scripting!

any suggestions?

 

thanks

Adam

[Wim Decorte]

 

As for "trivial" - it probably is for someone with experience, but for a complete novice like me its far from trivial....hence my asking for help with the scripting!

any suggestions?

 

 

Why don't you post the script you have so far and we can point out what can be done differently / more efficiently

 

Otherwise you're really saying: "can someone please write this script for me".  You may get a taker for that but not many

 

The idea here is to  help you learn.

[comment]

As for "trivial" - it probably is for someone with experience, but for a complete novice like me its far from trivial....hence my asking for help with the scripting!

any suggestions?

 

I can give you a general outline:

Assuming you are on a layout of Products, with these fields available:

• ProductID (auto-entered serial number)

• ProductCode

• gDuration (global field, Number)

• gTags (global field, Text) this is the checkbox field

Set Variable [ $productID ; Products::ProductID ]
Set Variable [ $productCode ; Products::ProductCode ]
Set Variable [ $agencyCode ; Agencies::AgencyCode ]
Set Variable [ $duration ; Products::gDuration ]
Set Variable [ $tags ; Products::gTags ]
Go to Layout [ Ads ]
Loop
 Set Variable [ $i ; $i + 1 ]
 Exit Loop If [ $i > ValueCount ( $tags ) ]
 New Record []
 Set Field [ Ads::ProductID ; $productID ]
 Set Field [ Ads::Duration ; $duration ]
 Set Field [ Ads::Tag ; GetValue ( $tags ; $i ) ]

 # HERE COMES THE NON-TRIVIAL PART:
 Set Field [ Ads::ClockNum ; $agencyCode & "/" & $productCode & SerialIncrement ( "000" ; Mod ( Ads::AdID ; 1000 ) & SerialIncrement ( "000" ; $duration ) ]
 # MAKE SURE IT'S UNIQUE ...

End Loop
Go to Layout [ original layout ]