I am trying to create a G/L reconciliation to show all the balance in a particular G/L account that exist for a given "Loan No." (think "Customer No.") as of a given date (i.e., 3/31/2022). Using an NL function to show rows for each Loan No. with a "sort by sum" filter for "Balance at Date" <>0 times out because there's too much history to sift through. I figured I'd build a table to do the same - list all Loan No., link to the G/L entry table and "sum" "amounts" for each loan in that account. This I can do, no problem. However, the list is 260k lines long and I want the table to only show a row for each loan where the "sum" of "amount" "<>0". I tried using the "sort by sum" as an additional filter, but the report takes over 2 hours to run given the volume of g/l entry history. I thought I could link the G/L Entry table with the G/L Account table, however I cannot link the "Loan No." field from the Loan card to the "Loan No. Filter" field in the G/L Account table. What is the best way to pull a G/L Reconciliation for balances in a G/L account by Loan No. (again, think "Customer No.") at a given date where the total is not zero?
3 comments
-
BobRoss Are you using NAV/BC? If so, you could use a flowfield to get what you need. In the GL Account table, use the Balance at Date and Date Filter to create your Rows function. I think your report is timing out because you are trying to do a Sum on a Balance at Date which is already a Sum function within in NAV. You can then put a sort on Balance at Date.
I don't have Jet on my computer but I think it might look like this (from memory so it may not be exact):
NL("Rows", "G/L account", "No.", "Date Filter", "..04/28/22", "-Balance at Date", "<>0")If that doesn't work, I would recommend working with your NAV/BC partner or a Jet consultant.
-
Charlie Murphy BobRoss, thanks for the response. Our platform is build on NAV/Dynamics. As to the rows function, that would give me a list of G?L accounts that have a balance at a point in time. However, What I want to pull is a list loans ("customers") that have a balance in that account at that date. For example, Anna, Brian, and Cody are customers. Anna's balance in G/L Account 1234 is $10, Brian's is $0, and Cody's is $15. The total of G/L Account 1234 at 4/28/22 is $25. I would return two rows and two columns. Names = Anna and Cody. Balance columns = $10 and $15. Total of the balance column = $25. In my real-world scenario, "rows" takes forever because I might have 100,000 names, and in that case I'm hiding the rows that are zero with a "HIDE+?" function. If I use Table Builder, I cannot start with my Loan/Customer list and flow-field to the G/L Account because the G/L Account field for "Loan" is a "filter" cell, so I have to tell it what to put through the filter. It can't link. I can sum all the G/L entries up to that period-end date (because I can link the Loan table to the G/L Entry table), but each of the 100,000 loan numbers may have 30 entries in that particular G/L account, so it takes forever to run.
To your point, this may call for a consultant project, unless anyone else in the 'Jet-mosphere' has any other thoughts.
-
BobRoss It sounds like you have some customization to your database since Loan isn't a standard field in NAV's G/L Account table. I'm also having trouble envisioning your report as described but working with a consultant provides you that visual interaction to show what you are looking for.