Script to copy multiple fields for all line items

[AndyCan]

Hi guys,

This is a question i first posed in the relationship forum, but i think it's much more of a scripting question now.

I need a script that when i click a 'generate job' button will create a new record in the job table and populate the job line items table with certain fields from the quote line items table. The best way to explain this is with an image, so here goes...

I started off thinking about this in terms of lookup fields, but that didn't seem to work.

I've read elsewhere about a 'set field' function that could work with a global variable set to the quote ID, but i'm unsure how to execute this.

Basically, i need the Category, Description and Estimated Hours fields for all the relevant Quote Line Item records copied from Quote Line Items to the Job Line Items when raising the parent Job. I could do this manually with every job and i'm sure it wouldn't take too long, but i'd quite like to solve this problem and learn a bit more about Filemaker in the process. Also, it just seems to be a bit more 'elegant' to have the database fill the fields in advance.

Please help! Thanks in advance ,

Andy.

[IdealData]

The best route is to use $vars and SetField, roughly like this

New Window

Go to layout "Quote Lines"

set $Field1 = "Quote Lines::Field1"

set $Field2 = "Quote Lines::Field2"

set $Field3 = "Quote Lines::Field3"

Go to Layout "Job Lines"

New record

SetField "Job LInes::Field1" = $Field1

SetField "Job LInes::Field2" = $Field2

SetField "Job LInes::Field3" = $Field3

Close Window

Personally I prefer to run these scripts in another window because when the script is finished and you close window the original window still has the original found set.

Create some dedicated layouts for "Quote Lines" and "Job Lines"

Edited March 13, 2010 by Guest

[bruceR]

Personally I prefer to run these scripts in another window because when the script is finished and you close window the original window still has the original found set.

I prefer to never use other windows for record creation. The same stay on current record goal can be accomplished using standard FileMaker autocreate relationships. Either use a global field for this purpose in the data table. Or -there are many reasons to use a Resource table, and it can be used for this purpose also. This way you don't need to put global fields in the data table. See attached.

SimpleCreateRelated.fp7.zip

[bobearl]

Hello all,

Apologies for the basic question, but this is something that I've never quite gotten, but I think Bruce's explanation is as close as I've ever been to understanding how to do this. What I'm not quite certain that I understand is how to use the 'standard FileMaker autocreate relationships'. Bruce, do you happen to have a link or a summary of what you're referring to in general. Your solution seems very elegant, I just am not following exactly when and how the new related record is created. Many thanks!

[Rainy Pass]

"I just am not following exactly when and how the new related record is created."

The new record is created by the Set Field script step. To see this, go to the Phones layout and run the Auto Create Phone Record Via Resource script from the Manage Scripts window. If you have FM Advanced, turn on the script debugger and you will see the record appear when the following script step is executed:

Set Field[RES_Create_Phones::fkPersonID; People::PersonID]

[Courtney]

On 3/14/2010 at 5:57 PM, BruceR said:

 

I prefer to never use other windows for record creation. The same stay on current record goal can be accomplished using standard FileMaker autocreate relationships. Either use a global field for this purpose in the data table. Or -there are many reasons to use a Resource table, and it can be used for this purpose also. This way you don't need to put global fields in the data table. See attached.

SimpleCreateRelated.fp7.zip

I realize this is quite an old thread, but it addresses a question I'm having. However, can someone explain the purpose of clearing the global field? (Twice in the same script without it apparently having been set to anything at any point?) Is it just to force record validation or something?

[bruceR]

You've got FileMake Advanced; it would be good to step through the script and watch how it works.

The field gets populated with the child-record ID when a field on the child side of the auto-create relationship is populated.

In order to create a new (different) child record, the global field first needs to be cleared.

[Courtney]

Hmm, I did watch the script work a few times and didn't see that field ever set, but I may not be starting at the right point.

Ok, I see it now, but I still don't understand it.

The global is populated because of the relationship between People and Create_ChildRecord?

[bruceR]

Think about what "allow creation of related records" means in practical terms. Which is this. (My very brief description)

If the records are not related, that means that one or more of the fields defining the relationship are empty (or do not match).

When allow create is turned on, setting a field in the no-record side of the relationship automatically populates the match-fields which are missing data.

It is a fascinating and very useful feature.