Skip to Content
author's profile photo Former Member
Former Member

Document Series Filtering

Hi All,

Here we have a query which will display the open AR Invoices.

SELECT t0.DOCNUM as 'Invoice No', t0.DOCDATE as 'Invoice Date', 
t0.DOCDUEDATE as 'Due Date', t0.CARDCODE as 'Customer Code', 
t0.CARDNAME as 'Customer Name',t0.NUMATCARD as 'Cust Ref No',
t0.DOCTOTAL as 'Amount', T0.pAIDSUM aS 'Paid Amount', (t0.DOCTOTAL- T0.pAIDSUM) aS 'Pending Amount',datediff(dd,getdate() ,t0.DOCdueDATE)as 'Due Days' 
FROM OINV T0 INNER JOIN NNM1 T1  ON T0.Series = T1.Series 
WHERE 
T0.[CardName] >=[%0] AND  T0.[CardName] <=[%1] AND  T0.[DocDate] >=[%2] AND  T0.[DocDate] <=[%3] AND  T1.[SeriesName] >=[%4] AND  T1.[SeriesName] <=[%5] AND T0.DOCSTATUS='O'

In the above query we have included NNM1 table to filter the document series as we have four different series for different brances like for Bangalore- BAN09-10, for Delhi -DEL09-10 etc..

Now the problem is the document series is displaying all series which are not assigned for Invoice( document like 'BAN91-10' which is for Journal Entry)

We want to filter the series which are assigned for AR Invoice.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 06, 2009 at 11:58 AM

    Hi

    Just edit yr query column oinv.objecttype=13 means A/R Invoices

    or u can trace this column and filter it

    Hope it work fine

    Kevin

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 06, 2009 at 03:18 PM

    The system always shows every value from a table when it offers the u2018List of existing valuesu2019.

    So if you want the user choose from only the series used in invoices, you have two possibilities:

    1. Make the user choose from the series codes only.

    2. Create an UDT with the series names and codes of the invoices and use this instead of the NNM1.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 06, 2009 at 04:00 PM

    One way to achieve your goal would be:

    INNER JOIN NNM1 T1 ON T0.Series = T1.Series AND T1.Series NOT IN (X, Y, Z ....)

    You may find all those unwanted series numbers by SELECT * FROM NNM1

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.