8 Replies Latest reply on Feb 7, 2016 2:17 PM by Steve Wright

    Confused about how or if I should use lookup in this situation.

    jcondle

      First thanks for the help I have receives so far in my quest to  build this database with file maker.

      The database I am building deals with pre-hospital med., basically ambulance data.

       

      Many of the fields are coded with integers which correspond to textual ambulance designations like "Medic4".

      I want to replace the integers with the text.  My guess would be to do this on import.

       

      In the data file there are ambulance entries basically the 1st,2nd,3rd and 4th ambulance to arrive represented by an integer.  The lookup table is a list of integers and with textual name of the ambulance. On most occasions there will be only two ambulances the database allow for four.

      import file 5517 entries 492 fields

      Field     sample value

      rig1d      10496                                  

      rig2d      10541

      rig3d      19119

      rig4d      10556

       

      Lookup File 220 entries 2 fields

      ridID     rigName

      10496   Eng23

      10541   5102

      19119   Z503

      10556   5112

      What I have tried so far.

       

      I have related the rigID field from the lookup table to the four rigID fields in the main database (rig1d,rig2d,rig3d,rig4d).  I have added the ::ridID field to the the layout, but of course it only get the first instance (the rig1d field).  Not sure how to get the others.

       

      On import I have tried to create a text field Rig1 and calculated field but I am confused about how to that. 

       

      Joe

        • 1. Re: Confused about how or if I should use lookup in this situation.
          BillisSaved

          Good afternoon jcondle,

           

          I hope your day is going well. I'd like to offer you some assistance, but I'm not quite sure what your goal is. What are you attempting to achieve? Thanks and have a great day!

           

          God bless,

           

           

          Bill

          • 2. Re: Confused about how or if I should use lookup in this situation.
            jcondle

            Bill,

                 Thanks for the response. I have a database with codes for the ambulance names.  I need the names to be in the database not the codes.  The work intensive way would be to do a find/replace in excel on all 220 codes.  I hoped there would be a better way to do it in Filemaker.  I just need to display the ambulance names, so I could keep the codes in the database and just some way to display them in a report. Or create new fields like (Rig1,Rig2,etc.) and some way populate them with rig Names from a lookup table.

            Thanks in Advance

             

            Joe

            • 3. Re: Confused about how or if I should use lookup in this situation.
              Steve Wright

              If you setup 4 relationships:

               

              DataTable  ------  rigLookupTable_1

              rig1d          =          rigID 

              .....

              DataTable  ------  rigLookupTable_4

              rig4d          =          rigID

               

              Then the rigName value will be available to you for each of the four fields, in the form of:

              rigLookupTable_1::rigName

              rigLookupTable_4::rigName


              At which point you could display it directly from the related tables, use replace field contents on a new field in the data table and remove the relationships etc..


              But, here's an alternative approach if you just want it to display the name but retain the rigID.

              Setup a value list using values from a field as follows:


              SampleVL.png


              Apply that value list to the data fields rig1d...rig4d.


              A quick and dirty sample file is attached with both of those in place.

              • 4. Re: Confused about how or if I should use lookup in this situation.
                Fahri Akar

                You did understand the question. Your solution is better. I have deleted my wrong posts not to confuse you.

                • 5. Re: Confused about how or if I should use lookup in this situation.
                  jcondle

                  Thank You

                  I will try this tomorrow when i get to work

                  Joe

                  • 6. Re: Confused about how or if I should use lookup in this situation.
                    BillisSaved

                    Good morning jcondle,

                     

                    I hope your day is going well. I may have misunderstood the issue you are experiencing, but is there a reason you couldn't add one additional field to your FileMaker Pro table, the one that contains the ambulance codes, and define an auto-enter calculation - ExecuteSQL, for example - that would insert the ambulance name that corresponds to the existing code? Have a great day!

                     

                    God bless,

                     

                     

                    Bill

                    • 7. Re: Confused about how or if I should use lookup in this situation.
                      jcondle

                      I have learned a lot about this process over the last week and I appreciate all of your help.  I know explaiing the issue is really tuff over these chat sessions. Please bear with me.

                       

                      I have only two tables.  Import file and the lookup file.

                      The Import file has many rigid references in it.  The lookup file has two fields the rigID and the rigName

                       

                      What I need is to have the corresponding rig names along with the rig IDs in my import file.

                      I have created 4 rig name fields as text to store the rig names that are looked up.

                      I have set relationships as follows

                       

                      rig1d |                                      

                      rig2d |  __________ RigID(lookup table)

                      rig3d |

                      rig4d |

                       

                      When I do this I get only the value for the first rig1d in the file.  The others (rig2d,rig3d and rig4d) which are different rigs come back with value rig1d1.

                       

                      If this is too frustrating my fall back position is to use find/replace in excel.

                       

                      Joe

                      • 8. Re: Confused about how or if I should use lookup in this situation.
                        Steve Wright

                        When you say you need it in your import file...that confuses me.   If you want it before importing, then where does FileMaker (after importing) come into it?  Are you wanting to import, process the data and then export a new file for later importing into another db, perhaps?

                         

                        The example I sent you above will allow you to do just that, but here's the same file with the 'value list' stuff stripped out and 4 new fields in your main data table, as an example. 

                         

                        These additional fields are just calculated fields pulling the information from the lookup tables.

                        Following this setup, you could import your data, then export again, including the additional 4 calculated fields.. in which case you then have the info in a new 'import file'