Update Activity Relationships

Automatically populate relationship fields on activities based on lookup field values

Overview

The Update Activity Relationships operation scans all activities in your Navigator Platform and populates their relationship fields based on existing lookup values. This ensures that multi-select fields (mag_familygroupmembers, mag_inboundreferrals, mag_outcomes) and the mag_relationships JSON field are synchronised with the actual individual, referral, and outcome lookup fields.

Unlike the rebuild operation which uses mag_relationships as the source, this operation builds relationships from lookup fields and populates the multi-select and JSON fields accordingly.

When to Use This Operation

Use This Operation When:

✅ After bulk imports where only lookup fields were populated
✅ Multi-select relationship fields are out of sync with lookups
✅ mag_relationships JSON is missing or outdated
✅ You've created activities via integrations that don't populate relationship fields
✅ You need to standardise relationship data across all activity types

Don't Use This Operation When:

❌ Lookup fields are empty (there's nothing to populate from)
❌ You need to rebuild N:N relationship records (use --RebuildMMRelationship instead)
❌ Activities are already correctly populated

Quick Start

Example 1: Update All Activities

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --UpdateActivityRelationships `
  --EnvironmentUrl https://myorg.crm6.dynamics.com `
  --ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef `
  --Secret YourClientSecretHere

Expected Output:

[2024-11-17 15:10:22.123] Starting activity relationship update...
[2024-11-17 15:10:22.456] Retrieving activity types from mag_activityregister...
[2024-11-17 15:10:23.789] Found 20 activity types to process
[2024-11-17 15:10:24.012] Processing appointment (mag_activitytype: 809730000)...
[2024-11-17 15:10:28.345] Updated 1,240 appointment records
[2024-11-17 15:10:28.678] Processing mag_casenote (mag_activitytype: 809730008)...
[2024-11-17 15:10:45.901] Updated 15,680 mag_casenote records
[2024-11-17 15:11:12.234] Processing mag_goal (mag_activitytype: 809730009)...
[2024-11-17 15:11:23.567] Updated 3,420 mag_goal records
... (continues for all activity types)
[2024-11-17 15:15:45.890] Activity relationship update completed
[2024-11-17 15:15:45.123] Total activities updated: 42,350

How It Works

Processing Steps

  1. Retrieve Activity Types: Queries mag_activityregister for all registered activity types
  2. For Each Activity Type:
    • Retrieve entity metadata to check which fields exist
    • Query all activities of that type
    • For each activity:
      • Extract Individual ID from mag_familygroupmember (or type-specific field)
      • Extract Referral ID from mag_inboundreferral (or type-specific field)
      • Extract Outcome ID from mag_outcome (or type-specific field)
      • Build NTONDATA string for multi-select fields
      • Build mag_relationships JSON object
      • Update the activity record

Supported Activity Types

The operation processes all 20 activity types:

Activity Type Code Logical Name Individual Field Referral Field
809730000 appointment mag_familygroupmember mag_inboundreferral
809730001 mag_consent mag_familygroupmember mag_inboundreferral
809730002 email mag_familygroupmember mag_inboundreferral
809730003 mag_phonecall mag_familygroupmember mag_inboundreferral
809730004 mag_review mag_familygroupmember mag_inboundreferral
809730005 task mag_familygroupmember mag_inboundreferral
809730006 mag_txtmessage mag_familygroupmember mag_inboundreferral
809730007 mag_assessmentactivity mag_client mag_wtl_referral
809730008 mag_casenote mag_familygroupmember mag_inboundreferral
809730009 mag_goal mag_client mag_wtl_referral
809730010 mag_wtl_referral mag_client N/A
809730011 mag_resource mag_familygroupmember mag_inboundreferral
809730012 mag_exit mag_familygroupmember mag_inboundreferral
809730013 mag_groupactivity mag_familygroupmember mag_inboundreferral
809730014 mag_alert mag_contact mag_inboundreferral
809730015 mag_bookingrequest mag_familygroupmember mag_inboundreferral
809730016 mag_diagnosis mag_contact mag_inboundreferral
809730017 mag_dynamicassessment mag_familygroupmember mag_inboundreferral
809730018 mag_packageofcare mag_contact mag_inboundreferral
809730019 mag_metabolicassessment mag_contact mag_inboundreferral
Note

Different activity types use different field names for lookups (mag_familygroupmember vs mag_client vs mag_contact). The operation automatically detects which fields exist on each activity type.

Field Population Logic

Multi-Select Fields (NTONDATA format):

NTONDATA:{"actions":[],"len":"mag_inboundreferral","ida":"mag_inboundreferralid","na":"mag_name","ids":"guid","names":"Referral Name"}

mag_relationships JSON:

{
  "Individuals": [{"Id": "12345678-1234-1234-1234-123456789012"}],
  "Referrals": [{"Id": "23456789-2345-2345-2345-234567890123"}],
  "Outcomes": [{"Id": "34567890-3456-3456-3456-345678901234"}]
}

Parameters

Required Parameters

--UpdateActivityRelationships

Flag to enable the operation.

Example:

--UpdateActivityRelationships

--EnvironmentUrl (-e)

The URL of your Dataverse environment.

Example:

--EnvironmentUrl https://myorg.crm6.dynamics.com

--ClientId (-c)

The Azure AD application (client) ID for authentication.

Example:

--ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef

Optional Parameters

--Secret (-s)

The client secret for authentication. If omitted, uses the default secret.

Example:

--Secret YourClientSecretHere
Caution

Never commit secrets to source control.

Real-World Scenarios

Scenario 1: Post-Import Relationship Sync

Situation: You imported 20,000 activities from Excel. The import populated mag_familygroupmember and mag_inboundreferral lookups, but the multi-select fields and mag_relationships are empty.

Solution:

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --UpdateActivityRelationships `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET

Expected Outcome:

  • mag_familygroupmembers populated with individual IDs
  • mag_inboundreferrals populated with referral IDs
  • mag_outcomes populated with outcome IDs (if applicable)
  • mag_relationships JSON contains all relationship data
  • Activities display correctly in relationship grids

Scenario 2: Fix Integration Data

Situation: A third-party integration creates activities but only sets the individual and referral lookup fields. The Navigator Platform views rely on mag_relationships JSON which is missing.

Solution:

Run monthly to keep relationships synchronised:

# monthly-sync.ps1
$ErrorActionPreference = "Stop"

$env:CLIENT_SECRET = Get-Secret -Name "DataverseClientSecret"

& "C:\Tools\WhanauTahi.Xpm.Tooling.CLI.exe" datamanipulation `
  --UpdateActivityRelationships `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:CLIENT_SECRET

# Send notification
$logFile = Get-ChildItem ".\Logs\DataManipulation_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Send-MailMessage -To "admin@example.com" -Subject "Activity Sync Complete" -Body "Updated $(Select-String 'Total activities updated:' $logFile)"

Performance & Scalability

Processing Characteristics

  • Sequential by Activity Type: Processes one activity type at a time
  • Batch Updates: Updates activities in batches for efficiency
  • Metadata Caching: Retrieves entity metadata once per activity type

Typical Performance

Total Activities Estimated Time Throughput
10,000 1-2 minutes 80-170 activities/sec
50,000 5-10 minutes 80-170 activities/sec
100,000 10-20 minutes 80-170 activities/sec
Note

Performance depends on number of activity types with data and network latency.

Troubleshooting

Issue: "Activity type not found in mag_activityregister"

Cause: Activity type exists in system but isn't registered.

Solution: Register the activity type in mag_activityregister table.

Issue: "Field does not exist on entity"

Cause: An activity type is missing one of the relationship fields.

Solution: The operation skips fields that don't exist. This is normal for some activity types. Check the log to see which fields were skipped.

Issue: "No activities updated"

Cause: All activities already have populated relationship fields, or no activities exist.

Solution: Verify activities exist and have lookup values:

SELECT TOP 10 * FROM mag_casenote WHERE mag_familygroupmember IS NOT NULL

Output & Logs

Log File Location

Logs\DataManipulation_2024-11-17_15-10-22-123.log

Log File Contents

[2024-11-17 15:10:22.123] Starting activity relationship update...
[2024-11-17 15:10:22.456] Retrieving activity types from mag_activityregister...
[2024-11-17 15:10:23.789] Found 20 activity types to process
[2024-11-17 15:10:24.012] Processing appointment (mag_activitytype: 809730000)...
[2024-11-17 15:10:24.345] Entity metadata retrieved: mag_familygroupmembers (exists), mag_inboundreferrals (exists), mag_outcomes (exists)
[2024-11-17 15:10:28.678] Updated 1,240 appointment records
[2024-11-17 15:10:28.901] Processing mag_consent (mag_activitytype: 809730001)...
... (continues for all activity types)
[2024-11-17 15:15:45.890] Activity relationship update completed
[2024-11-17 15:15:45.123] Total activities updated: 42,350

See Also