Function that returns true if criteria appears more than once in a data range

[rekent]

I have the following function that is operating correctly (returning true when more than 1 "Gas" or "Utilities" entries exists.

If( (ValueCount( FilterValues( List( Assistance::Assistance Type); "Gas")) + ValueCount( FilterValues( List( Assistance::Assistance Type); "Utilities"))) > 1; 1)

I want to add another limitation, but I am not sure how to and am hoping for help.  I only want the above function to return true if the "Gas" and "Utilities" criteria that it is finding appear more than once within the past 365 days - currently it returns true if the criteria appear more than once ever.  The date of each Assistance Type is stored in Assistance::Assistance Date.  These fields, Assistance Type and Assistance Date are fields in a portal, if that is important.

 

In normal words: If more than one entry of the type gas or utilities has been made in the past 365 days, return true.

 

Is there some way to do this by introducing a limitation that uses Get ( CurrentDate ) and (Get ( Current Date) - 365)?

 

Any help would be very greatly appreciated, and I thank you in advance for whatever you are able to do to help me out here.

[Matthew F]

An easy way to do this, though not so elegant, is to create a new relationship between your current table and Assistance that adds additional criteria based on date criteria.  The dates could be specified using the Get(CurrentDate) function, like you said, added to a date global field, and then this would be an link to the Assistance::Date with > or < operators.   The reason this is not too elegant, is that you would be adding a new table occurance, potentially just to serve this one calculation.

[David Jondreau]

A relationship filtering on date is probably your best bet. You can use a new unstored calculation field in the parent table = Get ( CurrentDate ) - 365. Or better yet (for leap years):

Let( [

today = get ( currentDate ) ;

d = day ( today ) ;

m = month ( today ) ;

y = year ( today )

];

date ( m ; d ; y-1 )

)

 

 

Also your original calc can be rewritten as:

 

=ValueCount ( FilterValues( List( Assistance::Assistance Type); List ( "Utilities"; "Gas" ) ) ) > 1