Creating a new (event) related record with data from multiple records
Again I'm stumped and can't seem to decipher the answer from reading the forums... Any help you can provide would be appreciated.
I am working on a solution to help us manage auctions. Currently our database's main table (liens) contains all of the relevant information about a car and it relates to a table of our customers (garagemen).
I have created a script that extracts relevant pieces of information from a "lien" record to create a truncated auction advertisement for a car and was able to figure out how to loop the script (Thanks to Philmodjunk) and create a concatenated auction advertisement with all the liens for a particular auction date and so far it seems to work great.
The problem is I was able to accomplish this with fields that are in the "lien" table so therefore although the script itself works, problematically it is a field connected to a record that is part of the lien table and not its own record, which as you can imagine can create a whole host of problems down the road.
My problem now is in figuring how how to properly set up a related table called "auctions" and write a script that essentially does the same thing (extracts relevant pieces of information from a "lien" record to create a truncated auction advertisement for a car and loops to create a concatenated auction advertisement with all the liens for a particular auction date) only creates the new related auction record in a separate table, bringing in the relevant data from each of the selected "lien" records.
But the data in the "auction" record also needs to remain fluid, meaning if in fact we decide to reschedule a "lien" to a different date, the auction record needs to update automatically to reflect the the new information.
My novice knowledge of filemaker lends me to believe I could accomplish this with a related table and portal records but I am unsure... I get stumped when thinking through the proper execution... Right now a lien record automatically creates an individual auction ad per lien once a specific field is exited.. Then I have a second script that only appears on a layout of records from a found set (based on auction date) and that script simply loops through all records in the pre sorted found set to create the concatenated advertisement... It pastes that data from each record into a field called Auction_Ad_Full_g.Consequently if I look for records from a different auction date and return to that layout, the previous auction ad is still showing in that field... Therefore I have to execute the script again which will work to accomplish one of the goals which is to create the concatenated ad for the found set but in turn it simply replaces the data from the previously created ad that had been in the field...
I'm at a bit of a loss to figure out the proper execution of this goal...
FYI- My ultimate goal in execution is to have a layout that shows all cars available for auction, have the terminal operator select a group (via check box) then click a button which as scripted will then create a new auction record with the selected group, update each of the records to reflect the selected date and other necessary data and create the concatenated ad for the auction...