[jessie]

I'm trying to place a formula field on a Check that shows if the check is cleared.

The following works great for previously created checks:

CASE WHEN {cleared}='T' THEN 'Yes' ELSE 'No' END

However, when Transactions | Bank | Write Checks is selected, the form displays ERROR: Field 'cleared' Not Found because the cleared field is not yet available.

I tried Begin / End to no avail.

Would anyone know how to test for the existence of the field and then execute the Case?
This is a cached copy. Click here to see the original post.

[khultquist]

Check and see if there are other fields you can use to test for presence of {cleared}, such as {status} or {tranid}. For example, a nested case

CASE WHEN {tranid} is NULL THEN 'No' ELSE

CASE WHEN {cleared}='T' THEN 'Yes' ELSE 'No' END

END

[pcutler]

I would love to know this too.

A few more work-arounds I can throw out:

1. Using two fields. One field with a default value of {cleared}. Another field with a formula based on the first field.

2. Using SuiteScript to set the field.

[jessie]

khultquist Great idea but it does not solve the issue. The function parser is evaluating every field before doing the case statement so the nest does not solve the issue.

pcutler I think you are on the right track, making a script/workflow that would set this field rather than doing a formula without saving the value.

[MikeBucklaew]

How about creating a workflow triggered on create, before load. Use a set field value action to set the field to 'No'.

[khultquist]

I think pcutler 1st idea is the best, make a hidden field {customfield} with formula of {cleared}, store value is No. Then change the formula in your existing field to CASE WHEN {customfield}='T' THEN 'Yes' ELSE 'No' END

[jessie]

MikeBucklaew I will test but I am thinking Workflow may have issues as well because of the inability to trap the error. In script we can try/catch but I am not certain workflow can handle. We shall see…

[MikeBucklaew]

I tried a test in 2017.1 I think it'll work OK. Since it's a text field that isn't stored, the formula fails and the error message text gets put in the field. The before load happens after that and we're just putting different text in it.

[jessie]

@khultquist – thank you for your post about pculter. I did not understand the solution the first time I read it.

@pcutler Creating the two fields worked.

@MikeBucklaew Thank you for testing it, the workflow works as well.

The is the solution I used:

custbody_cleared_hidden

Type = Free-Form Text

Applies to Purchase and Vendor Payment (if purchase is not checked, then receive error when custbody_cleared_display is hidden from form)

Display Type = Hidden

Default Value = CASE WHEN {cleared}='T' THEN 'T' ELSE 'F' END

custbody_cleared_display

Type=Free-Form Text

Applies to Vendor Payment

Display Type = Normal

Default Value = CASE WHEN {custbody_cleared_hidden}='T' THEN 'Yes' ELSE 'No' END

Thank you for the suggestions! It is resolved. 🙂

[Author]

Posts