Jump to content

Calculation based on related table expiry date


Recommended Posts

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!

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
Posted (edited)

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 by Chrism
Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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. 
 

Link to post
Share on other sites

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.

 

 

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.