Extra Value Details Report¶
This SQL report will list one row for each Extra Value on the person’s people record that has data associated with it. This includes both Standard and Ad Hoc Extra Values. So, if someone has more than one Extra Value, his name will be listed multiple times. The most recent EVs will display first. The main purpose of this report is to see the date/time the Extra Value was added.
The report will have the following columns:
- PID# (which is a link to the people record)
- Field (Extra Value name)
- EV Type
- Date the EV was created
If your database has a lot of Extra Values (perhaps from a data conversion), you will probably find it more helpful if you limit the number of people for whom you run the report. So, running it from Search Builder, a Tag, an Organization or just for one person might be more useful.
Create the Extra Value Details Script¶
- Step 1
- Go to Administration > Setup > Special Content and select the SQL Scripts tab.
- Step 2
- Click the green +New SQL Script File button. Enter the suggested name - ExtraValueDetails and click Submit. We recommend always using camel casing and no spaces.
- Step 3
- Copy the script below and paste it into the text box of the SQL Script content you just created, and Save SQL Script.
Now you can click Run Script and bookmark the results page for future use. However, you will probably want to add this to the blue Toolbar using the instructions above.
select e.PeopleId, p.Name2, Field, Type, (case [Type] when 'Date' then convert(varchar, DateValue) when 'Code' then StrValue when 'Int' then convert(varchar, IntValue) when 'Text' then Data when 'Bit' then iif(BitValue = 1, 'true', 'false') else NULL end) [Value], e.TransactionTime Created from dbo.PeopleExtra e join dbo.People p on p.PeopleId = e.PeopleId join dbo.TagPerson tp on tp.PeopleId = e.PeopleId and tp.Id = @qtagid order by p.Name2, e.TransactionTime desc
Modify image link with secure protocol.