Populate Family Group Member List

Automatically generate human-readable family member name lists

Overview

The Populate Family Group Member List operation processes family group records and populates the mag_familygroupmemberslist field with a comma-separated list of family member names. This provides a quick, human-readable reference of who belongs to each family group without needing to query the membership table.

The operation:

Queries all mag_familygroup records
Retrieves family group membership records for each group
Builds comma-separated string of member names
Updates mag_familygroupmemberslist field

When to Use This Operation

Use This Operation When:

✅ After bulk family group membership imports
✅ mag_familygroupmemberslist field is empty or outdated
✅ You need human-readable family member lists for views or reports
✅ Family membership has changed and lists need refreshing
✅ After data migration where membership data was imported

Don't Use This Operation When:

❌ No family group membership records exist
❌ Family group member lists are already up to date
❌ You need programmatic access to members (use mag_familygroupmembership instead)

Quick Start

Example: Update All Family Group Member Lists

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

Expected Output:

[2024-11-17 17:00:15.123] Starting family group member list population...
[2024-11-17 17:00:15.456] Retrieving family groups...
[2024-11-17 17:00:18.789] Found 8,450 family groups to process
[2024-11-17 17:00:19.012] Processing family group 1/8,450: Smith Family
[2024-11-17 17:00:19.345] Found 4 members: John Smith, Jane Smith, Emily Smith, Michael Smith
[2024-11-17 17:00:19.678] Updated mag_familygroupmemberslist
[2024-11-17 17:00:19.901] Processing family group 2/8,450: Jones Family
... (continues)
[2024-11-17 17:08:35.234] Family group member list population completed
[2024-11-17 17:08:35.567] Total family groups processed: 8,450
[2024-11-17 17:08:35.890] Total members processed: 19,820
[2024-11-17 17:08:35.123] Average members per family: 2.35

How It Works

Processing Steps

Retrieve Family Groups: Queries all mag_familygroup records
For Each Family Group:
- Query mag_familygroupmembership where mag_familygroup equals the current family group ID
- Retrieve contact names (fullname field) for all members
- Build comma-separated string: "John Smith, Jane Smith, Emily Smith"
- Update mag_familygroupmemberslist field on family group record
Sequential Processing: Processes family groups one at a time

Example Output Format

Family Group with 4 Members:

mag_familygroupmemberslist: "John Smith, Jane Smith, Emily Smith, Michael Smith"

Family Group with 1 Member:

mag_familygroupmemberslist: "Sarah Johnson"

Family Group with No Members:

mag_familygroupmemberslist: "" (empty string)

Membership Query

The operation retrieves membership using this logic:

SELECT c.fullname
FROM mag_familygroupmembership fgm
INNER JOIN contact c ON fgm.mag_contact = c.contactid
WHERE fgm.mag_familygroup = '<family-group-id>'
ORDER BY c.fullname ASC

Note

Member names are sorted alphabetically for consistent ordering.

Parameters

Required Parameters

`--FamilyGroupMemberList`

Flag to enable the operation.

Example:

--FamilyGroupMemberList

`--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: Refresh Lists After Membership Changes

Situation: Over the past month, 500 family members joined or left family groups. You need to refresh all family group member lists to reflect current membership.

Solution:

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

# Verify updates in Power Apps
# View: Family Groups
# Column: Family Group Members List
# Check that all families show current members

Expected Outcome:

All family groups have up-to-date member lists
mag_familygroupmemberslist reflects current membership
Lists are visible in views and forms for quick reference

Scenario 2: Post-Migration List Population

Situation: You've migrated 10,000 family groups and 25,000 family group membership records from a legacy system. The mag_familygroupmemberslist field is empty because it wasn't in the old system.

Solution:

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

# Check the log for statistics
$logFile = Get-ChildItem ".\Logs\DataManipulation_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Get-Content $logFile | Select-String "Total family groups", "Average members"

Expected Outcome:

All 10,000 family groups have populated member lists
Lists display correctly in Power Apps views
Users can quickly see family composition without drilling into membership records

Scenario 3: Scheduled Weekly Refresh

Situation: Your organisation adds/removes family members weekly. You want to automatically refresh member lists every Sunday night.

Solution:

# weekly-memberlist-refresh.ps1
$ErrorActionPreference = "Stop"

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

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

# Archive log
$logFile = Get-ChildItem ".\Logs\DataManipulation_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Copy-Item $logFile "\\fileserver\logs\memberlist-$(Get-Date -Format 'yyyy-MM-dd').log"

# Send notification
$summary = Get-Content $logFile | Select-String "Total family groups", "Average members"
Send-MailMessage -To "admin@example.com" -Subject "Member List Refresh Complete" -Body ($summary -join "`n")

Schedule via Windows Task Scheduler:

Trigger: Weekly on Sunday at 10:00 PM
Action: Run PowerShell script

Performance & Scalability

Processing Characteristics

Sequential Processing: Processes family groups one at a time
Batch Updates: Updates family group records in batches
Membership Queries: One query per family group to retrieve members

Typical Performance

