1 Reply Latest reply on Mar 26, 2010 7:24 AM by JoshO.

    lookup via 2 related tables, 2 databases, with formula not Auto "Look-up Value"



      lookup via 2 related tables, 2 databases, with formula not Auto "Look-up Value"


      Hello all fellow FMPro users!


      I hope I can find a bit of help on a process.....

      Here is the background on what I have.

      2 databases Master(many fields) and Reference(34 Field Names), indexed and related by date field.

      The user enters 3 data, and I need to calcuate based on input for Field Output in the Master Database.

      1.  selects a date from to be stored in Master database, this is then looked up in the related row in in Reference

      2. Enters an amount in Value field, stored in Master database, setup and working

      3. Selects Field Name from a drop down list from the Reference database, set up and working

      4. Now I have a row and a Field name to look up in the Reference.


      I can use Look-up Value just fine to fill in a value, but I have to manually set in the Look-up value function which Field name to look up in the related table, but this only one Field look up per field. But I have this working for a few fields via this method.

      But the issue is I need to look up the Field Name in the Selected field name that the user enters, so I cannot use this auto Look up feature.

      I am trying to use GetField ( "Selected Field" ) function. This obviously doesnt work, as the only database I can reference this from is the Master database, because the Field Name is a related database via date, Reference Database, and  doesn't show up in the "options" screen at  the top of the formula screen of a field to search that database.


      I really want the Reference database to only contain the data to look up, only numbers. I could recreate the the table in the Master Database, but this is not efficient use of resources, IMHO, and requires extra management, as the Reference database is updated daily, etc...


      So... how do I construct a formula like the "look up Value" with related table information for a row, where it finds the Field name based on the user selected value?

      Am I missing something to use the Refrence database in the formulas window? I of course have the Reference set up as a External Database, and as explained, and can do it through the Look up Value function, but it doesn't allow me to use a variable to look up a value based on a user input..... which would be a great feature!!!!! ... unfortunately not in FMPro 11, which we just upgraded to.


      Thanks for any formula ideas, or ways to get this to work!!



      Kirk @ servingaudio.com


      Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv: Gecko/20100202 Firefox/3.5.8 GTB6

      Filemaker Pro 10 and 11

      fairly new to Filemaker, but programming experience.








        • 1. Re: lookup via 2 related tables, 2 databases, with formula not Auto "Look-up Value"

          Use a Script Trigger to fire a script.  Which one you use will depend on your workflow.

          In the script, use the Set Field by Name step.  Use that to specify by calculation which field you want to set.  If may be useful to pass a script parameter to the script.  And use the Get (ScriptParameter) function to indicate the Field Name you want to set.  (That last part will work best if you know that you want an action on this field to always populate that field in the related file.


          If your relationship relates more than a single record in the other file, you will need some way to determine which record you want to affect.


          You may have questions, being fairly new to FM, but that should get you moving.  Ask more questions if you aren't sure how to accomplish something.