AnsweredAssumed Answered

Get a value from a field of the LATEST record of a related table

Question asked by marksystech on May 18, 2016
Latest reply on May 18, 2016 by BowdenData

Hello all...

 

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:

 

id

name

address

city

etc

calculated status

 

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:

 

approvalid

fdid_suppliers

approval status

approval timestamp

approval by

approval notes

 

So I might have a supplier:

 

Microsoft, 1 microsoft ave, redmond, wa

 

This supplier might have a list of approvals:

 

approved 01/12/14

expired 01/01/14

approved 05/05/13

 

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!

Outcomes