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:22.214.171.124) Gecko/20100202 Firefox/3.5.8 GTB6
Filemaker Pro 10 and 11
fairly new to Filemaker, but programming experience.