1 of 1 people found this helpful
There are a few ways you can accomplish this task:
1) You can use a Lookup or Auto-enter calculation to copy the data from Offers to Orders via a key field. You can put an OfferID field in your Orders table that has a relationship back to the Offers table. Then, you can just enter the OfferID on a new Orders record and have FileMaker copy all the data over. (A value list having all the OfferID values and some other value so you can recognize which is which will help you identify what OfferID to put in.)
2) You can set up an automated import from Offers to Orders. Do a Find to locate the specific Offer record, then import it to Orders. You can set up the necessary field mapping in the Import dialog in the Import Records script step as needed to get the right data into the right fields.
3) You can create a couple of global fields in a convenient layout (either Offers or Orders; likely Offers, based on your description) and then set up relationships between those global fields and the appropriate key fields in Offers and Orders. This allows you to do an operation known as "processing in place", which I first saw in a presentation by Ray Cologon. Basically, you set the global field that points to Offers equal to the key field of the record you want to copy from. You allow creation of related records on the relationship that points to Orders, and then use a series of Set Field script steps to create the new record over there. Example:
Set Field [ OrdersGlobal::CustomerID ; OffersGlobal::CustomerID ]
This technique takes advantage of an undocumented (but very handy) FileMaker behavior: When you have a global field on the "parent" side of a relationship that meets these conditions:
- The relationship allows record creation
- The relationship points to a key field that automatically creates a value (like a serial ID or a UUID)
- The global field is currently empty
and you use Set Field to set a different field on the "child" side, then the global field is automatically set to the newly-created ID value. (Try it; it's cool.)
In any case, once you've successfully created the new record, your script can use the Go to Layout script step to navigate to the correct layout.
Thank you Mike. Gonna try this ;-))
1 of 1 people found this helpful
Assuming that the offers and orders tables carry much of the same info, or that most offers fields are included in the order table, merge those tables so all fields are in a single table, just not all used when the offer is started.
Then there are a couple of simple methods to use some records as Orders while the Offers all remain:
- use a radio button to mark the record type, defaulting to offer on creation, changing it to order when changed. Filter any portals showing orders to show only the order type.
- Use a button or script to convert the offer to an order by having a second foreign-key field, same as for the client-to-offers table, but used for the client-to-orders relationship. Set that value to the same client ID at the time the record is changed to an order, and you have a separate relationship with just the order records out of that table.
Thank you Stephen.
Very early in development of my solution I was thinking about creating just one table with some boolean making the difference between offers and orders.
I guess in hindsight that would have been the better solution. Of course you are correct in assuming that most of the records in both tables hold the same info. I could merge the two tables and my practical issue would be gone.
However, I was curious about the mechanism of how to exactly 'take' a record from one table and insert the data of this record into another table, together with a switch to the appropriate layout where I could complete the record. A kind of complicated 'copy and paste' action if you like. Mike already made some usefull suggestions which I am going to try out. But I would be grateful for your suggestions too.
Thank you again.
Personally, if keeping separate tables, I would probably go for the lookup option which Mike mentioned. You need only set the primary key of the Offer record to a script variable, go to the layout for the Order records, create a new order, and set the foreign-key of the new order to the variable value.
The fields in the Order table need to be set to auto-enter based on a lookup setting between the Orders and Offers, so a relation has to be set between them. When the new Order record is created and its foreign key set to link it to the Offer, the data will fill itself in.