Hi,
I’m working on a production database (we need real time data in our reports), and I’ve got a problem for a report:
In the database, I’ve got 2 tables used to record the activity on the database (insert, update, delete) with the following structure:
- ActivityHeader (Id, ReferenceTable, ReferenceId, …)
- ActivityDetail (Id, ActivityHeaderId, Attribute, OldValue, NewValue, …)
ReferenceTable is the name of the table were the tracking is made and ReferenceId the Id of the record tracked.
Attribute is basically the name of the field of the table tracked, and old and new values are recorded (in fact, we’ve got several fields, one for each possible type of data in the database).
Here’s my problem :
When 1 record is added / deleted / updated in my ReferenceTable, I’ve got 1 record addedd in ActivityHeader, but several (5 in my case) records added in the ActivityHeader table.
1 of the field tracked concern the identifier of a user. (name & first name are stored in another table).
My final users want to have a prompt during the refresh of the report on the user tracked in the table (i.e. only have the information for the tracking for a given user).
My problem is to generate the LOV!!!
If I do nothing special, I get a full list of all properties (including the one that have nothing to do with users).
If I create a specific object at the universe level, and put a condition on that object for retrieving only the users(I put a condition on the attribute value in my LOV), that works, but I only have a list of Id (and users want to see Id - Name - First Name).
In that case, should I link my ActivityDetail table to an alias of the User table in order to have that LOV???
It looks like it’s a lot of things at the designer level just for the display of the LOV.
~ Yann ~ 
(BOB member since 2007-09-11)