By using this website, you agree to our Terms of Use (click here)
Our Billing department hit me with a GI request last night and I've been pulling my hair out over this for the past 12 hours 😥. Yesterday evening I was asked to pull the "unapplied balance" field into our Payments and Applications GI, and after a couple failed attempts + some forum surfing I found that this field "APPayment.CuryUnappliedBal" is simply not going to give me this information (because this is a calculation field).
I then stumbled across the post below, where this individual seems to be encountering the exact same issue. Naturally I was very excited to see the "best answer" providing a different route to accomplish what I'm after... but I have since attempted to use the fix provided in this thread, to no avail. Much like the "APPayment.CuryUnappliedBal" field, "APRegisterReport.DocBal" is also pulling a blank column into my GI.
"Instead of using APPayment.CuryUnappliedBal, join APRegisterReport with APPayement and using field APPayment.CuryDocBal, schema field is APRegisterReport.DocBal It will show the amount of unapplied"
I'm at a loss and I'm very much hoping that someone in the AUG community has an answer to this problem.
Thank You,
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.
@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.
@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.
@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.
@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.
@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.