Join four related values in one field

[creacionweb]

Hello,

Well my first question here . I´m working with Filemaker since summer and this forum is always a great help for me from the beginning.

Let´s see if someone can help me (please excuse my bad english) because this is breaking my head.

I have the following structure:

Table: Articles

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

ID of Article

Category 1 (contains ID of category)

Category 2 (contains ID of category)

Category 3 (contains ID of category)

Category 4 (contains ID of category)



Table: Categories

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

ID of Category

Category name

I need to combine the four product categories names in one single calculation field for our header reports.

Example:

The four categories of the article are : 3, 5, 9 and 12.

I need the calculation field with the result of the related categories: Apples, Oranges, Watermelons and Pineapples.

There is any way for make this?

Thanks!

Daniel

[comment]

Do I understand correctly you have 4 relationships between Articles and Categories?

[Fitch]

I'd recommend you create a new table, let's call it ArticleCategories. This table would be related to Article (by ID of Article), and to Category (by ID of Category):

Article >- ArticleCategories -< Category

I.e., it's a "join" table.

This would take the place of Category 1, Category 2, etc. You could display and select your categories in a portal.

You could then use the List function like so:

List( Category::Category )

This would give you a return-separate list of related categories. You could then make it a single line:

Substitute ( List( Category::Category ) ; ¶ ; " , " )

[creacionweb]

Do I understand correctly you have 4 relationships between Articles and Categories?

Yes, I use it for value lists display in the interface.

Most of the values are taken from tables in a msql database from a RP solution, so I´m tied to this structure because I have to take and put data in the msql.

I'd recommend you create a new table, let's call it ArticleCategories. This table would be related to Article (by ID of Article), and to Category (by ID of Category):

Article >- ArticleCategories -< Category

I.e., it's a "join" table.

This would take the place of Category 1, Category 2, etc. You could display and select your categories in a portal.

You could then use the List function like so:

List( Category::Category )

This would give you a return-separate list of related categories. You could then make it a single line:

Substitute ( List( Category::Category ) ; ¶ ; " , " )

If I understand it well, table ArticleCategories have then the same article ID four times with every category.

ArticleCategories

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

ID of Article (001) Category ( Contains category 1 )

ID of Article (001) Category ( Contains category 2 )

ID of Article (001) Category ( Contains category 3 )

ID of Article (001) Category ( Contains category 4 )

ID of Article (002) Category ( Contains category 1 )

ID of Article (002) Category ( Contains category 2 )

ID of Article (002) Category ( Contains category 3 )

ID of Article (002) Category ( Contains category 4 )

etc

I made a test in a new file and the List( ) field works well! I just need to adjust this to my current structure.

Thanks Fitch!

[comment]

Do I understand correctly you have 4 relationships between Articles and Categories?

Yes, I use it for value lists display in the interface.

That's not what your graph shows.

[creacionweb]

That's not what your graph shows.

Yep sorry, when I were reading on the fly I mistaked it with the number of fields than gets the categories ID.

[comment]

Well, I hope you have worked it out, because at this point I have no idea what this is about.