It can be difficult sometimes to tell how new person records get created in Rock. If someone makes a contribution, registers for an event, texts a keyword, fills out a workflow form, submits a prayer request etc. All of these are probably creating new person records with the same connection status (which is ideal). It would be helpful, though, if there was some better indication of how/why the person record was created.  

Because all the places in Rock that a person record can be created allow you to specify the person’s connection status, we took advantage of this functionality and created transient connection statuses that could be used to track this information.

In the example above, all of the statuses that have a Target Status value are “transient” because person records created with these statuses will only have that status for a short period of time. Long enough for a job to run and copy that transient connection status to a more permanent “How Created” person attribute. 

Once the job copies the transient connection status to the person's attribute, it then updates the connection status to the configured Target Value for the connection status.

This allows us to capture how the record was created, but then only use the shorter list of actual connection statuses.

To enable this functionality, you just need to do the following.

1.) Add a “How Created” person attribute.

Under Admin Tools > General Settings > Person Attributes add a new person attribute named “How Created” with a key of “HowCreated”. The field type should be a “Defined Value” and the Defined Type qualifier should be set to “Connection Status”. Make sure to select a category for the attribute so that it will appear in the person's Extended Attributes section of their profile. We used the "Visit Information" category.

2.) Add a "Target Status" attribute to the Connection Status defined type.

Under Admin Tools > General Settings > Defined Types search for the defined type named “Connection Status”. Add an attribute to the defined type naming it “Target Status” and make sure it has a key of “TargetStatus”. It should also use a "Defined Value" field type with a Defined Type qualifier of "Connection Status".

Create any new transient statuses and then set their Set the target status:

3.) Configure New Job

Under Admin Tools > System Settings > Jobs Administration add a new job named “Update How Created Values”. Set this job to run every hour and the Job Type as “Run SQL”.

Here is the SQL to use:

DECLARE @PersonEntityTypeId int = ( SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' )
DECLARE @ConnectionStatusDefinedTypeId int = ( SELECT TOP 1 [Id] FROM [DefinedType] WHERE [Guid] = '2E6540EA-63F0-40FE-BE50-F2A84735E600' )
DECLARE @DefinedValueEntityTypeId int = ( SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.DefinedValue' )

DECLARE @PersonHowCreatedAttrId int = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @PersonEntityTypeId AND [Key] = 'HowCreated' )
DECLARE @ConnStatusTargetAttrId int = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @DefinedValueEntityTypeId AND [EntityTypeQualifierColumn] = 'DefinedTypeId' AND [EntityTypeQualifierValue] = @ConnectionStatusDefinedTypeId AND [Key] = 'TargetStatus' )

;MERGE INTO [AttributeValue] AS NV
USING (
		SELECT
			@PersonHowCreatedAttrId AS [AttributeId],
			P.[ID] AS [EntityId],
			CAST(SDV.[Guid] AS varchar(40)) AS [Value]
		FROM [AttributeValue] V
		INNER JOIN [DefinedValue] SDV ON SDV.[Id] = V.[EntityId]
		INNER JOIN [DefinedValue] TDV ON TDV.[Guid] = V.[Value]
		INNER JOIN [Person] P ON P.[ConnectionStatusValueId] = SDV.[Id]
		LEFT OUTER JOIN [AttributeValue] PV ON PV.[AttributeId] = @PersonHowCreatedAttrId AND PV.[EntityId] = P.[Id]
		WHERE V.[AttributeId] = @ConnStatusTargetAttrId
		AND V.[Value] IS NOT NULl
		AND V.[Value] <> ''
		AND ( PV.[Value] IS NULL OR PV.[Value] = '' )
	) AS OV 
	ON NV.[AttributeId] = OV.[AttributeId]
	AND NV.[EntityId] = OV.[EntityId]
WHEN MATCHED THEN
	UPDATE SET [Value] = OV.[Value]
WHEN NOT MATCHED BY TARGET THEN
	INSERT ( [IsSystem],[AttributeId],[EntityId],[Value],[Guid]	)
	VALUES (0 ,OV.[AttributeId] ,OV.[EntityId] ,OV.[Value] ,NEWID() )
;

UPDATE P SET [ConnectionStatusValueId] = TDV.[Id]
FROM [AttributeValue] V
INNER JOIN [DefinedValue] SDV ON SDV.[Id] = V.[EntityId]
INNER JOIN [DefinedValue] TDV ON TDV.[Guid] = V.[Value]
INNER JOIN [Person] P ON P.[ConnectionStatusValueId] = SDV.[Id]
WHERE V.[AttributeId] = @ConnStatusTargetAttrId
AND V.[Value] IS NOT NULl
AND V.[Value] <> ''    

4.) Update your Blocks/Pages/Workflows

Now that you've created these attributes and configured the job, you can edit the settings for any block, page, workflow etc, so that when it creates a new person record, it creates the record with one of your new transient connection statuses. After the person record is created the job will then move that status to your new person attribute and update their actual connection status to reflect the target status.