People

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)

  • Name

  • Field (Extra Value name)

  • EV Type

  • Value

  • Date the EV was created

Add to Menu

After you save the SQL Script and then run it for the first time, you can scroll to the end of the report and select Add to Menu. This will add the report as an option on the blue Toolbar under the code icon and will appear wherever you have a list of people. You can even run this for one person from his people record.

https://i.tpsdb.com/./2018-05-16_19-47-06.png

Sample Extra Value Details Report

Tip

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


Latest Update

11/13/2020

Modify image link with secure protocol.