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

.. _AddExtraValueDetailsToMenu:

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.

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

    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 <http://techterms.com/definition/camelcase>`_ and no spaces.

Step 3
    Copy the :ref:`script below <ExtraValueDetailsCode>` 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 :ref:`using the instructions above <AddExtraValueDetailsToMenu>`.

.. _ExtraValueDetailsCode:
.. code-block:: sql

    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.