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)
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]
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
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.
Question
aschild
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
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.