Page 1 of 1

Countunique Vendor Ledger based on G/L Entry filter

Posted: Mon Jun 12, 2017 5:27 am
by Jackies
Hey guys

I'm trying to count Vendor ledger entries based on a filter in the G/L Entry table (Gen. Posting Type=Purchase). I have used an intermediary filter for the entries to retrieve the entries from the G/L entry table and then filter based on it in the query for the vendor ledger entry table.

I can't figure why it doesn't filter the entries and does not count anything. I get 0 in all the results.

J5 used as filter in G/L entry

Code: Select all

NL("Filter";"G/L Entry";"Entry No.";"Posting Date";Options!$D$7;"Reversed";"False";"Gen. Posting Type";"Purchase";"DataSource=";$B5;"Company=";$E5)
NL Countunique query

Code: Select all

NL("CountUnique";"Vendor Ledger Entry";"Transaction No.";"Entry No.";$J5;"Vendor No.";$I5;;;"Reversed";"False";;;;;"DataSource=";$B5;"Company=";$E5)

EDIT: Changed the filter to "Transaction No.". I think this was the major issue not matching G/L entries with Vendor ledger entries. But still the results are not consistent. In some companies I get matching entries, in other more vendor ledger and other g/l entries.

Re: Countunique Vendor Ledger based on G/L Entry filter

Posted: Thu Jun 15, 2017 10:46 am
by HPDeskJet
Hello -

Could you clarify what you mean by "the results are not consistent" ?

I've attached a simple example of your queries. I have left out the Gen. Posting Type of "Purchase" (it's set to "*" in the example) because my sample Vendor Ledger Entry table does not have any matches to your entire criteria (i.e., there are no VLE records where the Transaction No. field matches a list of G/L Entry No. records where the Gen. Posting Type is "Purchase").

If you run my example (you'll need to update the Data Source and Company in cells B5 and E5, respectively), do you still get "inconsistent" results?