Advanced Relationship inquiry
How do you guys solve this problem?
I have three tables:
ARTICLEThis is the parent Table where you choose which Colors that are available for this Article.
Fields: Color ID
ARTICLE LANGUAGEEvery Article is made in 1-8 languages. Every record here is a Child of a record in ARTICLE.
Fields: Article ID, Language ID
COLORAll available colors in different languages. Eg, Color Code 1 is "Svart" in Swedish, "Black" in English and "Schwartz" in German.
Fields: Language ID, Color Code, Color Name
PROBLEM:In ARTICLE I may choose 1-99 colors from a Checkbox set Value list. When I do that I would like the Colors to be visible in ARTICLE LANGUAGE in their respective language.
I choose which Colors I want in ARTICLE. That field is not available in ARTICLE LANGUAGE. Filemaker doesn't allow relations between more than two tables, which means I must get the field ARTICLE::Color ID to show up in ARTICLE LANGUAGE in some way.
APPROACH:I can make this work, but it seems awkward, and there must surely be a better solution.
If I make a Calculation field in ARTICLE LANGUAGE::cColor that calculates the data from its parent record in ARTICLE, this works. But I must make a refresh, and it doesn't feel very solid.
I can make a "Submit" button that Sets the information in all Child records, but then I miss one of Filemaker's features which mostly is "Instant relation" (or how to say it).
I have stumbled upon this problem (ie, relations from both Parent and Child, to a third table) from time to time, now it's time to do this the RIGHT way! :)