Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications" - Acumatica Generic Inquiries & Pivot Tables

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Lunar Windbloom — Mon, 24 Mar 2025 18:07:06 +0000

@timrodman No worries. Thanks for the quick response!

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Tim Rodman — Mon, 24 Mar 2025 18:05:42 +0000

@lunar I'm sure there's a way to see it in the code, but I don't know it off the top of my head.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Lunar Windbloom — Mon, 24 Mar 2025 17:19:17 +0000

@timrodman That tutorial was fantastic, and now I'm very excited at the possibilities for future projects. For this one though, how can i figure out which DACs or calculations are done on that ARPayments.curyUnappliedBal field before showing it on that spot in payments?

Seeking methods to hopefully use for future issues like this. My best guess would be pulling form the ARPostTran table which glitches like mad if you try to touch it, but that's purely a guess. For the calcs, I've tried lots of other fields/DACs to see if there's any consistency for what shows up there, But I haven't found it.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Lunar Windbloom — Mon, 24 Mar 2025 15:13:03 +0000

@timrodman Fantastic! I know what I'm learning today! Thanks Tim

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Tim Rodman — Fri, 21 Mar 2025 23:05:41 +0000

@lunar here's a tutorial:

https://www.augforums.com/acuconnect2023

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Lunar Windbloom — Fri, 21 Mar 2025 21:01:41 +0000

@timrodman I'm mired in this problem right now. I've not made an SQL view, but I do know enough SQL to get me in trouble.

How do you make an SQL view? My team is -just- starting to do non-GI customization so there's a lot of basic functionality we don't know.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Tim Rodman — Fri, 29 Sep 2023 21:38:24 +0000

@bmorgan @nscyoc I'll throw another idea out there and that is to use a SQL View to do the calculation. Then you could use the SQL View in your Generic Inquiry or Report Designer report.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Brian Morgan — Fri, 04 Aug 2023 18:15:34 +0000

@nscyoc This was plenty helpful! I appreciate you taking the time to come back and explain the solution that worked for you and your team.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Nicholas Scyoc — Thu, 03 Aug 2023 23:30:57 +0000

@bmorgan I'm not an expert by any means, but I'll do my best to explain it as I've come to understand things... The CuryUnappliedBal Field is what's known as a "calculated field" in Acumatica. What this means is that the field does not have a permanent value, the values that are shown in these fields within Acumatica are populated on the fly based on a variety of factors. So what we see when we open an invoice or a sales order is a value that's being calculated in real time. When we try to reference these fields within a generic inquiry, they pull blanks because when SQL runs a query through the GI screen - the field it's referencing does not have a hard value to pull.

We have rectified theses issues in a couple different ways, depending on the situation.

A. Create a custom field within Acumatica through the customization editor, this allows you to run the calculation before the table is referenced; making the value a "hard value" and as a result, a discoverable value by your GI. This option is tedious and requires a bit of expertise, as I don't recommend jumping into the customization editor without a good understanding of what you are doing.

B. Find a field that (equates to/shares the same values as) the one you are attempting to reference in your Generic inquiry. In MOST (but certainly not all) cases... this is your best and easiest answer. I do however regret to inform you that in the case of this specific issue; we were not able to do so, and ultimately our answer ended up being the "A" route.

I apologize that this isn't more helpful, and I certainly hope with all the time that has passed - you folks have found a way to get the information you were looking for into a generic inquiry.

RE: Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"

Brian Morgan — Mon, 01 May 2023 16:02:09 +0000

Apologies if bumping a thread like this is against the rules / bad practice.

@nscyoc Were you able to find a resolution to this issue? My accounting department is asking of me the exact same thing -- and I also encountered the same Acumatica forum link you did. The suggestion did not work -- and I've been having trouble getting the calculations onto the GI correctly.