cancel
Showing results for 
Search instead for 
Did you mean: 

Email Alerts

Former Member
0 Kudos

Hi Dear,

I define a query in query manager and used in alert in both internal and email alert.

the query have an contains more than 200 rows

In internal alert it shows all rows. But in email alerts it shows only upto 75 rows.

Can anybody suggest me how to show all rows in email?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try:

SELECT T0.[ItemCode], T0.[FrgnName] as 'Part No', LEFT(T0.[ItemName],40), T0.[OnHand] as 'In stock', T0.[LastPurPrc],(T0.[LastPurPrc]* T0.[OnHand]) as 'Total' FROM OITM T0 WHERE T0.[ItmsGrpCod] =110 AND T0.Onhand>0

UNION ALL

select NULL,Null,'Total', sum(T0.[OnHand]), sum(T0.[LastPurPrc]* T0.[OnHand])/ sum(T0.[OnHand]), sum(T0.[LastPurPrc]* T0.[OnHand]) from OITM T0 WHERE T0.[ItmsGrpCod] =110 AND T0.OnHand>0

Former Member
0 Kudos

Hi Gordon,

This query is helpful.. but it is not showing full records.

this query gives 110 records and But in mail alerts is shows only 82 records.

Is  restriction in mail alerts?

Former Member
0 Kudos

Yes. There is restriction in total length of the query results. Change LEFT(T0.[ItemName],40) to LEFT(T0.[ItemName],20) or something similar to allow more records.

KennedyT21
Active Contributor
0 Kudos

Hi  Ketaki Attarde

Check with this Note

874145  - Queries in alerts may not return the full query result


Symptom

When a specific query is run from the 'Query Generator' the results are  quite long. However when the same query is run as part of an alert not all of the results are displayed.

 

Cause

Application Functionality

 

Solution

An alert based on a query returning long results will not display the complete list should the result be over a certain size. This implies that a query which returns, for example only the invoice number, 200 lines in the query generator may only return 123 lines in the alert.

Workaround:

1) If possible limit the query in the 'where' condition.
For Example:
If the query in the alert returns all invoices that exist in the database. It may be pertinent to limit the display to those invoices added on that day only or to display only invoices that have the status 'Open'

E.G.
           Instead of

           select T0.docnum from oinv T0

           Write

           select T0.docnum from oinv T0 where datediff(day,getdate(),t0. updatedate) =0

To save the query in the query manager:

To save the query open the query generator (Reports -> query    generator).
           Press 'Execute' without entering any table information. 

           Click on the pencil icon to the left of the statement 'Select   *'  This opens the query for editing.

           Delete the words 'SELECT *' and copy in the query below. 

           Now press 'save' and the 'Query Manager' opens.

           Click on a title from the list displayed.  In the 'Query Name'  field  type the name the query will be saved under.

           Press Save and the query is saved in the query manager.


To create an alert:

           Open the 'Alerts Management' (Administration ->  Alerts Management).

           Change to add mode by choosing Data -> Add.

           Give the alert a name e.g. Invoice Document Numbers.

           Select the 'active' option.

           To select the saved query click on 'open saved query' and select the query from the query manager.

           Under the heading 'Frequency' choose the desired timing for the reoccurrence of the alert e.g. "Every '1' 'Day'",  "At '9:00Am'"

           Choose "Add" to save the alert.

2) In case the above is not feasable please verify the following:

a) Limit the resultset in the alert by modifying the query as follows:

DECLARE @rowsperpage INT

DECLARE @start INT

SET @start = 1

SET @rowsperpage = 50

SELECT * FROM

  (

  SELECT row_number() OVER (ORDER BY sortfield) --add a sorting field for the resultset

  AS rownum,

    -- add the requried fields here

  FROM  -- add the required table(s) here

  WHERE  -- add the required conditions here

  )

AS A

WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage)

This will result in the first 50 rows of the resultset.

b) Create duplicates of the query changing the value of the @start variable. Create as many queries as needed to be sure all rows are included (estimate the maximum number of rows expected).

c) Create an alert for each query created with the same settings as the original alert.

3) In case none of the above is feasible there is the workaround of creating an alert telling the user to run a certain saved report/query

and export the results to Excel.

Regards

Kennedy

Answers (4)

Answers (4)

former_member218051
Active Contributor
0 Kudos

Hi Ketaki,

Yes, alert is meant to notify the user of a specific activity at a set frequency. So it should not contain huge data. So data should always be limited to the most relevant data.

Thanking you

malhaar

Former Member
0 Kudos

Hi Gordon,

It is very simple query. I want to send inventory status on daily basis via email.

here is query

SELECT T0.[ItemCode], T0.[FrgnName] as 'Part No', T0.[ItemName], T0.[OnHand] as 'In stock', T0.[LastPurPrc],(T0.[LastPurPrc]* T0.[OnHand]) as 'Total' FROM OITM T0 WHERE T0.[ItmsGrpCod] =110

UNION ALL

select NULL,Null,'Total', sum(T1.[OnHand]), Sum (T1.[LastPurPrc]), sum(T1.[LastPurPrc]* T1.[OnHand]) from OITM T1 WHERE T1.[ItmsGrpCod] =110

Or you u can suggest me another way to sent a mail  about inventory status

Thanks

Former Member
0 Kudos

Hi,

If you can post your query here, we may be able to tailor it to get more rows that will still be within the limitation for email alert.

Thanks,

Gordon

former_member204969
Active Contributor
0 Kudos