Reflect change in value list immediately in database

[Gregor Alessi]

Hi all

I am new to FileMaker and Databases in General. My first small project is to create a pricelist for a small store with 20 articles.

=== The Setting ===

I have two databases which are not directly related:

db_articles

db_groups

Plus, there is a value list

vl_groups

which is defined to contain the entries from db_groups::article_group

the field db_articles::group has the following options set:

Validation: Always, Do *not* Allow to override, Not emtpy, Member of value list: vl_groups.

The field db_groups::article_group is defined to only contain unique entries, and not to allow user override.

In the layout, the field setup for db_articles::group is as follows: Drop down list, Display values from vl_articles, Include aroow, Auto-complete.

=== Let's put it together ===

Let's say I have five articles that have a value in db_articles:group named "paper". As expected, I get a pull down menu that shows me this value, as I entered it before in db_groups:article_group.

Now, I decide to change the entry paper in db_groups to "Papier", the German translation.

=== The Problem ===

The five articles don't reflect the change. Only new records will get "Papier" - the old records I have to change manually.

What would you recommend to get around that?

Is there a built-in functionality to achieve that or do I have to use a script?

--greg

[comment]

You need a relationship between the two tables, based on a meaningless ID. It's good practice to have such field in every table.

Table Groups:

• GroupID - Number, Auto-enter serial number

• Group - Text, Unique

Table Articles:

• ArticleID - - Number, Auto-enter serial number

• GroupID - Number

...

Relationship:

Groups::GroupID = Articles::GroupID

Define your value list to use values from Groups::GroupID, + 'Also display values from:' Groups::Group.

You can explore the various options at the bottom of value list definition regarding the second field.

Also note that there are some differences here between a popup menu and a drop-down list. But the important thing is that once you set it up this way, you can modify the Groups::Group field the way you want.

[comment]

One more note: if you define the value list to 'Show values only from second field', and format the Articles::GroupID field as a popup menu, it will work even without the relationship. But it will be difficult to show the actual group name of an article in other contexts.

[Gregor Alessi]

Hello comment

Thank you. I'm impressed how kind (all of) you are towards newbies on this forum, providing such detailed answers.

I don't get enough time to get into FM (I'm a full time sysadmin, studying CS alongside work)... I hope you can accept my excuse for not replying in such a long time.

I already had a GID in db_groups, but I couldn't find the right way to "explore" the options. I learn by relating to what works for sure, then abstracting my knowledge. So far the tutorial has served me well, but it was not yet enough for this issue, although it was mentioned. You helped me a lot. It worked.

If I may ask another related question:

In UML class diagrams we use the definition "cardinality" to specify how many objects can be instanced. I don't know the correct terms for ERDs. Anyway, here it comes as in a class diagram:

In db_groups I want ::article_group to be unique (cardinality:1..1), in db_articles::article_group I want the (now related) field to get its content from db_groups, but with a cardinality of 0..*, which means there may be none up to infinitely many occurences of article_group (as specified in db_groups).

This is what I described in plain english in my original post. How would an experienced FMP developer solve this? Just like me, using value lists, that get their entries from db_groups? The work-around with the pop-up-menus doesn't look promising in an application that should be in use for 5+ years.

On a sidenote: I think a "Read The Fine Manual" is good enough as an answer to this question, if you want to keep it short. What would help me a lot, would be the keywords that I should delve in or a specific chapter/book I should have an eye on.

Thank you again!

--greg

The goal is to have a stock software for a public primary school, including an inventory based on orders sent to suppliers and "sales" to teachers. I will release the beta under GPL, if there ever is one :-/

[comment]

I am not sure data modeling uses the same definition of "cardinality". I think in Filemaker you would describe your relationship as 'one-to-many'. In any case, the value list and the pop-up menu are merely user-interface elements - not a work-around nor a device to ensure data integrity.

This is usually done at the structure level. The 'one' is achieved by (a) the GroupID being unique (an auto-entered serial number, non-modifiable by the user, ensures that), and in this case also by (: the Group field being validated as unique.

Either one of these would be sufficient to ensure a group record is unique - but, because you want to be able to modify the group's name AND keep it unique, you need this double measure.

To ensure that an article is always a child of exactly one group, you'd validate the foreign key in Articles as not empty and member of the 'GroupIDs' value list. A further measure you can take to ensure referential integrity is having children automatically deleted when their parent record is deleted.

That said, i believe it's good practice to design the UI in such way that users are never confronted by a validation message.