Field lookup multiple results

[bigmac]

When i'm doing a field lookup (I have two databases, and they are linked via partnumber, and the invoice number is looked up), it always gives me the oldest invoice number, even though I have multiple invoice numbers matching the part number.

How can I make filemaker lookup the LAST matching record given instead of the first?

[LaRetta]

Lookups grab the first related. You can sort your relationship, reversing so the last record is the first (sort by Invoice date or something). Then your lookup will work properly.

[pjdodd]

How do change it so the lookup gets the nth related record?

[Vaughan]

You cannot.

Use an auto-enter calculation instead.

[pjdodd]

I'll re-phrase the question.

Your on table A want to copy fields from many records in Table B to new records in Table A. Using a look up and calculation along the lines of Set Field Table A::Field1 to Table B::Field 1 always calls up the first related field. How do you get the nth record in Table B?

[Vaughan]

If you're using FMP 7 or earlier, you cannot. The GetNthRecord() functions was new in FMP 8.5.

It sounds like you need to script the process: loop through the Table B records and create new records in Table A.

But all of this sounds like a hack, needed to work around bad data design.

[pjdodd]

Quite right it is a hack. The data design has been modified from the original design and it's too far gone to back track.

I thought of a really crude way of cycling through each source Table and condensing each record into one line, using a unique character to denote end of fields. Then jumping to the destination table and populating the fields using the MiddleValues function. I really wanted to avoid that because of the headache and mess it could cause. I also wanted to avoid export and re-importing using a custom match.

Would an upgrade to 9 mean that GetNthrecord would work, I mean how does that command know which Nth record to get??

[Vaughan]

"how does that command know which Nth record to get?"

It needs to be specified in the syntax of the function.

[Søren Dyhr]

Why not read here:

http://www.filemaker.com/help/FunctionsRef-39.html

--sd