Title

Parent and child tables. How to make sure changes in value in parent table lead to changes in values in child table?

Post

I an a starter and before I worked in MS access. I am missing it. Now I can't find how to solve this problem. I have a reference table which has a unique field and that field is the source for values in the child table. I want to make sure that if I change the value in the parent table, all the values in the related field in the child table should change accordingly. Does anyone knows how to do that?

Don't store the data in the child table, keep it in the parent table if you want the data change in a parent record to be automatically reflected in child records.

You may need to provide a more detailed example of what you mean. I am aware that MS Access allows you to link tables by primary key to foreign key and you can set an option to enforce referential integrity. Changing the primary key then kicks in an update that updates the foreign keys of related records so that they still match. There is no matching feature in FileMaker though you could set up a script that does the same. However, in my opinion, this is very poor database design and relying on it can have major negative impact on database performance. The primary keys should not be such that changes to the value are neither necessary nor permitted.

I want that I can input the values into a field of a child table which are restricted to the values stored in the primary key field of a parent table. In FM i can override the value list and input whatever your fantasy goes to. This is not good for me.

Also, if I decided to change the name of the type of product for example. In Access I just changed this in parent field and the child table was updated for all thousands records automatically.

And also, I need to set a default value for the field. In Access it was so easy, but in FM I struggle to find how to do that.

In FM i can override the value list and input whatever your fantasy goes to. This is not good for me.

You can set a validation to reject all input that is not a member of the value list. See the validation tab in field options.

Also, if I decided to change the name of the type of product for example. In Access I just changed this in parent field and the child table was updated for all thousands records automatically.

This was possible in Access, but is not good database design. If you had several 1000 records, this feature could pretty much shut down the database while the update took place.

In both Access, FileMaker and any other relational database, the product name should only exist in the parent record, not the child records. Any layout that displays data from the child table can include the product name stored in the parent record. Then, any updates to this product name automatically show on all related child records. The product name should not be your match field in Access or in any other relational database system.

And also, I need to set a default value for the field

In field options, you can use the auto-enter tab to specify a default value.

In Access it was so easy, but in FM I struggle to find how to do that.

I've worked in both systems. I'd say that FileMaker is actually much easier than Access, but when you make the change from Access to FileMaker, most of the techniques that you learned working with Access have to be replaced with different methods in FileMaker. Have some patience and keep at it, there's a lot of help available and not just in this forum and you should get up to speed with how FileMaker accomplishes this stuff.

Thanks PhilModJunk. Your remarks are very helpful. Yes I uderstand that all product information should be stored in the parent table and you link the parent and chold table by sort of code filds so that it does not matter whaever thier values are. I just sometimes assigned as a primary key an informative field, such as text if it was a short one. Now I see it was not a good practice.

Now I need to play with the layouts so that the fields from a child table display informative values from a parent table and so that I can work (add records from a value list for examle) with those informative values rather than the numerical codes by which they are related.

Knowing the "rules" also allows you to make informed decisions to break them. I sometimes violate this rule myself when:

The key is not the primary key--it might be a "category" key for example, used to facilitate user interface design, and the number of values used in the list is fairly small. If I then need to enable user initiated changes to this list of values, I set it up with a script that:

a) Uses Go To Related Records to pull up the matching set of records affected by the change

b) Updates them with Replace Field Contents in a batch update. (One replace Field contents operation updates all the records in one go.)

c) Then, and only then updates the matching record in the parent record in order to reconnect it with the child records

This is essentially what MS Access does with its built in tool and like using this in Access, record locks by other users and larger numbers of records to update can make this a very inadvisable thing to do in many circumstances.