Questions Journal Entry Amounts Greater Than a Value

Hi Scooter,

Yes, I think you can do this.

First of all, I'm assuming that you've joined together the PX.Objects.GL.Batch and PX.Objects.GL.GLTran Data Access Classes to create your Generic Inquiry. Is that right?

Assuming that's right, I would do something like this.

First of all, create a parameter to capture the Threshold that the user wants to look at. Something like this:

Note, in the screenshot above, the Schema Field is just there so Acumatica knows what type of field it is (a Date, a Number, Text, etc.). You can pick any field as long as it's a number type. I just happened to pick Batch.CuryDebitTotal.

Also note though, what I just said isn't entirely true. I tried using Batch.DebitTotal which should work as far as I can tell, but I got the following error:

Error: An error occurred during processing of the field Threshold : Specified cast is not valid.

I have no idea why I got this error, but switching to Batch.CuryDebitTotal stopped the error which made me happy enough not to pursue it further.

So, now that we have a parameter, we need to do something with this parameter.

This is what I did on the Conditions tab:

Pay special attention to the Operator column in the screenshot above.

Basically, I'm allowing the user to leave the Threshold parameter empty (which in this case is not really empty, but rather zero), or they can put a value. If they put a value then the line will get included if either the Debit Amount or the Credit Amount exceed the Threshold. Otherwise, the line will get filtered out.

Here it is in action.

First, this is what the results look like without a Threshold value:

Now, this is what the results look like with a Threshold value of 20,000 (note that only transactions with a Debit Amount or Credit Amount that is greater than 20,000 appear on the screen):

Will something like this work for you?

One last note: I used GLTran.DebitAmt and GLTran.CreditAmt on the Conditions tab rather than GLTran.CuryDebitAmt and GLTran.CuryCreditAmt. They are only different if you have transactions in different currencies. The fields with "Cury" in them are the amounts in the currency of the specific transaction. The fields without "Cury" in them are the amounts in the currency specified in the Base Currency ID field on the Branches screen. So, the reason why I used the GLTran.DebitAmt and GLTran.CreditAmt fields on the Conditions tab is that I wanted an "apples to apples" comparison. I didn't want to compare amounts in different currencies to the Threshold value. I wanted to make sure that all of my amounts were in the same currency.

Hi Tim,

Yes, those are the two Data Access Classes that I am using.

Your method is exactly what I'm looking for. This will come in very handy, especially during Audit season. Thank you!

Also, I just thought of another idea. I could include an additional calculated column that displays "Yes" or "No" whether or not the line meets the Threshold or not. Then I could throw the results into a Pivot Table and do some quick analysis to see what percentage of my debits and credits are above a certain threshold.

Great idea on the additional column Scooter!

If you want to take it a step further, then you could connect your Generic Inquiry to Excel 2016 using the Power BI features. Then you could have multiple Threshold scenarios displayed as columns to get an even better understanding of your debit and credits. You could even graph the values using a statistical visualization to see how they are distributed (normal bell curve, skewed, etc.).

Hmmm, that statistical stuff makes my head hurt. I'll let you know if I get brave enough to try it 🙂