Skip to main content
Solved

Formula not pulling in the correct values in a generic inquiry

E
Jr Varsity III
Forum|alt.badge.img

So I was calculating the % completion on a project in a generic inquiry by dividing the actual costs to date by the Revised estimated costs on a generic inquiry.  After some help from the community I was finally able to get the formula to not error out.  The ending calculation was not correct so I took out the division piece and had it show me just the top number and just the bottom number but those are not pulling correctly.  I used the same fields that are existing on other lines the generic inquiry. 

I’m using

PMBudget.Type= ‘E’ and the PMBudget.ActualAmount for the cost to date 

PMBudget.Type =’E’ and the PMBudget.RevisedAmount for the Revised Estimated costs.

 

If I just pull those lines that already exist Actual Cost to date and Revised Budget amount and setup new lines copying the same formula.  I get different amounts.

Here is the results the column and the test column next to it have the exact same formula I copied and pasted but they come out with different results?
Original Cost to Date
test Cost to date with the exact same formula

Why would the same formula come out with different amounts?  Is there a way of reference existing lines in a generic inquiry in a formula?

 

Best answer by bwhite49

You must have an aggregate function on one of the fields to make them display differently? Thats the only thing that makes sense for the same calculation to display two different values.

Also, unfortunately you can reference an existing column in a calculation. You have to repeat the calculation into the new calculation. It’s a real pain.

I’m hopeful Acumatica will introduce GI variables in the not-too-distant future.

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

6 replies

bwhite49
Pro III
Forum|alt.badge.img+1
  • bwhite49
  • Pro III
  • 106 replies
  • Answer
  • March 31, 2025

You must have an aggregate function on one of the fields to make them display differently? Thats the only thing that makes sense for the same calculation to display two different values.

Also, unfortunately you can reference an existing column in a calculation. You have to repeat the calculation into the new calculation. It’s a real pain.

I’m hopeful Acumatica will introduce GI variables in the not-too-distant future.

E
Chris Hackett
2 people like this
  • E
    eucciferri35
  • Chris Hackett
    Chris Hackett
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 31, 2025

​@bwhite49  Thank you.  That was the issue.  I am fairly new to GIs and that column was hidden. 

I tried to put the sum function in front of the each of the field but it saying that it’s missing an operand before [PMBudget.CuryActualAmount] but wouldn’t the SUM be the operand telling it what to do. I am working on figuring syntax and 

 

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

bwhite49
Pro III
Forum|alt.badge.img+1
  • bwhite49
  • Pro III
  • 106 replies
  • March 31, 2025

Should be SUM() not (SUM). 

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

It may ask you to add budget type to the grouping. 

E
Chris Hackett
2 people like this
  • E
    eucciferri35
  • Chris Hackett
    Chris Hackett
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 31, 2025

I am going to need to rework it because I got an error message 

I don’t necessarily need the sum on the conditions where it’s checking to confirm they are greater than or equal to zero.  I only put that in there because I kept getting a divide by zero.  but I do need it on the two variables its dividing. It definitely would be easier it you could group field on other lines and use them in the calculation.  Right now I download the GI into excel and add the column but I would like to learn how to do it in the system because I’m building GIs that eventually I want to add to dashboards. Once I master the GI side. 

bwhite49
Pro III
Forum|alt.badge.img+1
  • bwhite49
  • Pro III
  • 106 replies
  • March 31, 2025

This error you are receiving is because you have an aggregate function on the line. Remove the aggregate function and you should be able to use SUM() in the formula.

E
Chris Hackett
2 people like this
  • E
    eucciferri35
  • Chris Hackett
    Chris Hackett
E
Jr Varsity III
Forum|alt.badge.img
  • eucciferri35
  • Author
  • Jr Varsity III
  • 21 replies
  • March 31, 2025

Thank you 

Chris Hackett
1 person likes this
  • 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