• 0

# Formula Field Case Statement with Several IFs

## 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?

Anna

## 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 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

##### Share on other sites

• 0

Hi Andrii, thanks for your response.  What is an example of the [@field:Num_test] field in the  IsNull([@field:Num_test],0)=0  situation?

## 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. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.