cancel
Showing results for 
Search instead for 
Did you mean: 

How to evaluate the value of a field only having a string of the field name

jsstern
Explorer
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

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

jsstern
Explorer
0 Kudos

How do I create a stored function?

Julie

DellSC
Active Contributor
0 Kudos

A stored function is like a stored procedure that returns a result.  It's something that would have to be created in the database.  The syntax for this is dependent on what type of database you're connecting to.

-Dell

Answers (1)

Answers (1)

former_member292966
Active Contributor
0 Kudos

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

jsstern
Explorer
0 Kudos

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