Using Lava to Convert User ID

Question

0

Using Lava to Convert User ID

Jeremy Turgeon posted 9 Years Ago

When running a SQL query on a Workflow, I'm trying to pull up a person's name. While the query does return the person, it's formatted as their ID and not name (e.g. 7ac48ab4-5544-4f0c-8f4e-33ca01e0bbc0 instead of Bob Jones). Is there a way to report their actual name, perhaps using Lava?

0
- Jeremy Turgeon replied 9 Years Ago
Edit Answer

<img alt="SQL Query.PNG" src="/Content/RockExternal/Users/BelAirIT/SQL Query.PNG" /> <img alt="Comm Req.PNG" src="/Content/RockExternal/Users/BelAirIT/Comm Req.PNG" />
<img alt="SQL Query.PNG" src="/Content/RockExternal/Users/BelAirIT/SQL Query.PNG" /> <img alt="Comm Req.PNG" src="/Content/RockExternal/Users/BelAirIT/Comm Req.PNG" />
<img alt="SQL Query.PNG" src="/Content/RockExternal/Users/BelAirIT/SQL Query.PNG" /> <img alt="Comm Req.PNG" src="/Content/RockExternal/Users/BelAirIT/Comm Req.PNG" />
Save Cancel

David Turner · Accepted Answer · 2015-03-03T06:38-07:00

0

David Turner replied 9 Years Ago

If you are trying to get the person's name for the 'RequesterName' attribute and it is a Person field type attribute, the value is the person's PersonAlias guid, so you would have to join to the person table to get the name. For example...

   ( SELECT TOP 1 p.[NickName] + ' ' + p.[LastName]
   FROM [AttributeValue] av
       INNER JOIN [Attribute] a on a.[Id] = av.[AttributeId] and a.[Key] = 'RequesterName'
       INNER JOIN [PersonAlias] pa on pa.[Guid] = CAST( av.[Value] AS uniqueidentifier )
       INNER JOIN [Person] p on p.[id] = pa.[PersonId]
   WHERE av.[EntityId] = w.[Id]

Edit Answer

<p>If you are trying to get the person's name for the 'RequesterName' attribute and it is a Person field type attribute, the value is the person's PersonAlias guid, so you would have to join to the person table to get the name. &nbsp;For example...</p>

<p>&nbsp;&nbsp; &nbsp;( SELECT TOP 1 p.[NickName] + ' ' + p.[LastName]&nbsp;<br />
&nbsp;&nbsp; &nbsp;FROM [AttributeValue] av<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [Attribute] a on a.[Id] = av.[AttributeId] and a.[Key] = 'RequesterName'<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [PersonAlias] pa on pa.[Guid] = CAST( av.[Value] AS uniqueidentifier )<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [Person] p on p.[id] = pa.[PersonId]<br />
&nbsp;&nbsp; &nbsp;WHERE av.[EntityId] = w.[Id]</p>

<p>If you are trying to get the person's name for the 'RequesterName' attribute and it is a Person field type attribute, the value is the person's PersonAlias guid, so you would have to join to the person table to get the name. &nbsp;For example...</p>

<p>&nbsp;&nbsp; &nbsp;( SELECT TOP 1 p.[NickName] + ' ' + p.[LastName]&nbsp;<br />
&nbsp;&nbsp; &nbsp;FROM [AttributeValue] av<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [Attribute] a on a.[Id] = av.[AttributeId] and a.[Key] = 'RequesterName'<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [PersonAlias] pa on pa.[Guid] = CAST( av.[Value] AS uniqueidentifier )<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [Person] p on p.[id] = pa.[PersonId]<br />
&nbsp;&nbsp; &nbsp;WHERE av.[EntityId] = w.[Id]</p>

Save Cancel

Question

0

Using Lava to Convert User ID

0

0