Jump to content
  • 0

Formula Field Case Statement with Several IFs


aschild

Question

I am trying to set up a formula field (field name: Delta) with the logic I've listed below.  

So far I have been doing the formula in excel and want to move it to a Caspio field called Delta.  The excel formula is:

=IF(AND(G2="",G2<>DATEVALUE("9/9/9999"),F2="No",H2<>""),DAYS(H2,I2),
IF(AND(G2<>"",G2<>DATEVALUE("9/9/9999"),F2="No"), DAYS(G2,I2),
IF(AND(G2=DATEVALUE("9/9/9999"),F2="No"), DAYS(E2,I2),
IF(AND(G2<>"",G2<>DATEVALUE("9/9/9999"),G2<E2,F2="Yes"), DAYS(G2,I2),
IF(AND(G2>E2,F2="Yes"), DAYS(E2,I2),
IF(AND(H2=""), "NA",
"TBD"))))))

 

The fields referenced in the formula are:

Actual Test Bank Install (date/time datatype)
Estimated Test Bank Install (date/time datatype)
Contract Test Bank Install (date/time datatype)
RTS Actual (date/time datatype)
Released to Sales (formula datatype that contains either Yes, No or NA as the value)

 

The written out logic for Caspio is:

-----------------------------------------------------------------------------

If [@field:Actual Test Bank Install] is null and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Released to Sales] = ‘No’ and [@field:Estimated Test Bank Install] is not blank

Then calculate the number of calendar days between [@field:Estimated Test Bank Install] and [@field:Contract Test Bank Install]

If [@field:Actual Test Bank Install] is not blank and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Released to Sales] = ‘No’

Then calculate the number of calendar days between [@field:Actual Test Bank Install] and [@field:Contract Test Bank Install]

If [@field:Actual Test Bank Install] is “09/09/9999” and [@field:Released to Sales] = ‘No’

Then calculate the number of calendar days between [@field:RTS Actual] and [@field:Contract Test Bank Install]

If [@field:Actual Test Bank Install] is not blank and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Actual Test Bank Install] is less than (or before) [@field:RTS Actual] and [@field:Released to Sales]= ‘Yes’

Then calculate the number of calendar days between [@field:Actual Test Bank Install] and [@field:Contract Test Bank Install]

If [@field:Actual Test Bank Install] is greater than (or after) [@field:RTS Actual] and [@field:Released to Sales]= ‘Yes’

Then calculate the number of calendar days between [@field:RTS Actual]and [@field:Contract Test Bank Install]

If [@field:Estimated Test Bank Install] is blank

Then ‘NA’

Else ‘TBD’

-----------------------------------------------------------------------------

The formula for the Released to Sales field is:

CASE 
WHEN convert(nvarchar(10), [@field:RTS_Actual], 101) = '09/09/9999' 
THEN 'NA' 
WHEN convert(nvarchar(10), [@field:RTS_Actual], 101) != '09/09/9999' AND [@field:RTS_Actual] is not null 
THEN 'Yes' 
ELSE 'No' 
END

-----------------------------------------------------------------------------

Can someone help with what the Caspio case statement would be for my field?

Thanks in advance!

Anna

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Try to use such a statement as the example:

 

CASE

 WHEN [@field:date_test] < CONVERT(datetime, '09/09/9999', 101) AND  IsNull([@field:Num_test], 0) = 0 THEN Datediff(year ,[@field:date_test], CONVERT(datetime, '09/09/9999', 101))
 
END



You can adjust it for your need and add as much Case conditions as you need.

You may find the standard statements you can use in formula fields.  If you highlight these statement, you will find the description of the statement.

Also, check these articles to get familiar with the CASE and CONVERT statements:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
 

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
Answer this question...

×   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...