Title

Script Trigger to create multiple records from one record in different tables

Post

Hi I'm trying to automate the creation of some records in a table from the fields of one record in another table.

Using FileMakerPro 11 Advanced on a Mac

Scenario:

In Table "Orders" I have imported many files into a container field (1 for each record). Each record will then have a barcode scanned into a Customer ID# field so each file will match back to a related table with the customers ID#. I then populate an order field. The order field then populates various calculated fields based on the order type. Each record/order can represent multiple pieces of work for completion that I need to have as individual line items that match the order# in another table.

e.g.

Table "Orders" Record 1

Container Field: xyz

File Path: user/xyz/filename

ID#:1001

Opt1 (OrderType):1

Opt1_1:[If(IsEmpty(Option1)=0;FilePath;"False")]

Opt1_2:[If(IsEmpty(Option1)=0;Filepath;"False")]

Opt1_3:[If(IsEmpty(Option1)=0;Filepath;"False")]

There are many other options with calculated fields as well.

Table 2 - Requires each True calculated field above to be a separate record in another table but with the same ID#

e.g.

Record 1

ID#1001

Opt1_1User/folder1/filename

Record 2

ID#:1001

Opt2_2: User/folder1/filename

Record 3

ID#:1001

Opt2_3:User/folder1/filename

And so on and so forth. The actual number of potential fields is quite large (40+).

I would like to trigger the creation of the records in Table 2 when I hit a submit button in a layout based on the "Orders" Table.

Any ideas would be helpful. I'm new to scripts and have not found anything on the forum that seems to be similar to what I'm trying to do.

I have created a portal in ::PortraitImages to show the fields: Print Type which is related to the PrintType Table, Print_ID from the PrintOrder Table and Subject_ID whic is related to the PortraitImages Table.

When I click on a new row in the portal everything works great and it is creating my separate rows in the other table. Question? How can I automate the population of the portal rows based on the condition in one field in the PortraitImages Table.

For Example: Say there are two types of order available 1 requiring two prints, and one requiring 3 prints, lets call them option one, and option two. When I select Option 2, I need it to populate three rows in the portal with the appropriate line items to be printed. I need two rows populated for Option one. I've tried using a few script steps but whenever I try to create the second record it simply stops the script and does not populate the data in the second record. I'm new to scripting so I'm not quite sure where to go from here?

The additional fields are required because they are variables that are seen by another application outside of Filemaker. I'm sure you are familiar with variable data printing VDP? I wish all I had to do was set the qty that would be quite simple.

I seem to have resolved my issue by using a

go to first portal

field set field

Commit record

IF(if the next option is present then go to next portal)

go to next portal field ....etc I'm still ironging out the bugs but I think it will work.

If End

Sorry but my scripting syntax is not the best being relatively new to it.

I'm going to muddle through with this unless you can think of something simpler?

When manipulating data in a portal. It's often simpler and safer to Freeze the window, use Go To Related Records to pull up a found set of the portal records on a layout based on the portal's table. Then manipulate the data as needed on this layout and finally, return to your original layout.

Note that steps like go to portal row do not include an option for specifying which portal is to be manipulated by the step. If you have more than one portal on your layout, you have to include code that puts the focus on the correct portal before you execute the go to portal row step. This requires using the inspector to give the portal an object name and then you use Go to Object with that exact name to put the focus into the correct portal. Using Go to Related Records and switching layouts avoids those complications and also the risk that future changes to your layout desing could accidentally mess up how your script functions.

If you want to create brand new related records in the portal, you can put the parent record's ID into a variable, change layouts to the portal's table and then use new record, followed by a set field step that assigns the ID to the new record and you then have a new record that will appear in your portal.

Every bank statement you receive (be it electronic or paper) is created through VDP. The application that creates the PDF file is told by the variables set in it which graphics, data and images to print on the page. For example a premium customer may get a statement with a special offer because they spent more than a certain amount on their credit card. I digress.

What you say makes sense. I'll have a go this weekend and let you know where I end up. Appreciate the advice.

On another note, in the same DB I'm trying to create a layout that will print all of the subjects names an ID#'s from a particular group on one page with a page break after each group. I have been boning up on managing Parts but when I follow the advice I'm still getting subjects from the next group appearing with the previous group. I have uploaded a screen shot of my layout. See screen shot to see what I'm talking about. I've tried adjusting the page break options in various parts and also the print options as suggested in some posts but I just cant get it to break for each group.

The reason they are split is because within the graphic application they areusing one template to generate many different versions. This means the RIP used to print the document can cache the common elements and only print the variables regardless of which combination of variables are used therefore freeing up systme resources and enabling faster rip times. Anyway, I digress and I'm not an expert in the area so I'm no the best explain it.