Multi Criteria Lookup
FileMaker Help mentions a "Multi-Criteria Lookup"... does this mean i can set up a lookup script that identifies a record in a separate table based on multiple fields and not just one? For example: Lookup (F1=AB,F2=BB,F3=BC) then lookup what appears in F4? Right now I am using a find/copy/paste script, but a lookup seems like it would be more efficient. Thanks.
I believe you're referring to a multiple criteria FIND which is indeed possible and scriptable. If I'm not correct then Comment's post is right on. If you're using a "find/copy/paste" script perhaps you should be using a "find/set variable/ set field" type of scenario. "Not using copy and paste" ought to become a "golden rule". I've eliminated copy and paste from everything I do in FM. "Lookup" refers to either an auto-fill (or really a type) of a field (and very useful in some circumstances) or to the "Lookup" function which has some interesting advantages in a calculation field . . . all in the manual of course.
RW
Can I use "set field" and to set to a variable from a related table?
I tried using "set field" and specified a field in a related table in the calcualtion, assuming it would grab the value from the record currently in view, but my assumption was wrong and it did not work.
The script step looks like this:
Set Field [Table1::f1;Table 2::f5]
I really appreciate the advice. Thank you!
afubeca wrote:I tried using "set field" and specified a field in a related table in the calcualtion, assuming it would grab the value from the record currently in viewIt depends on where you are at the moment:
If you are on a layout of Table1, then you will be setting the value of the current record in Table1 to the value taken from the first related record in Table2.
If you are on a layout of Table2, you will be setting the value of the first related record in Table1 to the value from the current record in Table2.
There's no such thing as a "multi-criteria lookup" - but there can be a multi-criteria relationship (or more accurately, a multi-predicate relationship). So you can define your relationship as:
TableA::F1 = TableB::AB
AND
TableA::F2 = TableB::BB
AND
TableA::F3 = TableB::BC
then define another field in TableB to lookup from TableA::F4.