Create Sequence Number

[PauloMongo]

I am writing a report that extracts information out of one database and into another, each time a run the report I want to increment a number within Crystal to show a "File Sequence Number"

Can any body help ????

 

[dgillz]

There is already a "Record Number" special field in Crystal that you can put on the report. Will this work? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[IdoMillet]

I assume you are retrieving from Database #1
and Exporting (perhaps using ODBC) to Database #2.

One option to do what you want is to connect
to the other datbase (as a secondary data source
within the same report) and grabbing the
Maximum({File Sequence Number}) from it.

Add one to it and you are all set.

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[synapsevampire]

There are dependencies involved.

Since Crystal won't allow you to write to the same table name twice exporting under ODBC, you can't automatically use the output from the last export ran as a permanently affixed datasource unless you have another process in between run times that renames the exported table, or reads from it to construct a control table (either of these can become the permanent data source).

If the report runs at some standard interval, you can leverage that more easily and automatically (daily, etc.).

You might consider wrapping this report in code, such as VB. It doesn't have to do much, just maintain a control file and export the report.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

perhaps a stored procedure could be used...one whose purpose was not only to retreive a data set but also update a table indicating number of times the report has been run Jim Broadbent

 

[synapsevampire]

ngolem: I considered this too, and at that level, you should probably eliminate Crystal entirely.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Eliminate it from the point of view of creating the sequence itself...but not for the reporting of data and sequence number...I think you mean that. Jim Broadbent

 

[synapsevampire]

No, I meant that if they're reading from a database, and writing to an ODBC database, the SP can handle it all, and faster.

Buuuut, we don't even know whether the database supports SP's, ODBC was Ido's assumption.

The real answer so far is that Crystal cannot do this,some external process is required.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

well...maybe yes or maybe no....I have not done a lot with stored procedures but I do know that a limitation of one launched from Crystal is that it returns only one record set...but does that stop you from updating a sequence number before returning those records??? If you cannot then yes it would require an external action....but if it were possible to do both...well then Crystal could perform the task. Jim Broadbent

 

[chelseatech]

I have a couple of UFL's that can write to a disk file, and can extract data from that disk file.

Using both of these could save some data outside the CR, to be used next time it runs. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[synapsevampire]

ngolem: A SP can write to a dataset and still return a result set within CR, perhaps not with all databases, but it can with Oracle and SQL Server.

The SP could read the counter table, then update it, then return the dataset to Crystal, which would then construct a dataset. To write to the SAME table name as the last time, it must no longer exist (which means an external process, like within the SP or some other code).

Your theory works, but it's less efficient than letting the database do it all, and since the end result is a table, CR is probably just a layer of inefficiency and potential problems. With just an SP, you can have it handle the counter and export to the other database. Consider all of the posts you've read where the final solution was to use an SP to show the data. In this case, there's nothing to show, just populate another database, so why use CR?

Database programming is great fun and expansive, you can D/L both SQL Server and Oracle personal editions if you've ever a hankering to explore SP's more. Properly designed SP's are the fastest way to manipulate data from Crystal (and other tools), and the most flexible, there's no maybe yes or no to it. Plus, rather than a consultant billing themselves as a Crystal Reports consultant/instructor, they can add databa$e programmer...

Chelseatech: Intriguing UFL's... So the idea is to use a text file as the *counter* repository? Perhaps not the best solution to the problem at hand, but workable, and I can see that functionality being VERY useful elsewhere.

I'm interested in your UFL's, please send along info.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com