Apply a formula field action to only record entered after a certain date

[roattw]

We added a new formula field to a table.  Basically, a record field Complete is marked as Complete = Yes if several fields have data in them.  Basic SQL formula works fine:

CASE

WHEN [@field:FU_EVT_TICI] != '' AND [@field:FU_sICH] != '' AND [@field:FU_Angioedema] != '' AND [@field:FU_NIHSS] != '' AND [@field:FU_MRI_isch] != '' THEN 'Yes'
ELSE 'No'

END

It adds the NO to all previous records in the table before this new field was implemented.  Would prefer it to only apply the formula to records beginning on a certain date and not sure how reference that DATE>=[@form_date] and leave that field blank before the date, and Yes/No after the date.  SQL references mention SELECT butcant get the syntax right in Caspio.

[CoopperBackpack]

Hello @roattw,

As far as I understand, you have the field in this table with the Date/Time data type. For example, if the date is before June, 1 the formula should return an empty string. If the date if after June, 1, the formula should return Yes/No values. 

If I got it correctly, please test the following logic:

CASE
WHEN [@field:date] < CONVERT(date, '06/01/2024', 101)
THEN ''
WHEN [@field:FU_EVT_TICI] != '' AND [@field:FU_sICH] != '' AND [@field:FU_Angioedema] != '' AND [@field:FU_NIHSS] != '' AND [@field:FU_MRI_isch] != '' 
THEN 'Yes'
WHEN [@field:FU_EVT_TICI] = '' AND [@field:FU_sICH] = '' AND [@field:FU_Angioedema] = '' AND [@field:FU_NIHSS] = '' AND [@field:FU_MRI_isch] = '' 
THEN 'No'

END

In this example, [@field:date] is the Date/Time field that stores date to compare with. And 06/01/2024 (June, 1) is the date for comparison. 

[roattw]

Thank you!  This worked well.  What does the '101" signify in this case?

[CoopperBackpack]

Hello @roattw,

'101' is used to specify the date format. '101' stands for MM/dd/yyyy
These articles describe the syntax of the CONVERT() function 

https://www.w3schools.com/sql/func_sqlserver_convert.asp

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16