How to calc the latest value from the joined table

[AlexKite]

Hi,
Please help to find the solution:
I have two tables in database join:
The table "Shipments" has a database join to table "Shipment_Status".
Each Shipment has many statuses, and I want to calc the latest (by date/time) status, as follows:

So, if, for example, Shipment1 has 3 statuses as follows, I want to get the value "Received" as a result of calc plugin:
2017-05 "Shipped"
2017-06 "Customs Clearance"
2017-07 "Received"

Thank you in advance!

 

[cheesegrits]

So does Shipments just have the one join to Shipment_Status, which gets changed?

And you somehow want to record what dates it got changed to Shipped, Customs Clearance and Received?

-- hugh

 

[AlexKite]

So does Shipments just have the one join to Shipment_Status, which gets changed?

Yes, one join to Shipment_Status.
One shipment have many statuses- each status with its own date, and user adds new date+status when the shipment moves on.
And in list view of Shipments I want to see only the last status per one shipment, not all linked lines with statuses.

I guess this can be done by adding a Calc element to Shipments (without even storing the value in Shipments), but i'm the PHP beginner and cannot think out the needed Calc formula without your help.

 

[cheesegrits]

I'm still not sure how you are doing this.

Does each shipment have a single record, which starts as status "Shipped", then you edit that row and change status to "Customs", etc?

Or do you enter a new row for each change of status?

If you are editing the same row, that makes it very difficult, as you have to then maintain a separate journaling table that records when status was changed.

-- hugh

 

[AlexKite]

I'm still not sure how you are doing this.

Does each shipment have a single record, which starts as status "Shipped", then you edit that row and change status to "Customs", etc?

... maintain a separate journaling table that records when status was changed.

-- hugh

Yes, each shipment have only one row and I need a separate journaling table with all statuses+dates of this very shipment - like on DHL/TNT cargo tracking page.

 

[cheesegrits]

OK, that's not something I can help with in Community. You'd need to write a little code in a PHP form plugin, that checks to see if the status has changed, and if so, insert a new row into the tracking table with the status and date.

-- hugh

 

[AlexKite]

write a little code in a PHP form plugin

Thank you Hugh, I'll examine PHP form plugin and increase my programming skills ))