I don't see how that worked in filemaker 7, it certainly wouldn't have worked in filemaker 5 and older and won't work in current versions.
Set field is referring to a field in File_A via the relationship between the two tables. When there is more than one related record in File_A's table, it will reference the "first" related record every time. The record that is current in file A will have no bearing on which file the set field step references.
You'll need a different approach.
- Display the related records in File A in a portal on File B's layout and use go to portal row, next to step through the rows of the portal to access each related record in turn.
- Use global fields in one of the two files so that a script in one file can put the data in the global fields and a script in the other can move the data from the global fields to the correct fields and record in the other file.
- Perform a pair of script calls that passes the data via script parameters back and forth.
- Use import records to import the data from selected fields of the found set in File A into records in File B (This can be risky )
Thanks much for the info, PhilModJunk! Can we ask for a follow-up, to determine the most efficient way for FM 10 to handle this?
We think option 1 could work for us. (Option 2 probably wouldn't easily work using global fields, since multiple users can execute this processing concurrently. Option 3 also wouldn't be as easy, since we would need multiple parameters. And since this is executed many times each day, option 4 would require more imports than we want to do each day.)
We want to compare option 1 to another approach. File_A and File_B currently each contain one table (i.e., Table_A and Table_B). What if we moved the 2 tables into the same file, so Table_B would also be in File_A along with Table_A? In brief testing before we noticed your fast reply, this seemed to execute properly. In this case, File_B could be deleted. Which approach is more efficient for FileMaker? File_A/Table_A is currently about 190 MB, with about 250,000 records. These are line item records, tied to an invoice table in yet another file. The current File_B/Table_B is tiny, about 15KB, never more than a few hundred records. The process runs at least 30 times per 8 hour workday, and it runs while we are talking to customers via long distance phone calls. Each time it runs for a particular invoice, it loops through each line item to do its calculations; while most invoices have only one line item, they can have multiples. Before the move to FM 10, when it was working in FM7, it would take about 20 to 30 seconds for the process to complete each time. While this is acceptable, faster would be better.
With both tables in the same file, would we eliminate the problem of always having Set Field reference the first related record? And if so, is it more efficient to step through the rows in a portal? How would that compare to making multiple script calls between the 2 tables in the same file (one call to each table for each line item)?
Option 2 probably wouldn't easily work using global fields, since multiple users can execute this processing concurrently.
This is not a problem with global fields. Each user gets their own copy of global fields when they open the file. The values one user assigns to a global field will not be visible to another. Also changes made by a client to a global field will not persist, the field reverts to it's original value when the client closes the file. This is by design to avoid this precise issue.
Option 3 also wouldn't be as easy, since we would need multiple parameters.
Not a problem really. You can use the List function to combine multiple values in a single list when Perform script is used to send the parameter and you can use GetValue to extract specific elements of the list on the other side.
What if we moved the 2 tables into the same file, so Table_B would also be in File_A along with Table_A? In brief testing before we noticed your fast reply, this seemed to execute properly.
Something is odd here. Whether in the same file or separately you should see the same behavior as set field establishes references to fields based on the current record in the current layout (whether in the same file or not) and its relationship to other tables as spelled out in your relationship graph (whether in the same file or not).
Now that I've thought about this. How are your tables structured here?
I get that file A is your Line Items table, but what file/table are you moving data into, how is it related to the records in file A and why?
There may be a completely different approach that meets your needs here.
The whole of this thing is INVOICE, CUSTOMERS, INVOICE ITEMS, SHIPPING CALCULATOR Files.
We want our customer service staff to be able to calculate shipping cost for customers while on the phone. So after they enter the product line items. They click calculate shipping button which then generates a new record in the SHIPPING CALCULATOR, then copy/paste over zip code, invoice number (for relations), and residential from INVOICE, then SHIPPING CALCULATOR goes to related records in INVOICE ITEMS to begin gathering the quantity and sizes of each product. (which is the part that isn't working anymore, has been working for close to three years now in FM7 just perfect). After the SHIPPING CALCULATOR gathers the quantities and sizes, it then configures packages based on what fits into particular boxes for UPS (and that part works beautiful). Then lastly it displays a window for customer service to choose different UPS shipping cost for one day, two day and so on while the customer is on the phone.
Hope this makes sense :)
Residential is just a Y/N indicator.
The key field for relations is Invoice Number, from Invoices to Invoice Items and from Invoices to Shipping Calculator.
And while the scripts usually create a new record in Shipping Calculator for a given invoice, if the script has been executed before and a record already exists there for that invoice, we just reuse that record.
Thanks again for your help. We are learning a lot from your responses!
"Copy and Paste"--that's something to avoid if at all possible. Copy and paste steps will silently fail, (no error message will interrupt your script), if the referenced fields aren't present on the current layout and that could explain why your script from filemaker 7 days now fails. Perhaps someone recently edited the layout and deleted a needed field.
I think you are loading too may pieces of data into a "bucket" and using a script to carry them from file to file when you can simply use relationships to access the data instead. We can talk about copying to the clipboard, variables, script parameters and global fields, but they're all just different types of "buckets" and the best approach is not to use them unless absolutely necessary.
Note that everything ties together by Invoice Number. That's the only value your shipping calculator really needs. Once it has that value, it can get to everything else. No need to move copies of the customer's address around nor any invoice totals. The one exception is that it sounds like you need to step through all the invoice items to figure out packaging needs.
From Invoices, you can use Go To Related records to find all Invoice Items records on a layout that refers to Invoice Items. If you have relationships defined that link to Invoices and your Shipping calculator table, you can then do the whole thing there, without using any kind of "bucket" to carry data from one file to another. Your shipping calculation script would step through your Invoice Items records and use set field to modify the data in a shipping calculator record via a relationship based on invoice number. This can be set up to work for you with merged or separate files.
With separate files, you can go to Manage | Database | Relationship, click the button on the bottom left corner to start a new table occurrence "box" and select "add filemaker data source" from the data source drop down to select a table from one of your other fileds. Do this so that you can have the following relationships in your graph:
Invoice Items::InvoiceNumber = Invoices::InvoiceNumber
Invoice Items::InvoiceNumber = Shipping Calculator::Invoice Number (enable allow creation of records for Shipping Calculator)
Invoices::CustomerNumber = Customers::CustomerNumber
Almost forgot: If you haven't used Go To Related Records before. Read up on it in filemaker help for filemaker version 11 and pay close attention to the warnings given. If you are using an older version of filemaker, you won't find those warnings.
See this article for more on the various options, uses and dangers of GTRR:
Thank you for your help PhilModJunk. Your ideas worked out very nice and we now have the system functioning again.