Issue with Formula Displaying Instead of Value in Status Field

Peiman Parsa November 6, 2024

 

 

I hope this message finds you well. I am reaching out regarding an issue we’ve encountered with the status field in ‘ConfiForms Field’ Macro .

When trying to display the status value, we are seeing the formula itself rather than the expected value (e.g., “Ready to order”). Specifically, we are receiving the following error:

Error:
Expression:
[IF(4=1,'Waiting for non-EU approval', IF(4=2,CONCAT('Waiting for approval (', IF(NOT(TRUE) && NOT(TRUE),'all approvers', IF(NOT(TRUE),'project approver', IF(NOT(TRUE),'master approver',''))), IF(NOT(ZEROIFEMPTY('1')), IF(NOT(TRUE) || NOT(TRUE),' and necessity/fund availability','necessity/fund availability'),''),')'), IF(4=3,'Waiting for director's approval', IF(4=4,'Ready to order', IF(4=5,'Waiting for partial invoice payment', IF(4=6,'Waiting for delivery', IF(4=7,'Waiting for payment', IF(4=8,'Needs inventory','Completed'))))))))] could not be calculated, begin 530, end -1, length 538

This issue results in the formula being displayed in the status field rather than the appropriate value. Could you please assist us in troubleshooting this issue? We would appreciate any guidance on how to resolve it.

 

Best regards,

 

1 answer

0 votes
Alex Medved _ConfiForms_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 6, 2024

Hi

Please use double quotes for parameters, as in documentation

https://wiki.vertuna.com/display/CONFIFORMS/Supported+math+operators%2C+formulas+and+functions

Alex

Peiman Parsa November 6, 2024

This is our formula expression in ‘ConfiForms Field Definition’ Macro , and it worked until two days ago. We didn't change anything.

expression:

IF([entry.OrderingNonEu] && NOT([entry.NonEuIsOkay.upperCase()]),1,IF( NOT([entry.Approved.upperCase()] && [entry.Approved2.upperCase()] && ZEROIFEMPTY('[entry.Necessary]')),2,IF( NOT([entry.NetValue]<10000 || [entry.ApprovedByDirector.upperCase()]),3,IF( NOT([entry.Committed.upperCase()]),4,IF( NOT([entry.PartialInvoicesPaid.upperCase()]) && NOT([entry.DeliveryCompleted.upperCase()] && [entry.DocumentsComplete.upperCase()]),5,IF( NOT([entry.DeliveryCompleted.upperCase()] && [entry.DocumentsComplete.upperCase()]),6,IF( NOT([entry.Completed.upperCase()] && [entry.PaymentDone.upperCase()]),7,IF( NOT(NOT([entry.InventoryNecessary.upperCase()]) || [entry.InventoryStickerDone.upperCase()] && [entry.InventoryDone.upperCase()]),8,9))))))))

Do we need double quotes?

Alex Medved _ConfiForms_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 6, 2024

Of course you do, as in the documentation

Also, what does something like this do in your oinion?

[entry.DeliveryCompleted.upperCase()] && [entry.DocumentsComplete.upperCase()]

  Especially when the values are EMPTY, form example and you get a

NOT(&&)

as an expression!?

Peiman Parsa November 6, 2024

I mistakenly sent the status field. This is the status message that caused the problem:

IF([entry.Status]=1,'Waiting for non-EU approval', IF([entry.Status]=2,CONCAT('Waiting for approval (', IF(NOT([entry.Approved.upperCase()]) && NOT([entry.Approved2.upperCase()]),'all approvers', IF(NOT([entry.Approved.upperCase()]),'project approver', IF(NOT([entry.Approved2.upperCase()]),'master approver',''))), IF(NOT(ZEROIFEMPTY('[entry.Necessary]')), IF(NOT([entry.Approved.upperCase()]) || NOT([entry.Approved2.upperCase()]),' and necessity/fund availability','necessity/fund availability'),''),')'), IF([entry.Status]=3,'Waiting for director's approval', IF([entry.Status]=4,'Ready to order', IF([entry.Status]=5,'Waiting for partial invoice payment', IF([entry.Status]=6,'Waiting for delivery', IF([entry.Status]=7,'Waiting for payment', IF([entry.Status]=8,'Needs inventory','Completed'))))))))

Jonathan Vorndamme November 7, 2024

"Of course you do, as in the documentation"

 

I'm not sure, why I did it differently before, but double quotes don't seem to make a difference. Could it be that in the past, if one used double quotes, the field entries used in the strinbg would not be replaced, but when using single quotes they would?

 

"Also, what does something like this do in your oinion?"

[entry.DeliveryCompleted.upperCase()] && [entry.DocumentsComplete.upperCase()]

This actually seems to be the problem: for the last two years or so, empty checkboxes would evaluate to 'false' in that case, so it worked perfectly fine. This behavior seems to have changed now that our version has been updated to 3.13.9. I guess from your comment that we exploited undesired behavior for quite some time?

 

Alex Medved _ConfiForms_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 7, 2024

OK, I see

This must be an issue with the particular version - this regression has been fixed ASAP (please update ConfiForms)

Jonathan Vorndamme November 7, 2024

Thanks for the quick reply.

So empty checkboxes should evaluate to 'false'?

 

In case someone stumbles across this and like us does not have the option to immidiately update: we were able to fix the issue by using ZEROIFEMPTY: [entry.checkbox.upperCase()] -> ZEROIFEMPTY([entry.checkbox.upperCase()]).

Alex Medved _ConfiForms_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 7, 2024

Yes, they evaluate to true or false

The issue was with a function applied after "upperCase" - it was not recognized as a valid function (mistakenly checked as "field", not function)

Jonathan Vorndamme November 7, 2024

Thanks for the clarification :)

Jonathan Vorndamme November 12, 2024

ZEROIFEMPTY only camouflaged the issue. What worked better was to remove uppercase. This lead to no reported calculation errors and seems to be compatible with the NOT and && operators, but interestingly not with the || operator, even as of v3.14.1 which we have now.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events