1 2 Previous Next 17 Replies Latest reply on Dec 8, 2016 1:49 PM by djc728

    Script to pull / look up a reference value from an unrelated table

    annr

      This is making me crazy--feels like it should be dead simple and I'm missing a basic concept.

       

      I have a reference table with two fields:

           TaxYear

           FilingDate

       

      I have a script where I want to use Year (EventDate) to look up the TaxYear column, and set a variable to the FilingDate value.

       

      Similar to how you might pull a product name from a code, or city from Zip. Should I just add a calculated field which sets the EventDate Year, and set a relationship between the tables? Seems like there should be a more elegant approach without the surplus field.

       

      Thanks again for your help. I'm looking forward to when I can start giving back on this forum!

        • 1. Re: Script to pull / look up a reference value from an unrelated table
          philmodjunk

          nothing wrong with a relationship, but a script can certainly be used to find a record and set a variable. You haven't indicated how this fails for you.

           

          The ExecuteSQL function could also be used to look up the filing date--though you might need to reformat the result back into a FileMaker date.

          • 2. Re: Script to pull / look up a reference value from an unrelated table
            annr

            It's failing because I'm missing a basic function/concept. Something probably absurdly obvious, especially to power users. :-)

             

            Lookup seems to require the table connection. I'd rather not delve into raw SQL if I can avoid it (another layer of complexity to baffle someone maintaining it after me). Will go ahead with the table relationship, unless someone has a more elegant approach.

             

            Thanks!

            • 3. Re: Script to pull / look up a reference value from an unrelated table
              philmodjunk

              Set Variable [$Year ; value: year ( YourTable::eventDate ) ]

              Go to Layout ["LayoutbaseOnReferenceTable" (ReferenceTable ) ]

              Enter Find Mode [  ] -- clear the pause check box

              Set field [ ReferenceTable::Taxyear ; $Year ]

              Set Error Capture [ on ]
              Perform Find [ ]

              If [ Get ( FoundCount ) = 0 ]

                 Show custom dialog [ "No record found..." ]

              Else

                 Set Variable [ $FilingDate ; Referencetable::filingDate ]

              End If

              Go to layout [original layout ]

               

              For more examples of scripted finds, see:

              Scripted Find Examples

               

              And in Execute SQL you might do this*

               

              ExecuteSQL ("

                    SELECT FilingDate FROM ReferenceTable

                        WHERE

                              TaxYear = ?" ;

              "" ; "" ; Year ( YourTable::EventDate ) )

               

              *Note: I do not actually write SQL queries like this as it "hard codes" field and table occurrence names. The method I use expands this simple query into something much larger that uses custom functions to get field and table occurrence names without enclosing them in quoted text. I've kept it simple here so that the basic logic of the query doesn't "get lost" in the more complex format that I actually use.

              2 of 2 people found this helpful
              • 5. Re: Script to pull / look up a reference value from an unrelated table
                erolst

                btw, LaRetta (from FMForums) once taught me to reverse these two steps:

                 

                Enter Find Mode [  ] -- clear the pause check box

                Go to Layout [ "LayoutbaseOnReferenceTable" ( ReferenceTable ) ]

                 

                Why load a found set that you're evidently not interested in?

                2 of 2 people found this helpful
                • 6. Re: Script to pull / look up a reference value from an unrelated table
                  philmodjunk

                  That's a very good point and one that I learned only this Summer at DevCon and one I still forget, but in many, many circumstances, it makes no observable difference. In others, however, it can make for much faster performance, not only for the current user but for the user base as a whole.

                  1 of 1 people found this helpful
                  • 7. Re: Script to pull / look up a reference value from an unrelated table
                    Malcolm

                    philmodjunk wrote:


                    That's a very good point and one that I learned only this Summer at DevCon and one I still forget, but in many, many circumstances, it makes no observable difference. In others, however, it can make for much faster performance, not only for the current user but for the user base as a whole.

                    There is nothing worse than arriving on a layout with a large record set in list mode that has several summary fields or calculated sums on it when you are on the WAN. Coffee time anyone?

                    1 of 1 people found this helpful
                    • 8. Re: Script to pull / look up a reference value from an unrelated table
                      philmodjunk

                      I do not disagree, but note that this happened on a layout with a) a lot of records and b) a lot of aggregate data in need of updating. When that happens the "find mode first" trick not only makes it faster for the user that ran the script but frees up resources that can lead to better performance for everyone else.

                       

                      But for other cases, there may be no observable difference should you access the layout while in Browse mode before entering find mode.

                      • 9. Re: Script to pull / look up a reference value from an unrelated table
                        DamianKelly

                        Often it doesn't make any observable difference but it never does any harm. Also just form a user point of view there are very few use cases where a user wants to see every record in a table.

                        • 10. Re: Script to pull / look up a reference value from an unrelated table
                          philmodjunk

                          The user will not see every record in the table in this case as this is part of a script.

                          • 11. Re: Script to pull / look up a reference value from an unrelated table
                            sai

                            I have 2 questions:

                            1) If you Enter Find Mode 1st, aren't you putting the active layout in find mode instead of the new layout?

                            2) Why do you need to enter find mode at all?  Why not just use Load Layout and Perform Find?

                            • 12. Re: Script to pull / look up a reference value from an unrelated table
                              erolst

                              sai wrote:

                              1) If you Enter Find Mode 1st, aren't you putting the active layout in find mode instead of the new layout?

                              You're putting the window in Find mode; barring other influences (eg a triggered script), a layout switch does not change the window mode.

                               

                              sai wrote:

                              2) Why do you need to enter find mode at all? Why not just use Load Layout and Perform Find?

                              Because I eschew the Perform Find [ restore ] approach in favour of Enter Find Mode, Set Field and Perform Find [ no restore ]. This is one of the places where I dislike dense/implicit code

                               

                              And loading the layout in Browse mode, including the records of a found set that is obsolete for the purposes of your script, is what we want to avoid.

                              • 14. Re: Script to pull / look up a reference value from an unrelated table
                                philmodjunk

                                A bit more info on that:

                                 

                                Say your layout is based on a table with 3 million records and several summary fields. You change to that layout in browse mode and Filemaker immediately starts evaluating summary values over 3 million records. That takes time and slows things down. If this is a hosted solution, this is worse as your Client sessions first has to fetch all 3 million records and then compute the summary totals. That's a bit like sucking up the pacific ocean thru a straw. It not only makes for a delay for the user doing this, it can slow down response times for any other users as your server is busy serving up all this data to one client. Make that 40 or 50 users all asking for the same 3 million records and you have a major, potential problem.

                                 

                                BUT

                                 

                                Say you only have 300 records in your table and no summary fields. Then, it make very little difference whether or not you first enter find mode as you are "saving nano-seconds" to loosely quote Grace Hopper--one of the founders of modern computer programming.

                                 

                                So it's a good idea, but it's also useful to keep the reasons Why such "best practices" are recommended since there may be times when, say to avoid issues with script triggers, it's simpler to stay in find mode as long as you aren't setting yourself up for trouble--either now or some time in the future after the total number of records in the table may have grown to much larger numbers than present.

                                1 2 Previous Next