Skip to main content
Solved

Generic Inquiry iff statement that is giving me an invalid string constant error

E
Jr Varsity III
Forum|alt.badge.img

I am trying to enter a formula into a generic inquiry that gives me a % of completion based on:

cost to date/estimated budgeted costs * 100

When I started I kept getting a div/0 error so I wanted to write the formula as follows  if the cost to date or the estimated budgeted cost was less than or equal to zero then column would return a value of 0 otherwise it would return the value of the calculation.  I am just learning the syntax of more complex if then statements. I have tried everything and moved from syntax errors to index out of bounds. Here is my formula that is now generating a invalid string constant error. 

=iif([PMBudget.Type] = ‘E’,iif(([PMBudget.CuryActualAmount]<=0) or ([PMBudget.Revisedamount]<=0) ,0,([PMBudget.CuryActualAmount]/[PMBudget.Revisedamount]),0)     

 

It seems like it should be a fairly simple formula but it just not working.  I’m missing something.

Best answer by lauraj46

Hi ​@eucciferri35 ,

For future reference, you should be able to click on the file clipboard and click on ‘Export as XML’.  The Excel export is less useful because it just shows the Conditions without the Tables, Relations, etc.

In any event, I was able to create a simplified version of your GI with just the PMBudget table for testing purposes.

I found a couple of other issues in your formula:

  1. The single quote marks around the E are not the standard ascii characters.  
  2. Field names (i.e. PMBudget.RevisedAmount) are case sensitive

With a valid formula, you should be able to click the ‘Validate’ button on the formula builder and it will return ‘Validation Passed.’  Below screenshot shows errors before I finished the corrections.  This is the final working formula:

 

=iif([PMBudget.Type] = 'E',iif([PMBudget.CuryActualAmount]<=0 or [PMBudget.RevisedAmount]<=0 ,0,[PMBudget.CuryActualAmount]/[PMBudget.RevisedAmount]),0)

 

Hope this helps!

Laura

View original
Did this topic help you find an answer to your question?

6 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 594 replies
  • March 28, 2025

Hi ​@eucciferri35 ,

It looks like you're missing a parenthesis:

=iif([PMBudget.Type] = ‘E’,

iif(

([PMBudget.CuryActualAmount]<=0) or ([PMBudget.Revisedamount]<=0) ,0,([PMBudget.CuryActualAmount]/[PMBudget.Revisedamount])

)

,0)     

Hope this helps!

Laura 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
E
Chris Hackett
bwhite49
3 people like this
  • E
    eucciferri35
  • Chris Hackett
    Chris Hackett
  • bwhite49
    bwhite49
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 28, 2025

​@lauraj46  I add the extra parenthesis but I am still getting the invalid string error. I copied and pasted to make sure I wasn’t typing it in wrong.

lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 594 replies
  • March 28, 2025

Hi ​@eucciferri35 ,

Please export the XML for your GI and attach to this thread so that we can take a look.

Thanks,

Laura 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 28, 2025

HI ​@lauraj46,

 

It wasn’t giving the option in the generic inquiry to export to XML and when I try and view it with that line active it just errors out.  I was able to download it to excel.

1 Attachments
lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 594 replies
  • Answer
  • March 28, 2025

Hi ​@eucciferri35 ,

For future reference, you should be able to click on the file clipboard and click on ‘Export as XML’.  The Excel export is less useful because it just shows the Conditions without the Tables, Relations, etc.

In any event, I was able to create a simplified version of your GI with just the PMBudget table for testing purposes.

I found a couple of other issues in your formula:

  1. The single quote marks around the E are not the standard ascii characters.  
  2. Field names (i.e. PMBudget.RevisedAmount) are case sensitive

With a valid formula, you should be able to click the ‘Validate’ button on the formula builder and it will return ‘Validation Passed.’  Below screenshot shows errors before I finished the corrections.  This is the final working formula:

 

=iif([PMBudget.Type] = 'E',iif([PMBudget.CuryActualAmount]<=0 or [PMBudget.RevisedAmount]<=0 ,0,[PMBudget.CuryActualAmount]/[PMBudget.RevisedAmount]),0)

 

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
E
Chris Hackett
bwhite49
3 people like this
  • E
    eucciferri35
  • Chris Hackett
    Chris Hackett
  • bwhite49
    bwhite49
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 31, 2025

​@lauraj46 Thank you so much for your help. I didn’t know that the field names were case sensitive.  

lauraj46
Chris Hackett
2 people like this
  • lauraj46
    lauraj46
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved