Change Value in one table based on it's non-existence in other tables

[4Justme2]

I'm looking for some suggestions on how I might go about doing the following:

My main table (TableA) contains an ActiveStatus, text field. My main layout page is based on the context of TableA and the ActiveStatus field defaults to checked ( or active) everytime a new record is created (added to TableA).

key fields in TableA

ID_PEO.pk

Name

ActiveStatus

CreateDate

People should be considered to have an ActiveStatus (checked) for the first 18 mos they exist in the system, and/or if they have a ActivityDate in TableB or TableC (below) within the most recent 18 mos period.

I'm wondering what the script, calc or funtion might look like to atomate this so that if they don't have activity in Tables B or C within the most recent 18 mos period the ActivityStatus field in TableA becomes unchecked automatically.

key fields in TableB

ID_B.pk

ID_PEO.fk

Name

ActivityDate

key fields in TableC

ID_C.pk

ID_PEO.fk

Name

ActivityDate

[comment]

People should be considered to have an ActiveStatus (checked) for the first 18 mos they exist in the system, and/or if they have a ActivityDate in TableB or TableC (below) within the most recent 18 mos period.

I am not sure what you mean by "and/or".

[4Justme2]

I am not sure what you mean by "and/or".

Sorry, that was poorly and inaccurately worded on my part. (I have some difficulty explaining things in written format). I can correct and simplify this:

ActiveStatus should be unchecked in TableA if there has been no activity in TableB or TableC witthin the past 18 mos. That would do the trick.

[4Justme2]

Sorry, that was poorly and inaccurately worded on my part. (I have some difficulty explaining things in written format). I can correct and simplify this:

ActiveStatus should be unchecked in TableA if there has been no activity in TableB or TableC witthin the past 18 mos. That would do the trick.

I'm just now thinkiing the reason I had explained it as and/or was because it's possible and even likley that we enter someone in TableA and don't necessarily enter activity for them in TableB or TableC right away. When that's the case, they won't have any activity in TableB or TableC but we don't want their ActiveStatus to be unchecked.

[LaRetta]

A regular field in one table cannot see either 1) the values change in a related table such as Table B or Table C or 2) time pass (as in 18 months passing) unless the field is an unstored calculation.

Do you ever need to manually change Active Status? For instance, if someone calls in and says, "I've had recent activity in table B but I still want to be unchecked as active." If you never need to change it manually then you can change it to unstored calculation.

[comment]

IIUC, ActiveStatus should be a calculation field (result is Number) =

Let ( [

today = Get (CurrentDate) ;

xDay = Date ( Month ( today ) - 18 ; Day ( today ) ; Year ( today ) )

] ;

CreateDate > xDay

or

Last ( TableB::ActivityDate ) > xDay

or

Last ( TableC::ActivityDate ) > xDay

)

This will return 1 (True) if the person's record was created within the last 18 months OR if they have a ActivityDate in TableB or TableC within within the last 18 months

The use of Last() assumes activities are entered in chronological order and that the relationship is not sorted to the contrary; otherwise use Max() instead.

[4Justme2]

A regular field in one table cannot see either 1) the values change in a related table such as Table B or Table C or 2) time pass (as in 18 months passing) unless the field is an unstored calculation.

Do you ever need to manually change Active Status? For instance, if someone calls in and says, "I've had recent activity in table B but I still want to be unchecked as active." If you never need to change it manually then you can change it to unstored calculation.

I don't see a need to ever change it manually.

IIUC, ActiveStatus should be a calculation field (result is Number) =

Let ( [

today = Get (CurrentDate) ;

xDay = Date ( Month ( today ) - 18 ; Day ( today ) ; Year ( today ) )

] ;

CreateDate > xDay

or

Last ( TableB::ActivityDate ) > xDay

or

Last ( TableC::ActivityDate ) > xDay

)

This will return 1 (True) if the person's record was created within the last 18 months OR if they have a ActivityDate in TableB or TableC within within the last 18 months

The use of Last() assumes activities are entered in chronological order and that the relationship is not sorted to the contrary; otherwise use Max() instead.

Ahhh, I can't wait to try this!!! Many thanks for help. I'll let you know how I make out with it.

[4Justme2]

IIUC, ActiveStatus should be a calculation field (result is Number) =

Let ( [

today = Get (CurrentDate) ;

xDay = Date ( Month ( today ) - 18 ; Day ( today ) ; Year ( today ) )

] ;

CreateDate > xDay

or

Last ( TableB::ActivityDate ) > xDay

or

Last ( TableC::ActivityDate ) > xDay

)

This will return 1 (True) if the person's record was created within the last 18 months OR if they have a ActivityDate in TableB or TableC within within the last 18 months

The use of Last() assumes activities are entered in chronological order and that the relationship is not sorted to the contrary; otherwise use Max() instead.

This works BEAUTIFULLY! Thank you so much. :yep: