Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Writing back to a database table

Status
Not open for further replies.

AllpayRog

Technical User
Nov 10, 2008
17
GB
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
 
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.
 
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
 
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.
 
See list of 3rd-party Crystal tools at:
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.
 
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
 

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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top