Refreshing Value in a Pop-Up List field

[wmugrad28]

Hello,

I have two tables: 'Customer' and 'Work Order'

This is a one to many relationship.

In 'Customer' I have several fields including Customer Name. In 'Work Order' I have several fields including Customer Name as well. The two tables are joined together by the Customer Name field (set at = ). The focus of the 'Work Order' table is a Work Order Number and not the Customer Name. On the 'Work Order Layout' I have a pop-up menu tied to a value list. This value list is related by the field Customer Name in the Customer table. If I change the spelling of a customer's name on the Customer layout then I want it to change (or refresh) the selected Company Name on the Work Order layout (which is related to the Work Order table). The user can reclick on the pop-up menu to select the new spelling, but I want it to do this automatically. How can this be done?

Greg

[Chuck]

In generally, you don't want to use fields that have meaning to the user as your key fields to link tables. One reason for this is precisely what you're coming up against. The user may change the data and break the relationship between the two records.

Generally, every table should have a key field who's sole job is to uniquely identify each record. The easiest way to do this is to create something a number field called CustomerID and set it to auto-enter a serial number. You'll want to do similarly with your Work Order table, creating a WordOrderID field.

I go a step further in the creation of my tables, making the key fields text fields and using a three letter prefix in the auto-enter options, so that the first record of my Customer table would have a CustomerID of CST101 and the first one of the Work Order table a WorkOrderID of WOR101. All that's needed, however, is the assurance that this field will be unique (which it will be if you don't manually or programmatically change the auto-enter options of the field) and that it can't be changed. That last condition can be ensured by clicking the Prohibit modification of value during data entry and making sure that you don't script a change in the field.

You also want a CustomerID field in the Work Order table, but this will be a simple number (or text) field. Define your relationship to use the CustomerID field in both tables.

Then, instead of having the user select the customer's name, you have them select the ID (showing the customer's name next to the ID in the value list). Then if the user changes the customer's name in the Customer table, the change will automatically extend to the Work Order table.

Any good book on FileMaker will go into much greater detail than I have here. I would recommend Using FileMaker 7 from Que as the best one of the bunch.

Chuck