eddyb2 Posted March 19, 2009 Posted March 19, 2009 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.
mr_vodka Posted March 19, 2009 Posted March 19, 2009 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.
Fitch Posted March 19, 2009 Posted March 19, 2009 Another option is a one-line portal, sorted by date descending. That way you don't have to modify your schema at all.
eddyb2 Posted March 20, 2009 Author Posted March 20, 2009 Thanks all for your suggestions. I have gone with the 1 line portal, works perfect. Easy when you know how! Thanks again
Recommended Posts
This topic is 5727 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now