Best way to build filter from non-distinct values

Mike_HWG

in a report, I want to return a listing of the chart of accounts that does not contain accounts that are defined as a Purch. Account in the General Posting Setup table.


In my report, my current code looks like this:

GeneralPostingSetup.SETFILTER("Purch. Account",'<>''''');
GeneralPostingSetup.FINDFIRST;
REPEAT
  IF  PayablesAccountFilter = '' THEN
    PayablesAccountFilter := '<>' + GeneralPostingSetup."Purch. Account"
  ELSE
    PayablesAccountFilter += '&<>' + GeneralPostingSetup."Purch. Account";
UNTIL GeneralPostingSetup.NEXT = 0;

This results in the following filter:

'<>50720&<>50715&<>50715&<>50715&<>50701&<>50710&<>50710&<>50710&<>50710&<>50710&<>50710&<>50710&<>50710&<>50710'

What is the best way for me to create a cleaner filter?

I'm guessing I load up a temporary G/L Accounts table with the accounts I don't want, then loop through the temporary table to load the filter... but it sounds like a lot of looping that could probably be handled in a better way.

:-k

Savatage

I don't understand the final goal?
Why are you needing this?
Wouldn't it be easier to simply add a new field to the Table called "Purch account" type Boolean and check the accounts off.

Then if this is for reporting reason or something you can simply filter on this one field (Yes/no)

David_Singleton

Savatage wrote:

I don't understand the final goal?
Why are you needing this?
Wouldn't it be easier to simply add a new field to the Table called "Purch account" type Boolean and check the accounts off.

Then if this is for reporting reason or something you can simply filter on this one field (Yes/no)

This is basically how I would do it, except I would create the boolean as a FlowField so that there is no need to do any synchronization.

Mike_HWG

Two reasons:

1. Regardless of my report, I am trying to learn how to implement the SELECT DISTINCT issue correctly.


2. In NAV, our accountant will look at the check ledger and ask, "what was this check for?" He can navigate on the entry to bring up the Vendor Ledger Entry. For each of the VLE entries, he can view the Applied Entries. From there he needs to navigate on the applied entry to view the G/L entries. He will then copy the results to Excel where he will filter out any of the accounts that are associated to a clearing account or a generalized A/P. The end result is to show the accounts we paid against inventory, warehouse services, maintenance, etc. As you can see, though, this is time consuming when there are over 50 checks to wade through in a day and you need to see it all at once.

Based on the accounts he wants filtered out, I see that they match:
-the Payables Account fields from the Vendor Posting Groups
-the Purch. Account fields from the General Posting Setup
-We haven't discussed which accounts are filtered out for situations where we may cut a check for customer returns, since the situation hasn't occurred yet, so at the moment we've agreed to not filter.

If there's already a solution for doing this, I haven't come across it yet.

Savatage

Mike_HWG wrote:

He will then copy the results to Excel where he will filter out any of the accounts that are associated to a clearing account or a generalized A/P. The end result is to show the accounts we paid against inventory, warehouse services, maintenance, etc.

This is the part I keep trying to understand.
Does he not trust any original entry's are correct?
Why not use the G/L & the G/L Reports instead of going from check..all the way to g/l?

I guess this is your business need :-k

Mike_HWG

Savatage wrote:

Does he not trust any original entry's are correct?

I think he trusts them, but he can't figure out how to report on the data he needs. As I said, he wants to know, "when I posted this check, what accounts did I really pay off?"

Why not use the G/L & the G/L Reports instead of going from check..all the way to g/l?

How do you tie the check number to the G/L entries I want to look at without all the hoops to jump through?



We as a company are in our 2nd month live on NAV, so there's still culture changes and getting to know the system.
I do what I can, but I'm new and my background is in ADCS and warehousing, so please pardon me if I'm not clear on accounting topics all the time. :oops:

Savatage

Just asking because we are trying to narrow down exactly what you need.

For example when you say, "when I posted this check, what accounts did I really pay off?"
when you say Accounts do you mean Vendor Account or G/l Account.

When we cut a check to vendor "National Grid" (Gas Company) for example..that's the account.
We also know a journal entry was made hitting our g/L account for "utilities" to create the record to pay.

So does he need to know every amount for every g/l account for every entry in the entire system?
If nav is setup correctly then the majority of transactions should be applied correctly.
See Here: http://mibuso.com/blogs/alexchow/?s=cpa+way

Does he/she use the Detailed Trial Balance report? If so, is it missing something he/her needs?
Our accountants love this report

Mike_HWG

Savatage wrote:

Just asking because we are trying to narrow down exactly what you need.

Please do

For example when you say, "when I posted this check, what accounts did I really pay off?"
when you say Accounts do you mean Vendor Account or G/l Account.

G/L Account. Let me go for a semi-real-world example:

We cut a check 1234 for John Doe Bread Company, $100
I can see a vendor ledger entry for the payment, which I don't care much about.
I view the applied ledger entries, which gets me the invoice
I can navigate to see the G/L entries, which shows the following:
debit to Inventory Account 100
credit to Purchases Clearing -100
debit to Purchases Clearing 100
credit to A/P -100


As a result, my report would show:

Check 1234,John Doe Bread Company,Inventory Account, $100, Invoice No. ABC456

So does he need to know every amount for every g/l account for every entry in the entire system?
If nav is setup correctly then the majority of transactions should be applied correctly.
See Here: http://mibuso.com/blogs/alexchow/?s=cpa+way

I'm not sure what I'm looking at? I'll do my best to read through the site, though.

Does he/she use the Detailed Trial Balance report? If so, is it missing something he/her needs?
Our accountants love this report

Well, I can see the check, the amount, but I can't see why I would have written the check. Per each entry, I need to see the applied accounts, i.e., what accounts were satisfied by my check.

Savatage

Well if you created an PO from setup items, Received & invoiced the po
Then the g/l accounts should be the same for like items.

As for why you cut the check, I presume you entered terms for that vendor.
when you created the po, it used those terms to calculate a due date.
And you're paying it because it's due.

Perhaps we're confusing each other? :-k
I guess I'm confused because most accountants we deal with look at the big picture, not analyzing the details of every check. but I guess because it's a new system and they are trying to learn?

Mike_HWG

Hehe, I didn't mean as to why I would want to write a check. Maybe I can take a different approach and ask what you would do:



I look at the check ledger entry and I see check 1234 for $1,000.
What G/L accounts am I paying off with that check? Was it for my inventory? Health insurance? freight? equipment? Maybe more than one category?

I guess I'm confused because most accountants we deal with look at the big picture, not analyzing the details of every check. but I guess because it's a new system and they are trying to learn?

I see what you are getting at, but if I knew more about the meaning of what they were doing, I'd probably do a better job of pushback or guidance. I do try!

I guess because it's a new system and they are trying to learn?

I spend most of my day deciding when I need to do this -> [-( before having to do this :-k ](*,)

The old system was 'like feeding a black hole', so it's been a lot of culture change showing them that the data is readily available most of the time

Mike_HWG

I just re-scanned my notes and saw something that might add to the conversation:

Savatage wrote:

Why not use the G/L & the G/L Reports instead of going from check..all the way to g/l?

I ran the G/L Register, filtered by Source Code = PAYMENTJNL. All the account numbers in the first column reference the Accounts Payable account, or the bank's g/l account. When the accountant saw this, he said it was good, but he wants to know more about 'the other side of the equation'.

Mike_HWG

In any case, I've figured out the code - I used temporary tables

Kriki explains it best on his first post in this thread


figuring out accounting people is a different story