RELATION Reading latest record instead of oldest

[Brent Vinton]

How can I get information from the newest record in a database to post to another related database record instead of the oldest?

We have a new order taking form/database related by phone number to a completed invoice form/database. When a new order is taken, the customer's phone number is entered and his name & address are automatically entered if we have ever been to his home. One customer may have several records with the most current information being on the newest record. Filemaker looks for information on the oldest record instead of the newest. I don't want to make a third data base with customer information because of the volumn of orders we take.

Can I have Filemaker automatically read the newest record instead of the oldest?

[LaRetta]

Hi Brent,

So you want the last line entered from each Customer record. You can create a relationship (self-join) from CustomerID to CustomerID, as this is the set you want to isolate.

Then create a calculation (number) called RecordID with: Status(CurrentRecordID) or if you already have a unique serial number you can use that.

Then create a calculation (number) with: Max(SelfJoin::RecordID) = RecordID

This will boolean flag a '1' on the last line of each Customer record.

LaRetta

[SassyTexan]

Of you already have the relationship, edit it and sort the field, i.e. phone number, by descending order instead of a straight relationship. You can do this when defining the relationship, it will be located on the roght column at the bottom. Click sort, then define how you want to sort, highlight the selection on the right of this dialog and select descebding. That way you will always have the newest instead of the 1st record!

I hope this helps.