Jump to content

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


Recommended Posts

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...