12 Replies Latest reply on Jan 29, 2016 8:11 AM by tkemmere

    How to lookup in a large reference-table (2)

    tkemmere

      Dear all,

       

      (I posted this question before, but recieved no answer, so I'll try to pose my question more compact and to the point).

       

      I would like to set up something I didn't set up before in a database. I'm working in a Table A, in which for a certain record a value needs to be looked up in another table (Table B). The lookup needs to be done, based on 4 parameters.

       

      Table A has several fields:

      • ID
      • Content 1
      • Content 2
      • Parameter 1
      • Parameter 2
      • Parameter 3
      • Parameter 4
      • "Outcome field 1", calculated by looking up, a value in Table B, using the 4 different parameters.

      ...and some records.

       

      Table B has 6 fields:

      • ID
      • Parameter 1
      • Parameter 2
      • Parameter 3
      • Parameter 4
      • The value to be looked up.

      ...and 600 records.

       

      So there are 600 different values, depending on the combination of parameters.

       

      My questions are:

      • How do I link these two tables to one-another?
        • Should I put 4 relations between each of the parameters?
        • Should I use an intermediate table? But then, wouldn't every lookup bloat that table with 600 new records?

       

      Maybe I shouldn't use the term 'lookup' cause that is also a command, and I might be best off with find record ().

       

      In any case I'll be sorting my tables now, and allign the parameters and I'm going apply trial and error, see whether I'm on the right track.

       

      Is any one available to assist? Many thanks in advance.

       

      Regards, Thomas.

        • 1. Re: How to lookup in a large reference-table (2)
          mikebeargie

          When you say "looked up value", that implies a value you want to copy from table B, to table A, for permanent storage.

           

          An example of this is "current fuel price", if I bought 10 gallons of gas, I would do a lookup for the current rate. This way I could store the rate permanently in my "purchases" table, while the prices constantly fluctuate in the "rates" table.

           

          So in your case, if you are only storing a value in "outcome field 1", then you are correct in thinking that the 4 other match fields would be part of a relationship. I don't know what your data is, but I can probably speculate that you do NOT need a join table between the two tables if you are just interested in populating a looked up value.

           

          Another thing you could do is set a value in Table A using an ExecuteSQL() function, this requires NO relationships to table B, and possibly saves you fields in table A as well since no relationship is needed. It would look something like this:

          ExecuteSQL("SELECT TheValueToBeLookedUp FROM TableB WHERE Parameter1 = ? AND Parameter2 = ? AND Parameter3 = ? AND Parameter4 = ?";"";"";TableA::Parameter1;TableA::Parameter2;TableA::Parameter3;TableA::Parameter4)

          • 2. Re: How to lookup in a large reference-table (2)
            tkemmere

            Hi Mike,

            Thanks! Indeed, I'm interested in looking up a value for permanent storage.

            I think I'll go the ExecuteSQL() way, then I don't have to bother controlling 4 relationships.

            Thanks for giving me this direction.

            Cheers, Thomas.

            • 3. Re: How to lookup in a large reference-table (2)
              mikebeargie

              To confirm, it was one relationship with 4 parameters for matching. Not 4 relationships.

              • 4. Re: How to lookup in a large reference-table (2)
                beverly

                Yep! it's a little like making an AND FIND with 4 criteria (from TableA, pushed into variables) and used in TableB's fields during the find request. Then with a "found record" in TableB, get the value that will be pushed back into TableA::Outcome field 1

                 

                Mike's query is so much easier.

                 

                beverly

                • 5. Re: How to lookup in a large reference-table (2)

                  At some point, consider using a remote data IDE for working with SQL, which uses a direct connection to your FM database.

                   

                  With any SQL beyond the most basic, I find trying to get past the "?" you end up getting in the Data Viewer and then only seeing a few rows in the non-resizeable window is very frustrating.  Plus, SQLExecute() only supports SQL SELECT.

                   

                  Once I get the SQL working, it's a snap to move over to FMP (since the SQL you work on is actually connected to your actual FMP database) add SQL parameters, if needed, and the other SQLExecute options.

                   

                  Plus in a remote IDE you can resize windows, control fonts, export data, take advantage of SQL SELECT, UPDATE, DELETE, and INSERT.

                   

                  FileMaker includes the JDBC driver in the installation files. It's really good.

                   

                   

                   

                  - m

                  • 6. Re: How to lookup in a large reference-table (2)
                    tkemmere

                    Great all,

                     

                    Thanks for thinking along. I'm very happy with this forum. I try not to abuse it by just lazily asking stuff, but only when I have no idea. It is often just a direction in which to best think, that I need. Like in this case.

                     

                    mikebeargie,

                    Yes you're right. When you drag 2 relationships between two TOs it actally is one relationship, with 2 parameters. So after dragging between all 4 parameters in the opposite tables, the result is 1 relationship that forks on both ends to the respective 4 parameters.

                    This would be easy I guess, if the fields were perfectly matching. I think that then there would be only one single related record with my Outcome filed ready for the picking! But the reality is that some of the parameters first need to be calculated upon and are stored in variables.

                     

                    beverly,

                    With 'Query' you mean the ExecuteSQL() right? I've worked with it once and have no routine as yet, but I'm determined to learn! :-)

                     

                    disabled_morkus,

                    Thanks for the tip. I understand that the 'Remote IDE' is a form of external SQL script developer. I'll keep it in mind. But I don't plan to get beyond the really beginners level for now. So I work in the Script Workspace and Specify calculation window. Which I have full screen :-)

                     

                    I actually just flipped back to the browser to find out how to exactly use ExecuteSQL() when I saw your messages :-)

                     

                    'll keep you posted. T.

                    • 7. Re: How to lookup in a large reference-table (2)
                      beverly

                      Yes, T! SQL = structured query lanquange

                       

                      As my post mentioned, FIND could be done to get the necessary values by passing variables between tables. But the query (SQL statement using ExecuteSQL function) is much easier and more flexible.

                      beverly

                      • 8. Re: How to lookup in a large reference-table (2)

                        Bev -- it's interesting to note that aside from the universal agreement that "SQL" means "Structured Query Language", it apparently didn't start out that way.

                         

                        In Alan Taylor's (now ancient 1995) book "SQL For Dummies", he makes this point by showing the history of SQL and how it came to be and, most importantly, that SQL really didn't stand for ... anything.

                         

                        It's all a moot point now, but just say'n. 

                         

                        - m

                        • 9. Re: How to lookup in a large reference-table (2)
                          beverly

                          Sort of like the "meaning" of PHP or JDBC. moot moot! LOL

                           

                          -- sent from myPhone --

                          Beverly Voth

                          --

                          • 10. Re: How to lookup in a large reference-table (2)
                            tkemmere

                            Moot moot good mood! Nice.

                             

                            ... I came up with this so far, but it just yields only questionmarks.

                             

                            ExecuteSQL

                            (

                            "

                            SELECT OutcomeField1 FROM TableB

                            WHERE Parameter1 = $Parameter1

                            AND Parameter2 = $Parameter2

                            AND Parameter3 = $Parameter3

                            AND Parameter4 = $Parameter4

                            "; "" ; "" )

                             

                            (Before this scriptstep I set the variables $Parameter1, ...2, 3 and 4 of course).

                             

                            Am I basically on the right way, and it is just one of those common date-text-number mismatch or so silly things, or am I doing it all wrong?

                             

                            Mike's initial response had this long ;TableA::Parameter1;TableA::Parameter2;TableA::Parameter3;TableA::Parameter4 string behind it. That I can't seem to place, so I omitted it so far.


                            By the way, there is never a situation that 2 matches are found. Each parameter combination is unique.

                            It's tough to find tutorials or youtube vids that show this kind of beginner-level stuff.

                            I'll keep going.

                            • 11. Re: How to lookup in a large reference-table (2)
                              mikebeargie

                              Try this, the ? marks are placeholders for variables you pass at the end of the function. What you posted will not work.

                               

                               

                              ExecuteSQL

                               

                              (

                               

                              "

                               

                              SELECT OutcomeField1 FROM TableB

                               

                              WHERE Parameter1 = ?

                               

                              AND Parameter2 = ?

                               

                              AND Parameter3 = ?

                               

                              AND Parameter4 = ?

                               

                              "; "" ; "" ; $Parameter1 ; $Parameter2 ; $Parameter3 ; $Parameter4 )

                               

                               

                              The ? marks are evaluated in order, so you can place as many question marks as you need and then pass in the variables in an unending semicolon delimited list at the end of the function.

                               

                              The advantage towards passing in variables is that filemaker takes care of properly formatting the strings, EG if you have spaces, or date/times, it will auto-cast them into the proper SQL syntax for you. Nifty!

                              • 12. Re: How to lookup in a large reference-table (2)
                                tkemmere

                                Yeeeeeey! It's working!!!

                                My day is totally good. Thanks to you all!

                                Have a VERY nice weekend!