Grouping Customers Together for Review on General Ledger Entries


I’m currently looking for a way to group multiple customers together under one ‘Store Chain’ that I can view on the General Ledger Entries table. The reason for this is that I have mulitiple grocery stores selling our items that are all part of one “Acme Stores” chain and I have 38 Acme001 - Acme038. What I do to get sales data is filter the date range, then the GL Account Range and then past that into Excel, use a vlookup to match the name of every customer no (or in this case Source No.) to a more generic ‘Store Name’.

Is there anywhere I can add this on the customer card that will carry over to this part of the GL? I would like to be able to just grab the data, create a pivot table, and away I go - but if I do that now with just Source No. it is way to expansive, there are almost 1000 source No.s. I’m hoping a field already exists that can handle this, I was trying to use the ‘Search Description’ field but it does not show as an option in ‘Show Column’ when viewing the General Ledger Entries table.

I’ve been at this for hours and a bit fried so if this question doesn’t make sense, please let me know and I will provide more details but I ultimately want to be able to group almost 1000 customers/source into 85 “Store Groups” or “Store Chains” or whatever… The name of the field is irrelevant, I just need to group them.

I can continue to do this with a lookup table, but that is error prone and likely to not be updated when new customers are added and then things will begin to fall through the cracks.

Any help is greatly appreciated, thanks!


Use Dimensions functionality.
Add a Dim called e.g. CUSTGROUP, then you can use Analysis By Dimensions, the result exported to Excel will already be a PivotTable, not a "flat"Sheet. (BTW, such Dim and associated Analysis Views exist even in the DEMO Database, Cronus company)

However, the problem is that there is no easy way to add this new Dim value to already existing records. It can be achieved, but exact solution depends in which NAV version you have, and you haven’t said that in your post.

We handle chains in two ways.

if they are setup as different customers we use dimensions as stated above.

if the check comes from one main office that covers all the stores invoices in one check, we then set it up as one customer with each store a different ship-to. Then getting leader entires or whatever you need easier.