I need help with a scenario which will probably use Alerts.

[bessebo]

Today, in our business environment, we ship orders to our customers within our ERP system. I have developed a number of what we call Certificates of Validation which are forms that we send with the shipment of all of the serial numbers of the units in the box. Today our users go into Crystal Enterprise after they ship the order in our system and print out certificates by entering the order number. What I have been asked to do is to have this now happen automatically (print the certificates out automatically)rather than have a user have to go into CE and schedule the report. What has been happening is that the user may enter the wrong order and ship out the wrong cert or may choose someone else's certificate because more than one user is running these. In CE9 there is no way of limiting seeing only a particular user's instances (at least I don't think there is) but XI has this functionality I am pretty sure.
So what I was thinking is that when the order actually ships in our ERP system it's status changes. So I could write a trigger in Transact-SQL that writes that order to a table as soon as the status changes, indicating it has shipped. I could then have a certificate report out there that will run based on the existence of a file and that file will only exist if there is anything in the table that I mentioned previously. Then I will clean out the table and delete the file after it has printed.
This is just a first pass of thinking about it and I'm sure there is more to it. But I am interested in hearing from anyone who has implemented anything along these lines.

 

[IdoMillet]

There are several ways of handling this scenario. Do you have a DateTime field indicating when the status was set to "shipped"?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bessebo]

There is a date_shipped field that would indicate that. I am pretty sure that date gets changed when they want these certificates to print out but I will have to test that out. Let's assume that it does change. Please elaborate...

 

[bessebo]

IdoMillet,
Any help on this one?

Thanks,
Bessebo

 

[ShortyA]

Could you run the report to pick up the date_shipped field and only print those that are within the last day for example ?

ShortyA

 

[bessebo]

I need these reports to run immediately after the record is written to a file. So when it is shipped on our ERP system I will create a trigger that writes data to a history table with the pertinent information. Then, if I use events, I would have to have another routine out there that would look for new additions to the table and create a file in a directory which is tied to a file-based event. Then the report will be kicked off. I really have to think this through because I need to have the report run for the user that made the shipment.

 

[ShortyA]

I can see two options. Firstly have a trigger on the database side that creates a file each time an update is done. This would trigger an event within Crystal and the report would run. The down-side is that this trigger file must be deleted immediately after. If you have two updates on the database within seconds then one may be caught by the report and the other not as the trigger file couldn't be deleted/changed quick enough.

Secondly, if the report doesn't take long to run you could set it to run every 15 minutes and edit the record selection formula to only print records where the date_shipped field is within the past 15 minutes (assuming that it also shows the time shipped).

ShortyA