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,
Hi
Please use double quotes for parameters, as in documentation
https://wiki.vertuna.com/display/CONFIFORMS/Supported+math+operators%2C+formulas+and+functions
Alex
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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'))))))))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
"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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
OK, I see
This must be an issue with the particular version - this regression has been fixed ASAP (please update ConfiForms)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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()]).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.