Problem with relationship and change of records

[Deep Thought II]

Here is the problem that I encountered. I put it in two ways... illustrated and in words. If you can understand the problem in words, it will save some time... otherwise please read the illustrated way of showing the problem in an example.

Word:

-----

2 tables related by a single field in which one allows addition and deletion of records depending on the other. When the field is changed in the primary table (the one that causes addition and deletion on the other) on the same record, the records on the second table losts its track. Diagnosis: relationship doesn't have the option of "if the field on the table has changed, it also changes the field on the field on the other table." But in MS Access, there is the relationship option that is "Enforce Referential Integrity" and "Cascade Update Related Fields." Can anyone tell me where is this option and how to have it done in FileMaker? Thanks a bundle.

Illustrated:

------------

First, suppose I have table#1 {Purchase Orders}, and in it there are fields of order information such as Order Number, Customer name, etc. Then there is table#2 {Purchase Details}, which stores the details of each order.

Table#1 and table#2 are related by [Order Number]. The relationship allows creation and deletion of records in table#2.

So in the simplest design of a layout one would use table#1, and create a portal that shows table#2 for entering the entire Order information.

Now, one would obviously hide the field [Order Number] in the portal that shows table#2 since it will be automatically entered as a new record is created.

Now when one creates a new Purchase Order record, one enters the order information, then enters the details in the portal... then the record is saved...

But when one comes back later, and change the [Order Number], the entire order detail in the portal is gone! Why? Because there is no related records with the new order number, or it might show a completely different set of records. This is a big problem.

As in MS ACCESS, there is the "Cascade Update Related Fields" option that does the job nicely if this situation happens... how do I do that in FileMaker?

thanks..

[Matt Haughton]

The way to do it is to perform a script to change the [Order Number], that firstly asks for a new [Order Number] then goes and changes all the relevant records

Go To Related Record [ Show Only Related Records ; Purchase Order Items ]

Perform Script [External: Purchase Order Items(Amend [Order Number] In found set]

Set Field [Order Number ; New Order Number

i.e. firstly show only the records to change in the other file, then perform a script in the other file that loops through the entire found set and makes the relevant changes, then change the parent record.

Of course the main problems are, "What if processing is interrupted half way through?" this is a custom script and therefore no automatic rollback feature so be careful, and what if another file links to either of these files based on [Order Number]?

This does however beg the question, why would you want to do this? I can see the benefit of changing unique IDs in certain systems, however if a unique ID is likely to change (or even if there is a possibility of it changing) it shouldn't be used as a key for the relationship.

Try auto-entering a serial number [PO_ID] into your Purchase Orders file (the main file), and add a [PO_ID] field in Purchase order items, and use these to relate the files. Then any purchase order items added via the portal (which will now be based on the PO_ID) will be related via this unique, unchanging ID. The [Order Number] can then be stored in the Parent file and an unstored calculation field added to the purchase order items file to pull the [Order Number] through the relationship if necessary

[Deep Thought II]

yes, that's good idea, to keep the PO number permanent in this case. the key-in in my company just typed the order number wrong today, and she was going back to that record to change it, and suddenly this problem pops out.

thanks for the tip :-)