on 10-06-2015 1:30 PM
I am reading records from an audit table that has a field containing the name of the field that has been changed. I want to link to the table and record that was changed to pull in the current value of the field, but I don't know a way to reference that field without creating a huge, cumbersome CASE statement containing the name of all 140 of so fields in the table?
I would create a stored function in the database that you would pass the relevant information to and get the value that way. Unfortunately, the stored function would probably have the long case statement, but it would be reusable if you need it in other data. The function would have to return any values in a common format, so you would probably have to convert everything to a string.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Julie,
I don't think there is an easy way to do what you want. Definitely not in Crystal alone.
Something you can try is to have a stored procedure that can dynamically return the record to you. You can pass the tablename and field, which you can get from your audit table. In the report, group on the field in the audit table and then have a subreport linked to the group that calls the stored procedure.
The report could pass the tablename into the FROM and the field to the SELECT in the procedure and return a generic recordset that would work for all 140 tables.
If the field may be a different type, you will need to convert them to a common type like a string then have the report convert it back if you need it formatted.
So 1 group and 1 subreport. If this works, it would minimize the maintenance to the report in case a new table was added.
Good luck,
Brian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is actually only one table name but about 140 fields that could have been changed.
I think I will try to narrow it down to the fields that they change the most often -- I found about 35 -- and will build a CASE statement for those fields.
I can't group by field name -- the top level needs to be the key field of the record that is updated.
Thanks anyway. I'll keep that solution in mind for future needs.
Julie
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.