Hi all,

Please could you help me out with this.

I have 2 tables... Table1 and Table2

Table1 contains orders

Table2 contains the status of the orders

In Table2 for each order there can be several records - each time an order status changes

The order status is changed via a portal to Table2 in Table1

So against a particular order in Table1 I will go to the portal and set the status to ordered, or processing, or shipped etc

Each time I change the status it is creating a record in Table2 just with "OrderNo" and "Status" and a date/time stamp.

Now a seperate layout in Table1 I want to show the current status, so I reference using the relationship to table2. relationship is orderNo=orderNo

The problem is it returns the status of the first record it finds for that particular order number. So this will be the very first status this order was set to. It disregards any further status.

I need it to return the last.

Rather than going to Table2 to get this, can I pull it from the top line in the portal?

Hope this makes sense!

Thanks for any help.

Use the Last function.

Create a new table table occurrence ( since you use the orig for the portal) and sort it by date descending. Put the field on the layout. This way you will not have to create a calc to do this.

Another option is a one-line portal, sorted by date descending. That way you don't have to modify your schema at all.

Thanks all for your suggestions. I have gone with the 1 line portal, works perfect. Easy when you know how!

Thanks again