Email Crystal Report To Recipient In Database

Posted on 2009-02-17
Last Modified: 2013-12-24
I am currently working on a solution that will allow all outstanding purchase orders on our ERP system to be automatically emailed to their associated supplier. My plan is to utilise Crystal Reports and InfoView for this.

To make this as easy to use - and dynamic as possible - I'd like to use the email addresses stored in our ERP system. However, I'm unaware of any way of setting the recipient's email address to one that is stored in a database. So, question 1 - does anyone know of a way of doing this? Would I need to look at a VB solution, perhaps?

Secondly, when these orders have been successfully emailed, I need to update a field in our database so, when the report is run again, it doesn't resend the same orders. Is it possible to run an update query on a database upon successful (note the importance of "successful") run?

I'm a little bit stuck as to the best way forward. I could always create one report per supplier, but I'd prefer the system to be more dynamic.

Any help would be greatly appreciated!
Question by:MrDerp
    LVL 4

    Expert Comment

    I've done something similar to this, and I chose VB to generate the Crystal report, primarily because of the database updating requirement.  There was just no way to do it in Crystal alone.

    You need to read up on SAP's licensing in the area of automated report distribution in Crystal.  I know that Business Objects had a restriction on how many email recipients could receive an automatically-generated Crystal report (I think it was around 50...) before you needed their 'Distribution License - a 6-figure item !
    LVL 10

    Accepted Solution

    I use a 3rd party product called VisCut for this.  Ultra cheap and does the job quite well.

    Also, the SAP restriction described in a previous post doesn't apply.  There is a blurb in the viscut manual which explains what versions of crystal were affected by the distribution restrictions.

    LVL 22

    Assisted Solution

    I'm the developer of Visual CUT.  To address the 2nd part of your question:
    "Secondly, when these orders have been successfullyemailed, I need to update a field in our database so, when the reportis run again, it doesn't resend the same orders."
    This is possible using two different approaches:

    1. assign dynamic file names to the exported PO's and Include a Skip_Recent argument in the command line.

    - or -

    2. turn on the option to log processing to a database using ODBC.  This would give you one record for each bursted PO with an indication of success or failure.  You can use that table in your report (using an outer join) to ensure the PO doesn't have a Visual CUT log record with successful status.

    - Ido

    Author Comment

    Many thanks for your responses to this. I've got to say, Visual CUT is a fantastic piece of software and it looks like it is the perfect solution to my problem.

    With regards to the second part of my question, the outer join on the table I want to update will work (and I guess I could run a little script to update the printed status on the original table with whatever has successfully run and been stored in the log processing table).

    My only outstanding issue now is that sometimes a user may change an order and want to resubmit it. As the database would indicate that it has already been sent, Visual CUT would not retransmit it. Any recommendations? I'm thinking of just giving users a little interface that will allow users to mark an order for resubmission - this would delete the record from the log and would appear like it had never been sent.
    LVL 22

    Expert Comment

    There are several options to handle this.  The cleanest one would be an Update trigger in the DBMS. There are also ways to accomplish this with Visual CUT.  However, this is no longer a Crystal question, so consider closing this thread.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now