Writing back to a database table

[AllpayRog]

Hi All,
Is it possible to get CR to write a flag back to a table after (or during) a report is printed. I need to set up a Crystal Report which prints an invoice, but changes a 'flag' in the Invoice table to 'Printed?' = 'Y'.

I would be very grateful for any help....this is very important for me.

Thanks
Regards
Roger

 

[jeffwest2]

Crystal only reports on the data that you give it, you cannot get CR to write anything back as it is a one way link only.

You can conditionally change data on the report, but this will be based on the data at runtime.

 

[lbass]

Well, although jeff is correct that CR is a reporting software not intended for making changes to the database, I think it is possible to write to the database using a command, but I don't know how to go about it. You might try doing an advanced search on the CR forums, using "update database" or "write to database" or something like that.

-LB

 

[kskid]

I have used Crystal reports to create the SQL update transactions. I then export it to a text file and process them in the database.

 

[jeffwest2]

Here is an article that i have come across before discussing doing it with a third party piece of software, but it is not recommended.

http://www.crystalkeen.com/articles/crystalreports/updatedatabasewithreport.htm

 

[IdoMillet]

See list of 3rd-party Crystal tools at:

http://www.kenhamady.com/bookmarks.html

My CUT Light User Function Library (UFL) allows you to trigger any SQL statement via ODBC from inside a Crystal formula. Since the formula can construct the SQL statement on the fly, it allows you to set a flag for each item included in your report.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kenhamady]

I just happen to be playing with this today for another project and I am not convinced it can be done without external help. The problem I ran into, at least with my little test in MS Access, is that a command always has to return data to the report. So to do an update you would need the command to do BOTH the update and some type of query. I could not write SQL that would do both and have CR pass it to the database. I know this would work in a stored procedure where you can have multiple steps but I couldn't make it happen in a command.

I found an article that showed a command with a semicolon separating the update from the select, but that didn't work in MS Access (native or ODBC). I don't know if it works in SQL Server.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[mocgp]

Ken: I managed to get a table (Oracle) to update from one report by using subreports containing the chunks of SQL that would not work with a ; separator. In other words, the main report conatined the SQL command DELETE FROM... , the first subreport contained the INSERT command and the selection criteria, and the second subreport was the SQL command COMMIT. The bad news is that I could not get the process to work as a scheduled report in CE, it would only work from within Crystal Pro. I was trying to find information on publishing the entire SQL as a "Program" but have not been able to get past the generic info in the Crystal XI reference manual (Peck).

Roger - If the bad news above could be resolved, it may be possible for you to adapt this to setting a flag in the table.

If anyone can make this work in CE, please let me know how you did it.

Thanks

 

[kenhamady]

Could you post an example of a Command that worked in Oracle? I would like to see your syntax. I sure didn't get far in MS Access but it might be something that is only supported in the larger DBs.

My goal was to INSERT a record into a table as I read the report, so that I could build an index at the end including the page numbers. I was able to do it using a formula with a custom function DLL (Cut Light). This DLL allows me to build a formula and execute the SQL INSERTS as the report is processed.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/