GL Query problem

Susan Trippe

I am trying to download my complete general ledger for the entire fiscal year for my auditors. The query is duplicating some transactions, so the GL download does not tie to my trial balance. Most of the duplicates appear to be checks that were cut by A/P. The entries were not posted twice, but they are showing up twice on my query. Help!! This is the only decent general ledger I can get out of FENXT, and if it doesn't work correctly, I'm in a fix!

Sannyasin Siddhanathaswami

Aloha Susan,

Unfortunately, that's a limitation of that type of query. The only way to get the info you need without duplicates is to remove them yourself afterward. It relates to how the data is stored in the behind-the-scenes database.

I recommend outputting some unique identifiers, which makes it easier to de-duplicate.

I paste it into text in software called “BBedit” on a Mac and automatically remove the duplicates. I'm sure there's other ways of de-duplicating. Then proof it, of course.

Angela Tuttle

Are there are certain accounts like your bank account codes you can choose to not include them. Or perhaps the Accounts Payable liability account?

Angela Tuttle

Are there any parameters on the Trial Balance that are not being duplicated in the GL Query? Or is the Trial Balance only including accounts from a certain chart template?

Liz Hall

Susan--My organization uses Projects, so I use a project query where I filter by posting date and Account category. (Keep it SIMPLE) My output is Project ID, Transaction #, account category, account, account description, account class, post date, journal reference and transaction amount

I run this for all Account categories--Asset, Liability, Net Assets, Expense and Revenue and then paste them all together. This also keeps the file size small enough to fit in Excel and come through an easy download.

Good Luck

Susan Trippe

I only use projects for a few types of transactions, so this wouldn't work for me, but I appreciate your input!

Susan Trippe

Most of the duplicates are in the cash account and A/P account. I want to be able to run an entire general ledger that is sortable and filterable for my audit. I think I'm understanding that it's not possible. What I don't understand is why a general ledger system wouldn't offer that option to its accountants…

Susan Trippe

What kind of unique identifier might I use to make the duplicates easy to spot? They are mostly in my cash account and my A/P account.

Susan Trippe

Thanks Sannyasin! I added a column for the System ID and ran the Excel “Remove Duplicates” process and it worked!!! You're the greatest!!

Sannyasin Siddhanathaswami

That's awesome!!! I've only had to do this a few times, but the System ID is an amazing tip. Nice discovery!