Countunique Vendor Ledger based on G/L Entry filter

Questions and Answers asked by users to users, about how others use Jet Reports.

For additional "how to" information, please visit our online Knowledge Base ... ofessional
Forum rules
The Jet Reports Community forums are not a guaranteed support medium. Official support for Jet Reports is available only through the support desk at Staff responses to posts on the forum are not guaranteed.
User avatar
Posts: 98
Joined: Fri Feb 01, 2008 4:15 am

Countunique Vendor Ledger based on G/L Entry filter

Postby Jackies » Mon Jun 12, 2017 5:27 am

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.

User avatar
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

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

Postby HPDeskJet » Thu Jun 15, 2017 10:46 am

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?

(12.46 KiB) Downloaded 11 times
Jet 2015 Certified Trainer

Return to “How do I?”

Who is online

Users browsing this forum: Bing [Bot] and 2 guests