Defining a Business Alert Formula 3

[Itexphil]

I am trying to build a business alert that will report inventory items set up each day. I am using IV00101. The logic I require is where created date is today-1 but when I come to define the alert, obviously IV00101 does not contain today as a field. I presume I have to join a date field from a system table - but which table? Can anybody help?

Thanks in anticipation

Phil

 

[MentatDragon]

You can use T-SQL commands. So you could use:

IV00101_T1.CREATDDT = DATEADD(DAY, -1, GETDATE())

For the Business Alert Condition Formula. To get any items created yesterday, or substitute:

IV00101_T1.MODIFDT

to get any items changed yesterday.

 

[Itexphil]

Thanks for this.

Does this mean I need to build the alert in native SQL or can I build a simple plain vanilla alert using the business alert front end and then edit the resulting SQL script. If the latter - where do I find the SQL script?

Thanks in anticipation

Phil

 

[MentatDragon]

No,

Look at some of the sample business alerts that use date conditions, and you should see similar code. In the Business Alert part of the wizard, you just add the code.

It then becomes the WHERE clause of the query.

Let me know if you have trouble and I will give you a more detailed answer.

 

[Itexphil]

Ok Thanks for this. I went and looked at some of the sample alerts and see how this command is used however, I I still have a problem.

If try and run the alerts where ABCCODE = 1, I get a repoirt e-mailed to me as expected.

If I try and run the report using the where as advised above I get no report.

I the browsed the data and chose a particular date,

CREATDDT = 06/02/2004 again I get no report.

So I have a report that I know will work - but not using the conditions I require! Is the date held in a particular way?

Thanks in anticipation

Phil

 

[jazgeek]

here's the condition you're looking to report on

IV00101.CREATDDT=CONVERT(SMALLDATETIME, CONVERT(CHAR(10), GETDATE(),101))

you're best creating the job and using sql to fire the alert instead of business alerts....

alternatively, luvsql has pointed me towards a report scheduling program from recrystallize that works amazingly well. Reports Scheduler Pro fires off crystal reports in whatever format you want at desired intervals.... pain is it must be logged into a machine constantly to work....


interesting product though.

http://www.recrystallize.com

-----------
and they wonder why they call it Great Pains!

jaz

 

[Itexphil]

Brilliant. This works a treat. Obviously I need to improve my SQL skills

Thanks