Value list made of pairs in a value list from another table

[climbd]

I'm a Filemaker novice, but have done quite a lot with MySQL, so the concepts aren't new, just the ways to do them

I have a filemaker table of categories it has two simple fields. ID and catagory.

example:

ID Category

1 Red

2 Blue

3 Green

In another table, I want to assign one ore more categories to a product through a value list populated by the category table. When the user see the options to check them, I want it to show the category names but I want it to save it as the ID value(s)

Or at minimum be able to export the field as a list of the category ID values rather than a list of the category names.

I'm sure this is super easy and I'm just missing something, but it appears that value lists are just lists of values and can't tie back to the actual ID's associate with the category name. Any help or advice would be greatly appreciated.

[Russell Barlow]

I'm a Filemaker novice, but have done quite a lot with MySQL, so the concepts aren't new, just the ways to do them

I have a filemaker table of categories it has two simple fields. ID and catagory.

example:

ID Category

1 Red

2 Blue

3 Green

In another table, I want to assign one ore more categories to a product through a value list populated by the category table. When the user see the options to check them, I want it to show the category names but I want it to save it as the ID value(s)

Or at minimum be able to export the field as a list of the category ID values rather than a list of the category names.

I'm sure this is super easy and I'm just missing something, but it appears that value lists are just lists of values and can't tie back to the actual ID's associate with the category name. Any help or advice would be greatly appreciated.

In the Value List Creation, one of the options is Use Values from Field.

When you are defining this field, you will notice two columns. If you pick the ID field from the first list, you can choose the category name from the second list after checking the box Also Display values from second field. Down below that, there is a box that says, Show values only from Second Field. Make sure that is checked and you are good to go.

[bcooney]

In another table, I want to assign one ore more categories to a product through a value list populated by the category table. When the user see the options to check them, I want it to show the category names but I want it to save it as the ID value(s)

This is a many-to-many relationship, and is resolved by using a join table between the two parents. That is, you need a ProdCat table to store the combinations of Product and Category. You would create a portal to this join table on the Product form layout, and relate Products to the ProdCat table by ProductID. Now all the user needs to do is specify a CategoryID (using a value list, CatID and CategoryName). If you format this field in the portal to be a popup menu, they will not see the CatID.

The demo attached might be worth your time taking apart.

ManyToMany.fp7.zip

[climbd]

Thanks Russell, that did the trick! Also thanks to bcooney for posting. I can see how a many to many relationship could be useful, too but my current need is more basic.