Question

Photo of Bronson Witting

1

Getting Campus via SQL (in a workflow)

2

I'm trying to set a Campus attribute in a workflow to the campus that is set on a Person's record (to assign the workflow to the approprate group based on campus).  I have accomplished this successfully with a Run SQL workflow action.  Here's My SQL:

 

DECLARE @PersonAliasGuid uniqueidentifier = '{{ Workflow.Person_unformatted }}'

SELECT
    dbo.Campus.Name
FROM
    dbo.GroupMember
JOIN dbo.[Group] ON (
    dbo.GroupMember.GroupId = dbo.[Group].Id
)
JOIN [Person] ON (
    dbo.GroupMember.PersonId = dbo.Person.Id
)
JOIN dbo.[Campus] ON (
    dbo.[Group].CampusId = dbo.Campus.Id
)
JOIN dbo.PersonAlias ON (
    dbo.Person.Guid = dbo.PersonAlias.AliasPersonGuid
)
WHERE
    dbo.[Group].GroupTypeId = 10
AND dbo.PersonAlias.Guid = @PersonAliasGuid;

 

Here is the example in the Working with Workflows documentation: 

DECLARE @PersonAliasGuid uniqueidentifier = '{{ Workflow.Requester_unformatted }}'
SELECT [Gender] 
FROM [Person]
WHERE [Guid] = @PersonAliasGuid

 

My example is working, but I wanted to make sure I'm doing this correctly.  The example is trying to select the Alias GUID from the Person table, but that doesn't exist - I had to join the PersonAlias table to get this to work.  Also, I used the GroupTypeId of 10 for the "Family" group type.  Is this type always ID 10, or should I be deriving a person's campus another way?  If anyone can help me wrap my head around this more, I'd be greatful.  Thanks! 

 

  • Photo of Rock RMS

    1

    Bronson, awesome work! Not only have you figured out SQL in workflows but you found an error in the documentation too!  We updated the docs to reflect the link from [Person] to [PersonAlias]. Couple of other thoughts:

    • Using the [GroupTypeId] = 10 is OK, but it would be better to join to the [GroupType] table and then filter on [Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E'. That is the safest way. You can garantee that in your database it will always be 10, but if you share the workflow with someone it could be different. I wrote this additional link in the SQL below.
    • Since it is possible for a person to be in two families your  SELECT could return two rows. I also added a TOP 1 to the updated SQL below. This is unlikely to occur, but it's a good practice.
    • Looks like your workflow was using a text field to store the campus in. That works well, but note you could also use a 'Campus' attribute type. If you do your SQL should return the [Guid] of the campus to set it's value. This is totally optional, but I thought I'd mention it. The SQL below returns the [Guid].

    Looks like you're on the right track. 

    SELECT
    TOP 1 c.[Guid]
FROM
    [GroupMember] gm
	INNER JOIN [Group] g ON gm.[GroupId] = g.[Id]
	INNER JOIN [Person] p ON gm.[PersonId] = p.[Id]
	INNER JOIN dbo.[Campus] c ON g.[CampusId] = c.[Id]
	INNER JOIN dbo.PersonAlias pa ON p.[Guid] = pa.[AliasPersonGuid]
	INNER JOIN dbo.GroupType gt ON g.[GroupTypeId] = gt.[Id]
WHERE
    gt.[Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E'
	AND pa.[Guid] = @PersonAliasGuid;
  • Photo of Bronson Witting

    0

    Great, thanks so much for the feedback!!