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

Automatic Query

Hi Experts,

I created a query that links OINV, INV1, and OSLP tables to show invoices by sales employee. Is there a way to make this type of query automatic so that at the end of each business day, a new query will be created showing that days' sales by employee? If yes, how would that work?

Thank you,

Hayden

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 30, 2011 at 05:42 AM

    Hi Hayden.......

    This is possible when you fire an alert at the end of the day.........

    Please try this.....

    SELECT * FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T0.DocDate = GetDate()

    Set the above query in Alert Management. For this go to Administration --> Alert Management

    Attach this query and assign the proper text and then set the timing when you want this alert exactly during the day......

    This will make it automatic.......

    Regards,

    Rahul

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Hayden,

      If you want to have alert query automatically, you will not be able to make date range selectable.

      You may try:

      SELECT T0.CardCode as 'BP Code', T0.DocNum as 'Doc No.', T0.DocDate, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price as 'Price', T1.LineTotal, T2.SlpName, T2.Commission

      FROM dbo.OINV T0

      INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry

      INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

      WHERE DateDiff(YY,T0.DocDate,Getdate())=0

      ORDER BY T2.SlpName, T0.DocDate

      This will give you this years info. Or you can change it to any given date range.

      Thanks,

      Gordon

  • author's profile photo Former Member
    Former Member
    Posted on Jun 30, 2011 at 03:34 AM

    Hi Hayden,

    Try ths query,

    SELECT * FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T0.[DocDate] = convert(char(10),getdate(),101)

    this condition is the most important >> WHERE T0.[DocDate] = convert(char(10),getdate(),101)

    after creating this query, create an alert that will retrieve all Posted transaction by a specific sales employee.

    Hope this will help you.

    Thanks,

    Darius

    Edited by: Darius Robert Gragasin on Jun 30, 2011 5:34 AM

    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.