Skip to main content
Answer

Hours Variance on Generic Inquiry

  • September 8, 2025
  • 4 replies
  • 65 views
S
Freshman I

I would like my generic inquiry to include a variance column for our labor hours. The problem I am running into is that the inquiry doesn’t return a negative number when the “To Date” amount is less than the “Est Labor Hrs”. (Ex. 673.05-1,358.00 should return -684.95 so we can see the difference)

This is the current formula I am using for the +/- hours column: =IIf([PMBudget.Type]='E' AND [PMAccountGroup.GroupCD]='LABOR', [PMBudget.ActualQty]-[PMBudget.Qty], 0)

 

I am also using these formulas to get my estimated and to date labor hours.

Estimated Labor Hrs: =IIf([PMBudget.Type] = 'E' AND [PMAccountGroup.GroupCD] = 'LABOR', [PMBudget.Qty], 0)

To Date Labor Hours: =IIf([PMBudget.Type] = 'E' AND [PMAccountGroup.GroupCD] = 'LABOR', [PMBudget.ActualQty], 0)

 

I know the within the projects the balances tab shows a variance amount, has anyone figured out how to replicate this in their own generic inquiry?

Best answer by jhouser

​@SZink Do the fields you tried support negatives?

Another possibility: You could add a condition in formula where if Actual < Budget a “-” is added in front of the result using CONCAT function. 

4 replies

jhouser
Captain II
Forum|alt.badge.img+6
  • jhouser
  • Captain II
  • September 8, 2025

​@SZink Sometimes you can select a field in the "Schema Field" of results grid that supports the format you need. The fields you're using probably don't allow negative, but you can select a different schema field, which only impacts formatting. 

If I recall I've even linked to another table just to get the schema field I needed. 

Contract for hire: https://www.linkedin.com/in/jesse-houser-052817b6/
Chris Hackett
bwhite49
N
S
Freshman I
  • SZink
  • Author
  • Freshman I
  • September 11, 2025

​@jhouser Thank you! I tried a few different schema fields but nothing seems to impact the formatting. Any other suggestions?

jhouser
Captain II
Forum|alt.badge.img+6
  • jhouser
  • Captain II
  • Answer
  • September 11, 2025

​@SZink Do the fields you tried support negatives?

Another possibility: You could add a condition in formula where if Actual < Budget a “-” is added in front of the result using CONCAT function. 

Contract for hire: https://www.linkedin.com/in/jesse-houser-052817b6/
Chris Hackett
S
S
Freshman I
  • SZink
  • Author
  • Freshman I
  • September 17, 2025

I’ve tried several different schema fields, all of which show negative values on other generic inquiries within the system but it isn’t changing my format. I was able to get a formula to show a value when Actual < Budget and used the styles to highlight those instances. This should work for what our company needs.

 

Here is the formula I used in case it is helpful for anyone else: 

=IIf([PMBudget.Type]='E' AND [PMAccountGroup.GroupCD]='LABOR', IIf([PMBudget.ActualQty]<[PMBudget.Qty], -1*([PMBudget.ActualQty]-[PMBudget.Qty]), [PMBudget.ActualQty]-[PMBudget.Qty]), 0)

Chris Hackett
jhouser
Terms & ConditionsCookie settingsAccessibility statement
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