3 Replies Latest reply on Jun 19, 2013 10:06 AM by philmodjunk

    Multiple field lookup

    MilutinPetrovic

      Title

      Multiple field lookup

      Post

           Hello friends,

           My problem is illustrated by an image bellow!

           I have 2 tables, one containing some values, other containing the Spanish translation of the said values (its for a multi-lingual db). So, the way it is setup right now, when You select the value from the field "First" it will do a look-up and fill in appropriate value in Spanish language from the second table. 

           My problem is, how to do that exact same thing with the multiple fields in that same table. I tried the "Many-to-one" connection, but it failed, and I am out of ideas, so I would be grateful if someone could help!


           Regards,

           M.

      Image.png

        • 1. Re: Multiple field lookup
          philmodjunk
               

                    when You select the value from the field "First" it will do a look-up and fill in appropriate value in Spanish language from the second table.

               Fill it in to what field? Using what method? (There's more than one way to use the relationship shown to do that.)

               This thread may help clarify your options: Auto Fill

          • 2. Re: Multiple field lookup
            MilutinPetrovic

                 There is a field called "First", "Second" and "Third", which are filled in from a value list, and there are fields "First | ESP" ,"Second | ESP" and "Third | ESP" which are supposed to be filed with a Spanish translation of the said fields, which are stored in the second table. 

                 Maybe all of this doesn't make any sense to You but my goal is to, if it is possible, not use the script for this thing, rather some kind of look-up option...

                  

                 __________________________________________________________

                 EDIT:

                 I took the look in the topic You have suggested, but those offer the solution where You fill in the multiple fields with regard to the value in the single one, and what I need is to fill in multiple fields through look-up...

            • 3. Re: Multiple field lookup
              philmodjunk

                   I asked the questions I did in my first post because it was not clear what results you wanted to achieve--it had nothing to do with whether or not they made sense to me.

                   

                        I took the look in the topic You have suggested, but those offer the solution where You fill in the multiple fields with regard to the value in the single one, and what I need is to fill in multiple fields through look-up...

                   That is quite not the case. It spells out two different approaches for looking up data from one table and displayinig it from the context of another table. The key difference in the two methods described is that one physically copies the data while the other dynamically displays the data from the related table.

                   In both cases, functional relationships between the two tables are required and this is what you do not have given the current structure of your test table. You can either redesign your table, add more relationships that link to additional Tutorial: What are Table Occurrences? of the Dictonary table or use ExecuteSQL (FileMaker 12 or newer only) to look up the needed value.

                   What you currently have only looks up a value in the First | ESP field and will not work for any of the other fields. You could, BTW, remove First | Esp from your table and just put Dictionary::ESP on your field and you'd see the same translated word/phrase. The difference between using First | ESP and Dictionary::ESP is the point of the thread to which I directed you in my previous comment. Both options are the best option in different circumstandes so you need to read that thread and decide for yourself.

                   If you opt to redesign your table, you can split up your data into separate records so that you only have a first and First | ESP pair of fields. The data that you now have in Second, would be placed in the First field of a different record and then your relationship will work. (and so forth for the other fields)

                   If you opt to add more table occurrences, you'd duplicate the Occurrence for Dictionary and link ENG to the Second field instead of First so that you can then have Second | ESP look up translated text from this new table occurrence. (You'd then repeat this for Third, fourth, etc.)

                   The third option avoids the need for added table occurrences, but requires a decent working knowledge of SQL. You up for that?