Automatic Query

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thank you both,

I am new to queries, so I probably should have given a little more detail on what I have so far. This is my query.

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 T0.[DocDate] >=[%0] ORDER BY T2.[SlpName], T0.[DocDate]

When I run the query, it asks for the posting date and will show all invoices by sales employee since that day (I use today, so it shows all for today)

Is this enough that setting an alert will complete this automatically at the end of each day?

Thanks again,

Hayden

[Former Member · Accepted solution]

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

[Former Member]

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