Formula and " Record cannot be saved because an error was encountered in Triggered Actions."

[Becca37]

I have a need to calculate a status value based on (a) Open and Closed date combinations and (b) an override value, if present.

My formula validates, my trigger validates, and all works well for creating status based on the data fields, but I get an error when basing it on the override field.

StatusOverride is a Text(255) data type.

Status is a formula in Admissions and a Text(255) in Admissions_AuditLog. Formula: 

CASE 

WHEN IsNull([@field:StatusOverride], 0) != 0
THEN [@field:StatusOverride]

WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0
THEN 'Pending'

WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) = 0
THEN 'Open'

WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) != 0
THEN 'Closed'

ELSE 'Error'

END

GOOD: If I add an admission record with no date, I successfully get a status of Pending as expected, and record is successfully written to audit log.

GOOD: If I add an admission record with an opening date but not closing date, I successfully get a status of Open  as expected, and record is successfully written to audit log.

GOOD: If I add an admission record with an opening date and a closing date, I successfully get a status of Closed as expected, and record is successfully written to audit log.

GOOD: If I add an admission record with a closing date but not opening date, I successfully get a status of Error as expected, and record is successfully written to audit log.

ERROR: If I add an admission record with a status override value, I get ERROR: Record cannot be saved because an error was encountered in Triggered Actions.

Can anyone tell me what I'm doing wrong? :0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TEST RECORDS:

DATA TABLES DESIGN:

TRIGGER:

[Glitch]

Hi @Becca37

 

Can you try out this formula?:

CASE

WHEN Isnull([@field:StatusOverride],0) != 0
THEN [@field:StatusOverride]

WHEN Isnull([@field:StatusOverride],0) = 0
THEN CASE

WHEN  Isnull([@field:DateOpened],0) = 0 AND
Isnull([@field:DateOpened],0) = 0
THEN 'PENDING'

WHEN  Isnull([@field:DateOpened],0) != 0 AND
Isnull([@field:DateOpened],0) = 0
THEN 'OPEN'

WHEN  Isnull([@field:DateOpened],0) != 0 AND
Isnull([@field:DateOpened],0) != 0
THEN 'CLOSED'

ELSE 'ERROR'

END

END

I'm thinking, we should first isolate the Cases for the Override.

 

This formula works provided that the Field StatusOverride is a number. 

 

Now if that field also includes text, this formula should work:

 

CASE

WHEN [@field:StatusOverride] != '' AND [@field:StatusOverride] != '0'
THEN [@field:StatusOverride]

WHEN Isnull([@field:StatusOverride],0) = 0
THEN CASE

WHEN  Isnull([@field:DateOpened],0) = 0 AND
Isnull([@field:DateClosed],0) = 0
THEN 'PENDING'

WHEN  Isnull([@field:DateOpened],0) != 0 AND
Isnull([@field:DateClosed],0) = 0
THEN 'OPEN'

WHEN  Isnull([@field:DateOpened],0) != 0 AND
Isnull([@field:DateClosed],0) != 0
THEN 'CLOSED'

ELSE 'ERROR'

END

END

 

Both formula works on my end. I used the second one here. See attached screenshot.

Let me know if this works!

 

Glitch.

[Glitch]

It seems that you are trying to involve two different types CASES.

1st case: 

WHEN IsNull([@field:StatusOverride], 0) != 0
THEN [@field:StatusOverride]

2nd case:

WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0

 

Now these are the conditions that are to be met to have the value.

 

What I think causes the error is because, if the input satisfies both type of cases, the formula field will return 2 values.

Like for example,

WHEN IsNull([@field:StatusOverride], 0) != 0 

AND

WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0

 

Which THEN Statement should the formula return as value, is it the "THEN [@field:StatusOverride] or the THEN 'Pending'"

 

To fix this, I think you should add another condition inside your CASE WHEN DateOpened and DateClosed to

WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0 AND [@field:StatusOverride] = 0

 

Hope this makes sense.

Glitch 

[Becca37]

Thanks @Glitch

It makes some sense yes! I tried adding IsNull([@field:StatusOverride], 0) = 0 AND to the other conditions and it still produces the same error regarding the trigger.

I then pruned the formula to:

CASE 

WHEN IsNull([@field:StatusOverride], 0) != 0
THEN [@field:StatusOverride]

ELSE 'Error'

END

And, the same error.

I then turned the trigger OFF and tried again with the stripped down formula and got: Record cannot be saved due to incompatible values in one or more formula fields.

Hmm, so maybe that seems to be the underlying issue. Then accidentally tested the status override field with an integer instead of a string, and it worked. But it will be a string so I updated the stripped down formula to:

CASE 

WHEN IsNull([@field:StatusOverride], 'n/a') != 'n/a'
THEN [@field:StatusOverride]

ELSE 'Error'

END

And that worked. Yay!

So I added back in the other cases, and the status override works, but now the others do not. Boo!

They are dates so the comparison to check for Null has to be with 0 as far as I've been able to find. So, then I nested another CASE statement to try to eliminate the possibility of two values being returned:
 

CASE 

    WHEN IsNull([@field:StatusOverride], 'n/a') = 'n/a'
    THEN CASE

        WHEN IsNull([@field:DateOpened], 0) = 0
            AND IsNull([@field:DateClosed], 0) = 0
        THEN 'Pending'

        WHEN IsNull([@field:DateOpened], 0) != 0
            AND IsNull([@field:DateClosed], 0) = 0
        THEN 'Open'

        WHEN IsNull([@field:DateOpened], 0) != 0
            AND IsNull([@field:DateClosed], 0) != 0
        THEN 'Closed'    

        ELSE 'Error'

    END
    
    ELSE [@field:StatusOverride]

END

But still only the status override option works. Boo!

{pulling out my hair}

SO now, this works:

CASE 

WHEN IsNull([@field:StatusOverride], 'n/a') != 'n/a'
THEN [@field:StatusOverride]

ELSE 'Error'

END

AND this works:

CASE 

        WHEN IsNull([@field:DateOpened], 0) = 0
            AND IsNull([@field:DateClosed], 0) = 0
        THEN 'Pending'

        WHEN IsNull([@field:DateOpened], 0) != 0
            AND IsNull([@field:DateClosed], 0) = 0
        THEN 'Open'

        WHEN IsNull([@field:DateOpened], 0) != 0
            AND IsNull([@field:DateClosed], 0) != 0
        THEN 'Closed'    

        ELSE 'Error'

    END

BUT I need to be able to check BOTH in the same formula because we have to be able to override the normal status value that is otherwise calculated based on open and close dates. 

[Becca37]

32 minutes ago, Glitch said:

CASE WHEN [@field:StatusOverride] != '' AND [@field:StatusOverride] != '0' THEN [@field:StatusOverride] WHEN Isnull([@field:StatusOverride],0) = 0 THEN CASE WHEN Isnull([@field:DateOpened],0) = 0 AND Isnull([@field:DateClosed],0) = 0 THEN 'PENDING' WHEN Isnull([@field:DateOpened],0) != 0 AND Isnull([@field:DateClosed],0) = 0 THEN 'OPEN' WHEN Isnull([@field:DateOpened],0) != 0 AND Isnull([@field:DateClosed],0) != 0 THEN 'CLOSED' ELSE 'ERROR' END END

@Glitch This (the second one because it is text) worked, thank you so very much!

[Glitch]

1 hour ago, Becca37 said:

@Glitch This (the second one because it is text) worked, thank you so very much!

Glad it worked!