I am sure many of you out there have a better brain and eyes that I. I need a bit of help making this lookup work.
FormulaFill lookup("ExternalDatabase",FieldValue("ExternalDatabase", ""+A), ""+B,FieldValue("ExternalDatabase", ""+C),"",0)
A, B and C are variables that identify FieldNames. A and C contain the names of FieldNames in the external database. B contains a FieldName in the active database.
Thank you in advance for the help.
The KeyField, and DataField parameters are the names of the fields, not their values, so you don’t want to use the FieldValue( function for those parameters, because that would return the value in whichever record was active in the external database rather than the name of the field.
The DataValue parameter, on the other hand, is a value, and you will need to use the FieldValue( function, if the B variable is to tell it which field to draw that value from.
FormulaFill lookup("ExternalDatabase", ""+A, fieldvalue("", ""+B), ""+C),"",0)
I haven’t actually tried this, so there’s no guarantee, but it looks right.
I haven’t tried Dave’s solution either, but FWIW it also looks correct to me.
However, since you have access to Denali, I would suggest using the join statement instead, it will be much faster. Like Dave, I haven’t tested this, but I think this is what you want. You will have to change CURRENTFIELD to the name of the field you want the data put into.
join "ExternalDatabase",
"Key",A,
"SourceKey",fieldvalue("",""+B),
"«CURRENTFIELD»",C
Also, I would sure suggest using more descriptive names rather than A, B and C, just to make the code more readable.
Note that in this case I am not building a relation in advance with the dialog, I am setting up the relation “on-the-fly” in the code.