Family Groups	Avg Members/Group	Estimated Time	Throughput
1,000	2-3	30-60 seconds	15-35 groups/sec
5,000	2-3	2-5 minutes	15-40 groups/sec
10,000	2-3	5-10 minutes	15-35 groups/sec
50,000	2-3	25-50 minutes	15-35 groups/sec

Note

Performance depends on number of members per family group and network latency.

Optimisation Tips

1. Run during off-peak hours to avoid impacting user experience:

# Schedule for evenings or weekends

2. Monitor progress in real-time:

# In another PowerShell window
Get-Content ".\Logs\DataManipulation_*" -Wait -Tail 20

Troubleshooting

Issue: "No family groups found"

Cause: No mag_familygroup records exist.

Solution:

Verify family groups exist:

SELECT TOP 10 mag_familygroupid, mag_name FROM mag_familygroup

Ensure family groups were created during data migration or normal operations

Issue: "Member lists are empty"

Cause: No mag_familygroupmembership records exist, or contacts are not linked.

Solution:

Verify memberships exist:

SELECT TOP 10 mag_familygroup, mag_contact 
FROM mag_familygroupmembership

Ensure contacts are created and linked to family groups

Issue: "Duplicate names in lists"

Cause: Multiple membership records exist for the same contact and family group.

Solution:

Identify duplicates:

SELECT mag_familygroup, mag_contact, COUNT(*) AS DuplicateCount
FROM mag_familygroupmembership
GROUP BY mag_familygroup, mag_contact
HAVING COUNT(*) > 1

Delete duplicate membership records

Issue: "Lists not updating in UI"

Cause: Browser cache or form cache.

Solution:

Hard refresh the browser (Ctrl+F5)
Clear Dataverse cache in Power Apps
Verify field updated in database:

SELECT mag_familygroupid, mag_name, mag_familygroupmemberslist 
FROM mag_familygroup 
WHERE mag_familygroupid = '<specific-id>'

Output & Logs

Log File Location

Logs\DataManipulation_2024-11-17_17-00-15-123.log

Log File Contents

Startup Phase:

[2024-11-17 17:00:15.123] Starting family group member list population...
[2024-11-17 17:00:15.456] Retrieving family groups...
[2024-11-17 17:00:18.789] Found 8,450 family groups to process

Processing Phase:

[2024-11-17 17:00:19.012] Processing family group 1/8,450: Smith Family (12345678-1234-1234-1234-123456789012)
[2024-11-17 17:00:19.345] Retrieving family group memberships...
[2024-11-17 17:00:19.678] Found 4 members
[2024-11-17 17:00:19.901] Member names: John Smith, Jane Smith, Emily Smith, Michael Smith
[2024-11-17 17:00:19.234] Updating mag_familygroupmemberslist field...
[2024-11-17 17:00:19.567] Update successful
[2024-11-17 17:00:19.890] Processing family group 2/8,450: Jones Family (23456789-2345-2345-2345-234567890123)
... (continues)
[2024-11-17 17:04:35.123] Progress: 5,000/8,450 (59.2%) - 25 groups/sec

Completion Phase:

[2024-11-17 17:08:35.234] Family group member list population completed
[2024-11-17 17:08:35.567] Total family groups processed: 8,450
[2024-11-17 17:08:35.890] Total members processed: 19,820
[2024-11-17 17:08:35.123] Average members per family: 2.35
[2024-11-17 17:08:35.456] Family groups with 0 members: 120
[2024-11-17 17:08:35.789] Family groups with 1 member: 2,340
[2024-11-17 17:08:35.012] Family groups with 2+ members: 5,990
[2024-11-17 17:08:35.345] Total time: 520 seconds (8 min 40 sec)
[2024-11-17 17:08:35.678] Average throughput: 16 groups/sec

Understanding the Results

Success Indicators:

✅ "Total family groups processed" matches expected count
✅ "Average members per family" is reasonable (typically 2-4)
✅ No ERROR lines in log
✅ mag_familygroupmemberslist visible in Power Apps

Warning Signs:

⚠️ "Family groups with 0 members" is very high - Membership data may be missing
⚠️ "Average members per family" is unusually low/high - Data quality issue
⚠️ Many ERROR messages - Check permissions or data integrity

Populate Family Group Member List

Overview

When to Use This Operation

Use This Operation When:

Don't Use This Operation When:

Quick Start

Example: Update All Family Group Member Lists

How It Works

Processing Steps

Example Output Format

Membership Query

Note

Parameters

Required Parameters

--FamilyGroupMemberList

--EnvironmentUrl (-e)

--ClientId (-c)

Optional Parameters

--Secret (-s)

Caution

Real-World Scenarios

Scenario 1: Refresh Lists After Membership Changes

Scenario 2: Post-Migration List Population

Scenario 3: Scheduled Weekly Refresh

Performance & Scalability

Processing Characteristics

Typical Performance

Note

Optimisation Tips

Troubleshooting

Issue: "No family groups found"

Issue: "Member lists are empty"

Issue: "Duplicate names in lists"

Issue: "Lists not updating in UI"

Output & Logs

Log File Location

Log File Contents

Understanding the Results

See Also

`--FamilyGroupMemberList`

`--EnvironmentUrl` (-e)

`--ClientId` (-c)

`--Secret` (-s)