I've been searching but I haven't found an answer so far... I might have just missed it.
I have a table of suppliers:
These suppliers for my customer need to be approved and a decision was made to have a related table that tracked approvals since a supplier could be approved for some time then expire or might actually be deapproved at some point.
So I have a related approval table:
So I might have a supplier:
Microsoft, 1 microsoft ave, redmond, wa
This supplier might have a list of approvals:
I'm interested in the MOST RECENT approval status. The others are maintained for sake of history and tracing.
In my supplier table I'm trying to define a calculated field that would be assigned the value of the MOST RECENT "approval status" from the related table.
I have tried:
GetNthRecord( SupplierApproval::approval status; ??? )
and I've replaced ??? with a bunch of different things. In psuedo code I need to do something like this:
Set current supplier calculated status to
find approval records with foreign key == supplier id
Sort desending on timestamp
Move to last record of the found set
return approval status
I could use a hint on how to do this in a nice clean way so my calculated field in my suppliers table is always up to date with the last status found in the approval list.
Now I know I could simply assign the status when the last approval status record is updated... In this case I'd simply have a text field in my suppliers table and force a script when creating a new approval status to assign the status to the supplier record.
This would probably work just fine but if the approval status records are ever manually deleted then things could get out of sync.
I do understand this kind of a calculation might have a performance hit but we are talking about 300 suppliers each of which may have over time like 10 approval statuses.
But based on all of this I'm open to other ways to do it.
Thanks in advance!