Calculation based on related table expiry date

[Chrism]

hi,

 

I have a Client table, with a related table containing certification dates

I need a calc to look at the related table and check that at least one in the list has not expired (older than todays date)

I also different query to look at the the same table, but need to specifically look at a type as well, so in this case need to check that type1 has not expired

Thanks!

[comment]

Do you really need this as a calculation, or do you only need it for display? The latter is easy to accomplish using filtered portals, the former is more complicated.

 

[Chrism]

Hi,

Think I do?  I want to automatically show on the client record that the above items are basically 1 or 0.

I already have this Calculation that does it, but I am now moving the Certification records out into there own related table, and want to replicate what i have.

Let ( [ today = Get (CurrentDate) ; criteria1 = ISO9001 expiry date ≥ today or ISO17021 Expiry ≥ today ] ; criteria1 )

Edited January 9, 2021 by Chrism

[comment]

1 hour ago, Chrism said:

Think I do?  I want to automatically show on the client record that the above items are basically 1 or 0.

If all you need is a visual indication then no, you don't need a calculation. Place a one-row portal to Certifications on the layout of Client and filter it to show only records that haven't expired yet. Any object placed in the portal row will be visible only when there is at least one certification that passes the test.

 

[Chrism]

Hi,

Yes I need more than just a visual indicator, I need to be able to include it it search results etc, but will have a visual indicator based on the result

If I can have a calculation like the one in my previous post, but is looking at the related certifications table instead. 
 

[comment]

You can have a calculation but as I said, it's not going to be as simple. Basically, you could always loop over the related records - but we are trying to avoid that.

If the certifications are entered in chronological order, and the relationship is not sorted otherwise, then it would be sufficient to test for:

Last ( Certifications::ExpiryDate ) ≥ Get (CurrentDate) 

If one of these assumptions is incorrect (or if certifications can have different periods), then:

Max ( Certifications::ExpiryDate ) ≥ Get (CurrentDate) 

should accomplish the same thing, albeit a little less efficiently.

 

So far this is pretty easy. Your additional request to filter by type is more difficult. I would suggest you add an unstored calculation field to the Certifications table =

Type = 1 and ExpiryDate ≥ Get (CurrentDate)

then sum this field from the context of Client.

 

Do note that the original (stored) fields are much more suitable for searching than these calculation fields.

 

 

[Chrism]

Perfect as always, do exactly what i need.

Thanks!