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?

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.

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

You cannot.

Use an auto-enter calculation instead.

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?

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.

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??

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

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

Why not read here:

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

--sd