In Customer, define an auto-entered serial number field "CustomerID". (You have probably already done this.)
In BigShooter, you need exactly one field: "CustomerID". (You can add other fields, but don't add any other fields such as customer name that already exist in Customer.)
Define a relationship linking the two fields:
Customer::CustomerID = BigShooter::CustomerID // enable "Allow creation of related records via this relationship for the BigShooter table
Now a single script step will assign a customer to the BigShooter table:
Set Field [BigShooter::CustomerID; Customer::CustomerID]
On a layout that lists records from BigShooter, simply add the fields from Customer that you need to show names, contact info, etc.
Now that I've typed this all in, it occurs to me that you might not need the Big Shooter table at all.
Define a calculation field in Customers, cBigShooter:
If ( Orders>10 ; "Big Shooter" ; "" )
Now, on a layout based on Customer, perform a find for the text "Big Shooter" in the cBigShooter field to find all such customers.
Thank you, that helped me to get to the next step.
I now have a layout in the Customer database AND I have two portals in that layout -- one to BigShooter and one to TheOrders.
If I'm looping through the Customer database, what does the script step look like, if I want to add a record to TheOrders database?
Again, thank you for your help.
The script Step is New Record/Request, but you have to tell Filemaker in which table to create the new record.
Assuming the script is triggered from a Customer based layout and that the Order record will be related to that customer by CustomerID:
Set Variable [$CustID; value:Customer::CustomerID]
Go To Layout ["TheOrders" (TheOrders)] //specify a layout based on TheOrders table
Set Field [TheOrders::CustomerID; $CustId]
Go To Layout [original layout]
I see what you are doing here, but I'm having a problem with the "go to layout" script command
When add the "go to layout" command to my script, the FMP interface allows me to select a script. However, I can only see my Customer-based layouts. My customer database and orders database are in two different physical files.
IS there a way to select a layout for a different database file?
When you open Manage | Database | Relationships, is there a Table Occurrence box that refers to this external file? I assumed you do.
Create a new layout and select that Table Occurrence as the table for your new layout.
By the way, what version of Filemaker are you using?
If you're using a fairly recent version of filemaker, it may be much easier for you to merge the files into a single file with multiple related tables.
If your using FMP 6 or older, the script I've posted won't work.
I'm using version 10 of FMP.
What you suggested worked. Thank you so much....