6 Replies Latest reply on Aug 19, 2009 7:30 AM by carlsson_1

    Advanced Relationship inquiry



      Advanced Relationship inquiry


      How do you guys solve this problem?


      I have three tables:




      This is the parent Table where you choose which Colors that are available for this Article.
      Fields: Color ID


      Every Article is made in 1-8 languages. Every record here is a Child of a record in ARTICLE.
      Fields: Article ID,  Language ID



      All 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



      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.




      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! :)

      Any suggestions?





      Sample from the current build 

        • 1. Re: Advanced Relationship inquiry

          It would seem that ARTICLE would have Article ID, and a field to contain the English names for all the possible colors.  It would be a checkbox set, based on a static value list.  Then, your ARTICLE LANGUAGE table would have Article ID, and Language ID, plus a calculation field (ct_colors) = to the Color field in ARTICLE, and then the calculation field (ct_translated_colors )for the colors in the correct language.  Finally, COLOR would contain the Language ID, the English color name, and the appropriate language color name.


          Relate ARTICLE to ARTICLE LANGUAGE by Article ID.  Relate ARTICLE LANGUAGE to COLOR by Language ID and ct_colors to Language ID and the English color ID.  Your calculation field, ct_translated_colors, will be based on that relationship, like this: ARTICLE LANGUAGE::ct_translated_colors = Substitute ( List ( COLOR:translated_name ), ¶, ", " )

          • 2. Re: Advanced Relationship inquiry

            Thanks for the answer.


            However, I do not want the Checkbox set to be from a static value list. But your suggestion is on the right way I guess, I need a separate table with all the colours. With that I'll have all the options I need.



            • 3. Re: Advanced Relationship inquiry

              Hmmm... It turns out, no matter how I create the colors for every language, I *must* do a Refresh before the checked colors shows up correctly.


              Is this a limit in Filemaker or have I made something wrong?

              • 4. Re: Advanced Relationship inquiry
                   It could be that the calculation fields are being stored.  In the Storage Options... for the calculation fields, try clicking Do not store calculation results -- recalculate when needed.
                • 5. Re: Advanced Relationship inquiry

                  Hi carlsson,


                  the approach you use look fine to me. The trick is to have a colours table and a colours_language table that contains the translations for the colours. The last table then needs 2 table occurrences in the graph:

                   Relationship graph

                  The first toc is find the correct translationvalues for the chosen language and chosen values in article and the second is to find all the translationvalues for all the colours that are available as a choice.

                  Then you have a record looking like this:

                  Record example

                  If you change the _FkColour field, values of the colours in the portal change immediately.


                  You can download the file if you like:Examplefile:Colours.zip


                  Hope this solves your problem



                  • 6. Re: Advanced Relationship inquiry


                    Thanks for your extensive answer!


                    Since I have 99 colors it's not practical to show all the checkboxes in every related record. Look at my picture in my first post, the colors should be able to fit at the bottommost row (with a comma between the values).


                    To solve this I created a calculation field that looks like this:

                     Substitute( List ( Article | ArticleLanguage | ColourLanguage::Translation ); ¶; ", " ) 


                    This still needs a refresh though! I cannot get this to work without a refresh, neither in your or my file.


                    I tried the "Get(ValueListItems)" as well, but that only gives me the numerical values for the colours. 



                    More good ideas?  


                    Thanks again, 

                    Juicht toe! :)