asked on
Crystal Reports Random Selection and Sql Commands
I have a requirement to generate a list of 5 records from one database table randomly. That part I have working. I created a sql statement as follows:
SELECT TOP 5 LICENSE.LICENSENO, LICENSE.LICENSECAT, LICENSE.LICENSEKEY"
FROM LICENSE WHERE LICENSE.LICENSEKEY <>1 AND LICENSE.ISSDTTM IS NOT NULL AND LICENSE.LICSTATUS = 'Active' ORDER BY NEWID()
The above statement generates a list of 5 records randomly selected. If i run the same statement again it generates a new list of five items.......perfect.
The problem i have is with the second part of the requirements which state that for every record that is returned I need to update the Database based on the licensekey. The purpose of this is to avoid records that have been selected in the past to not be selected again.
I was thinking i can write a sql command and put it in a subreport. then place that subreport in the group header. Everytime the main report is run i want it to update the 5 records it returned.
SELECT TOP 5 LICENSE.LICENSENO, LICENSE.LICENSECAT, LICENSE.LICENSEKEY"
FROM LICENSE WHERE LICENSE.LICENSEKEY <>1 AND LICENSE.ISSDTTM IS NOT NULL AND LICENSE.LICSTATUS = 'Active' ORDER BY NEWID()
The above statement generates a list of 5 records randomly selected. If i run the same statement again it generates a new list of five items.......perfect.
The problem i have is with the second part of the requirements which state that for every record that is returned I need to update the Database based on the licensekey. The purpose of this is to avoid records that have been selected in the past to not be selected again.
I was thinking i can write a sql command and put it in a subreport. then place that subreport in the group header. Everytime the main report is run i want it to update the 5 records it returned.
Crystal Reports
Last Comment
Ido Millet
ASKER
mlmcc,
Thanks for the response. Unfortunately i stll need to randomly select the 5 records.....thus the Select Top 5......
For each record that comes back I need to update that record also.
The solution would require me to update the records manually.
Thanks for the response. Unfortunately i stll need to randomly select the 5 records.....thus the Select Top 5......
For each record that comes back I need to update that record also.
The solution would require me to update the records manually.
Crystal cannot do it in the report so you have to use anpother method.
1. A stored procedure that updates the record
2. An application that runs the report but also does the selection so it can update the records.
3. Manually after you review the report.
mlmcc
1. A stored procedure that updates the record
2. An application that runs the report but also does the selection so it can update the records.
3. Manually after you review the report.
mlmcc
There is at least one 3rd-party UFL (see list at http://www.kenhamady.com/bookmarks.html) that allows you to use a Crystal formula to update any database via ODBC.
Good point Ido. I hadn't thought of using an UFL to do it.
andretix :In case you are wondering, check Visual Cut.
mlmcc
andretix :In case you are wondering, check Visual Cut.
mlmcc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You could add a new column to the database called SELECTED (boolean) Default it to FALSE and update to TRUE when you select it.
Change the selection to
SELECT TOP 5 LICENSE.LICENSENO, LICENSE.LICENSECAT, LICENSE.LICENSEKEY"
FROM LICENSE WHERE LICENSE.LICENSEKEY <>1 AND LICENSE.ISSDTTM IS NOT NULL AND LICENSE.LICSTATUS = 'Active' ORDER BY NEWID()
AND Not LICENSE.Selected
mlmcc