dataverse-datagenerator

name	dataverse-datagenerator
description	Generate realistic, non-real-world test data for Dataverse tables. Discovers schema from Microsoft Learn docs and live Dataverse metadata, creates contextually appropriate data (addresses, GPS, names, dates), and imports via PAC CLI auth + Web API with upsert logic to skip existing records. TRIGGER: test data, sample data, generate data, populate table, seed data, mock data, fake data, Dataverse data generation.
user-invocable	true
argument-hint	[business scenario description] [table names]

Dataverse Test Data Generator

ROLE

You generate realistic, contextually appropriate test data for Dataverse tables. Data must be plausible but never based on real-world individuals, organisations, or locations. You understand Dataverse schema discovery, data relationships, bulk import patterns, and record ownership.

AUTHORITATIVE REFERENCES

Always use microsoft-docs tools to look up table schemas and field definitions:

• Dataverse tables: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/reference/about-entity-reference
• Web API metadata: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query-metadata-web-api
• FetchXML reference: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/reference/
• Web API CRUD: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/create-entity-web-api

---

AUTHENTICATION — CRITICAL RULES

NEVER use Azure CLI tokens (az account get-access-token) or attempt to extract tokens from Azure CLI. Central IT organisations restrict this.

Proper auth flow

1. Add the environment (one-time): pac auth create --environment https://orgXXX.crm.dynamics.com
2. PAC CLI opens browser → user authenticates → credentials cached
3. Python scripts use MSAL PublicClientApplication with the Microsoft first-party client ID to acquire tokens silently from the shared cache
4. If cache is cold, scripts fall back to interactive browser auth (same mechanism PAC CLI uses)

Auth constants

CLIENT_ID = "51f81489-12ee-4a9e-aaae-a2591f45987d"  # Microsoft first-party Power Apps app
AUTHORITY = "https://login.microsoftonline.com/organizations"  # or specific tenant

config.json pattern

Every data generation project should have a config.json:

{
    "dataverse_url": "https://orgXXX.crm.dynamics.com",
    "client_id": "51f81489-12ee-4a9e-aaae-a2591f45987d",
    "authority": "https://login.microsoftonline.com/organizations",
    "batch_size": 10
}

---

WORKFLOW

Phase 1: Understand the business scenario

Infer from user arguments or ask for:

1. Business context — industry, region, process (e.g., "Field Service plumbing company in Sydney")
2. Tables to populate — which Dataverse tables (e.g., account, contact, msdyn_workorder)
3. Volume — records per table (default: 10-20 for dev/test)
4. Relationships — how records link (e.g., work orders → accounts, bookings → work orders)
5. Record ownership — should records be owned by a specific user or team? (default: creating user)

Phase 2: Discover table schema

This skill supports ANY Dataverse table — standard (account, contact, msdyn_workorder), first-party app tables (msdyn_, msevtmgt_), and fully custom tables (cr123_, new_).

Use THREE steps: resolve table metadata, discover columns, then look up reference data.

Step 2a: Resolve entity set name and ownership type

This is mandatory for every table, especially custom tables. The entity set name (used in Web API URLs) is NOT always just the plural of the logical name.

Use the deploy script's DataverseClient to query the Web API metadata endpoint:

# In the deploy script, add this helper:
def resolve_table_metadata(session, api_url, logical_name):
    """Get entity set name and ownership type for any table."""
    resp = session.get(
        f"{api_url}/EntityDefinitions(LogicalName='{logical_name}')",
        params={"$select": "EntitySetName,OwnershipType,DisplayName,PrimaryNameAttribute,PrimaryIdAttribute"}
    )
    if resp.status_code == 200:
        meta = resp.json()
        return {
            "logical_name": logical_name,
            "entity_set_name": meta["EntitySetName"],           # e.g., "cr123_inspections" 
            "ownership_type": meta["OwnershipType"],            # "UserOwned" or "OrganizationOwned"
            "primary_name": meta["PrimaryNameAttribute"],       # e.g., "cr123_name"
            "primary_id": meta["PrimaryIdAttribute"],           # e.g., "cr123_inspectionid"
            "display_name": meta["DisplayName"]["UserLocalizedLabel"]["Label"],
        }
    return None

Ownership type matters:

• UserOwned → supports [e-mail] (user or team)
• OrganizationOwned → does NOT support ownerid, skip ownership assignment

Step 2b: Discover columns from live Dataverse

# Get all columns for a table with types
pac org fetch --xml "<fetch no-lock='true'>
  <entity name='attribute'>
    <attribute name='logicalname'/>
    <attribute name='displayname'/>
    <attribute name='attributetypename'/>
    <attribute name='isrequiredforentity'/>
    <filter>
      <condition attribute='entitylogicalname' operator='eq' value='{tablename}'/>
    </filter>
    <order attribute='logicalname'/>
  </entity>
</fetch>"

For custom table lookups, also discover relationship targets:

# Get lookup field targets from Web API metadata
resp = session.get(
    f"{api_url}/EntityDefinitions(LogicalName='{logical_name}')/Attributes/Microsoft.Dynamics.CRM.LookupAttributeMetadata",
    params={"$select": "LogicalName,Targets"}
)
# Returns e.g.: {"LogicalName": "cr123_accountid", "Targets": ["account"]}

Step 2c: Look up reference data from Microsoft Learn (standard tables only)

For standard/first-party tables, also check Microsoft Learn for documentation:

microsoft_docs_search("Dataverse {tablename} table columns")
microsoft_docs_fetch("https://learn.microsoft.com/en-us/power-apps/developer/data-platform/reference/entities/{tablename}")

Note: custom tables won't be documented on Microsoft Learn — rely solely on live metadata for those.

Step 2d: Get option set values and existing lookup targets

# Get existing lookup targets to link to
pac org fetch --xml "<fetch count='20' no-lock='true'>
  <entity name='{relatedtable}'>
    <attribute name='{relatedtable}id'/>
    <attribute name='name'/>
    <filter><condition attribute='statecode' operator='eq' value='0'/></filter>
  </entity>
</fetch>"

For option sets on custom tables, use Web API metadata (more reliable than stringmap):

# Get option set values for a specific field
resp = session.get(
    f"{api_url}/EntityDefinitions(LogicalName='{logical_name}')/Attributes(LogicalName='{field_name}')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata",
    params={"$expand": "OptionSet($select=Options)"}
)
# Returns Options array with Value and Label for each choice

Discover users and teams for record ownership

# Find system users (for ownerid assignment)
pac org fetch --xml "<fetch count='20' no-lock='true'>
  <entity name='systemuser'>
    <attribute name='systemuserid'/>
    <attribute name='fullname'/>
    <attribute name='domainname'/>
    <attribute name='businessunitid'/>
    <filter>
      <condition attribute='isdisabled' operator='eq' value='0'/>
      <condition attribute='accessmode' operator='ne' value='3'/>
    </filter>
    <order attribute='fullname'/>
  </entity>
</fetch>" --output json

# Find teams (for team ownership)
pac org fetch --xml "<fetch count='20' no-lock='true'>
  <entity name='team'>
    <attribute name='teamid'/>
    <attribute name='name'/>
    <attribute name='teamtype'/>
    <filter>
      <condition attribute='isdefault' operator='eq' value='0'/>
    </filter>
    <order attribute='name'/>
  </entity>
</fetch>" --output json

Phase 3: Generate realistic data

Apply domain-appropriate data generation rules (see DATA GENERATION RULES below).

Phase 4: Import via Python + Web API

Generate a Python import script using the proven DataverseClient pattern (see IMPORT SCRIPT PATTERN below).

---

RECORD OWNERSHIP

Dataverse records in user-owned tables have an ownerid field. By default, records are owned by the user who creates them. To assign ownership:

Assign to a specific user

record_data = {
    "name": "Greenfield Plumbing",
    "telephone1": "0399991234",
    "[e-mail]": f"/systemusers({user_guid})"
}

Assign to a team

record_data = {
    "name": "Greenfield Plumbing",
    "telephone1": "0399991234",
    "[e-mail]": f"/teams({team_guid})"
}

Ownership rules

• Query available users/teams during Phase 2 (schema discovery)
• If user requests specific ownership, resolve the user/team GUID first via FetchXML
• Distribute ownership across multiple users/teams realistically if appropriate
• If no ownership specified, omit [e-mail] (defaults to creating user)
• Some tables are organisation-owned (not user-owned) — these don't support ownerid
• CRITICAL: The owner team MUST have a security role assigned (e.g., "Field Service - Administrator"). Without privileges, record creation will fail with 403 prvReadXxx errors. Warn the user to check this before running the import.

---

DATA GENERATION RULES

CRITICAL: No real-world data

• Names: Plausible but fictional. Mix common first/last names from the target locale — never combinations that match real people.
• Companies: Descriptive fictional names (e.g., "Greenfield Plumbing Services", "Coastal HVAC Solutions").
• Addresses: Plausible street names and real suburb/city names for the target region, but fictional street numbers. Verify the suburb exists in the specified region.
• GPS coordinates: Must fall within the correct geographic area. Use coordinates that land on roads or commercial areas, not oceans or empty fields. Offset from real landmarks by 50-200m.
• Phone numbers: Valid format for the locale but fictional. AU: 04xx xxx xxx (mobile), 0x xxxx xxxx (landline). US: (555) xxx-xxxx.
• Email: Use @example.com, @test.com, or [e-mail] (RFC 2606 reserved domains).
• Dates: Relative to today. Appointments/bookings should span past 30 days to future 14 days. Use business hours for the locale.

Geographic data

When the user specifies a region:

1. Identify the city/region bounding box (lat/lng min/max)
2. Generate points within commercial/suburban areas (not waterways, parks, or restricted zones)
3. Use real suburb names but fictional street numbers
4. Ensure coordinates and addresses are geographically consistent (same suburb)
5. Latitude/Longitude to 6 decimal places
6. Spread points realistically (cluster near commercial areas, spread in suburbs)

Field type rules

Field Type	Generation Rule
SingleLine.Text	Context-appropriate text, max length respected
Multiple	1-3 sentences of domain-relevant description
Whole.None	Realistic integer for context (e.g., duration: 30-180 mins)
Currency	Realistic amounts with 2 decimal places
FP / Decimal	Appropriate precision for context
DateAndTime	Business hours, realistic scheduling gaps
TwoOptions	Weighted distribution (not all true/false)
Choice/OptionSet	Query valid values first, distribute realistically
Lookup	Must reference existing records (query first)
Owner	Assign via [e-mail] if specified

Relationship rules

• Create records in dependency order (accounts → contacts → work orders → bookings)
• Distribute lookups realistically (not all pointing to the same parent)
• Ensure referential integrity across all tables

---

IMPORT SCRIPT PATTERN

Project structure

project-folder/
  config.json           # Environment URL, client ID, authority, batch size
  generate_data.py      # Generates data as JSON files in ./output/
  deploy.py             # Imports data to Dataverse via Web API
  output/
    accounts.json       # Generated data files
    contacts.json
    ...
    .token_cache.bin    # MSAL token cache (auto-created, gitignored)
    id_mappings.json    # Maps local IDs to Dataverse GUIDs (auto-created)

Python import script structure (proven pattern)

"""Import test data into Dataverse via Web API.
Prerequisites: pip install msal requests
Auth: Uses pac auth cached credentials. Run `pac auth create --environment <url>` first.
"""
import json, sys, time, requests
from pathlib import Path

try:
    import msal
except ImportError:
    print("ERROR: pip install msal requests"); sys.exit(1)

BASE_DIR = Path(__file__).parent
OUTPUT_DIR = BASE_DIR / "output"
CONFIG = json.loads((BASE_DIR / "config.json").read_text())

DATAVERSE_URL = CONFIG["dataverse_url"].rstrip("/")
API_URL = f"{DATAVERSE_URL}/api/data/v9.2"
CLIENT_ID = "51f81489-12ee-4a9e-aaae-a2591f45987d"
AUTHORITY = CONFIG.get("authority", "https://login.microsoftonline.com/organizations")
SCOPE = [f"{DATAVERSE_URL}/.default"]
CACHE_PATH = OUTPUT_DIR / ".token_cache.bin"

# --- Auth (MSAL with pac auth cache) ---
def get_access_token():
    cache = msal.SerializableTokenCache()
    if CACHE_PATH.exists():
        cache.deserialize(CACHE_PATH.read_text(encoding="utf-8"))
    app = msal.PublicClientApplication(CLIENT_ID, authority=AUTHORITY, token_cache=cache)

    accounts = app.get_accounts()
    if accounts:
        result = app.acquire_token_silent(SCOPE, account=accounts[0])
        if result and "access_token" in result:
            CACHE_PATH.write_text(cache.serialize(), encoding="utf-8")
            print("  Using cached token")
            return result["access_token"]

    print("  Opening browser for authentication...")
    result = app.acquire_token_interactive(scopes=SCOPE, prompt="select_account")
    if "access_token" in result:
        CACHE_PATH.write_text(cache.serialize(), encoding="utf-8")
        return result["access_token"]
    print(f"ERROR: {result.get('error_description', result)}"); sys.exit(1)

# --- Dataverse Client ---
class DataverseClient:
    def __init__(self, token):
        self.session = requests.Session()
        self.session.headers.update({
            "Authorization": f"Bearer {token}",
            "OData-MaxVersion": "4.0", "OData-Version": "4.0",
            "Accept": "application/json",
            "Content-Type": "application/json; charset=utf-8",
        })
        # NOTE: Do NOT add "Prefer: return=representation" — it changes the
        # response from 204+OData-EntityId header to 201+full body, and the
        # body contains many GUID fields (address1_addressid, etc.) that
        # confuse generic ID parsing. Without it, 204 + OData-EntityId is reliable.

    def whoami(self):
        """Verify connection and return current user info."""
        resp = self.session.get(f"{API_URL}/WhoAmI()")
        if resp.status_code == 200:
            data = resp.json()
            print(f"  Connected as user: {data['UserId']}")
            return data
        print(f"  ERROR: WhoAmI failed: {resp.status_code}"); sys.exit(1)

    def find_existing(self, entity_set, filter_expr):
        """Check if record exists. Returns record dict or None."""
        # IMPORTANT: use params dict, not f-string URL — avoids & in values breaking the query
        resp = self.session.get(
            f"{API_URL}/{entity_set}",
            params={"$filter": filter_expr, "$top": "1"},
            headers={"Prefer": "odata.maxpagesize=1"},
        )
        if resp.status_code == 200:
            records = resp.json().get("value", [])
            return records[0] if records else None
        return None

    def create_record(self, entity_set, data):
        """Create a record with retry. Returns record ID or None."""
        for attempt in range(3):
            try:
                resp = self.session.post(f"{API_URL}/{entity_set}", json=data, timeout=120)
                if resp.status_code == 401:
                    self._refresh(); resp = self.session.post(f"{API_URL}/{entity_set}", json=data, timeout=120)
                if resp.status_code in (200, 201, 204):
                    # Parse ID from OData-EntityId header (always present on 204, usually on 201)
                    eid = resp.headers.get("OData-EntityId", "")
                    if "(" in eid:
                        return eid.split("(")[1].rstrip(")")
                    return None
                print(f"    ERROR {resp.status_code}: {resp.text[:300]}")
                return None
            except requests.exceptions.RequestException:
                if attempt < 2: time.sleep(5 * (2 ** attempt))
                else: return None

    def upsert(self, entity_set, name_field, name_value, data, id_field=None):
        """Create if not exists, skip if exists. Returns (action, record_id).
        id_field: the primary key field name (e.g., 'accountid', 'contactid').
                  MUST be provided to avoid picking up wrong GUID fields like address1_addressid."""
        safe_value = name_value.replace("'", "''")
        existing = self.find_existing(entity_set, f"{name_field} eq '{safe_value}'")
        if existing:
            rid = None
            if id_field and id_field in existing:
                rid = existing[id_field]
            else:
                rid = next((v for k, v in existing.items() if k.endswith("id") and isinstance(v, str) and len(v) == 36), None)
            print(f"  SKIP: '{name_value}' exists ({rid})")
            return "skip", rid
        rid = self.create_record(entity_set, data)
        if rid:
            print(f"  CREATE: '{name_value}' -> {rid}")
            return "create", rid
        return "error", None

    def _refresh(self):
        token = get_access_token()
        self.session.headers["Authorization"] = f"Bearer {token}"

# --- Import ---
def main():
    print("Authenticating...")
    token = get_access_token()
    client = DataverseClient(token)
    client.whoami()

    stats = {"created": 0, "skipped": 0, "errors": 0}
    id_map = {}  # local_key -> dataverse_guid

    # Import in dependency order. ALWAYS pass id_field to get the correct primary key.
    accounts = json.loads((OUTPUT_DIR / "accounts.json").read_text())
    print(f"\nImporting accounts... ({len(accounts)} records)")
    for acc in accounts:
        action, rid = client.upsert("accounts", "name", acc["name"], acc, id_field="accountid")
        stats[{"create": "created", "skip": "skipped"}.get(action, "errors")] += 1
        if rid: id_map[f"account:{acc['name']}"] = rid

    # Continue in dependency order: contacts, work orders, bookings, etc.
    # ALWAYS pass id_field: "contactid", "msdyn_workorderid", "incidentid", etc.
    # Use id_map to resolve lookup bindings:
    #   "[e-mail]": f"/accounts({id_map['account:Greenfield Plumbing']})"

    print(f"\nSummary: {stats['created']} created, {stats['skipped']} skipped, {stats['errors']} errors")
    (OUTPUT_DIR / "id_mappings.json").write_text(json.dumps(id_map, indent=2))

if __name__ == "__main__":
    main()

Lookup bindings (critical syntax)

# Customer lookup (polymorphic — can be account or contact)
"[e-mail]": f"/accounts({account_guid})"

# Single-type lookup
"[e-mail]": f"/accounts({account_guid})"
"[e-mail]": f"/msdyn_incidenttypes({type_guid})"

# Owner (user or team)
"[e-mail]": f"/systemusers({user_guid})"
"[e-mail]": f"/teams({team_guid})"

Alternative: CSV + Maker Portal Import

For large volumes (100+ records) or non-technical users, generate CSV files and import via the Maker Portal data import wizard.

---

GOTCHAS — learned from production use

1. find_existing must use params dict — never build the OData filter URL with f-strings. Characters like & in record names break the URL. Use requests.get(url, params={"$filter": expr}).
2. Do NOT use Prefer: return=representation — it changes the response from 204+OData-EntityId to 201+full body. The body contains many GUID fields (address1_addressid, address2_addressid, processid) that come before the primary key alphabetically, causing generic ID parsing to capture the wrong GUID. Without this header, 204+OData-EntityId is simple and reliable.
3. Handle 204 status code — without Prefer: return=representation, successful creates return 204 (not 201). The success check must be resp.status_code in (200, 201, 204).
4. Always pass id_field to upsert — generic "find first field ending in id" logic picks up address1_addressid before contactid. Always pass the explicit primary key field name: id_field="accountid", id_field="contactid", id_field="incidentid", etc.
5. Case contacts must belong to the case account — when setting both customerid_account and primarycontactid on an incident, the contact's parentcustomerid must point to that account. Build an account→contacts mapping and pick contacts from the correct account.
6. Avoid special characters in record names — &, accented characters (é, ñ), and unicode can cause filter/encoding issues. Use plain ASCII for demo data names.
7. Owner team needs a security role — assigning [e-mail] to a team with no security role fails with 403 prvReadXxx errors. Verify the team has appropriate privileges before running.
8. --output json not supported in all PAC CLI versions — some versions of pac org fetch don't support --output json. Use default table output and parse accordingly.
9. stringmap.objecttypecode requires integer — when querying option set values, objecttypecode is an integer (entity type code), not the logical name string.
10. Security role propagation delay — after assigning a security role to a team, there can be a brief delay before privileges take effect. If the first run gets 403 errors, wait 30 seconds and retry.
11. Token authority must match the tenant — use the specific tenant domain (e.g., contoso.onmicrosoft.com) or organizations in the MSAL authority. Using the wrong tenant returns "user is not a member of the organization".
12. Upsert key must be unique per record — if multiple records share the same title/name (e.g., "Delivery arrived during wrong moon phase"), the upsert will skip duplicates. Include unique identifiers (case number, date, account name) in titles.

---

UPSERT LOGIC

The import script MUST support multiple runs safely:

1. Before creating: query for existing record by name or alternate key
2. If exists: SKIP with log message (return existing GUID for lookups)
3. If not exists: CREATE new record
4. Lookup resolution: always query target table to get GUIDs — never hardcode GUIDs
5. Owner resolution: query systemuser/team table to resolve owner GUIDs — never hardcode

Logging

Authenticating...
  Using cached token
  Connected as user: abc123-...

Importing accounts... (5 records)
  SKIP: 'Greenfield Plumbing' exists (abc123...)
  CREATE: 'Coastal HVAC Solutions' -> def456...
  CREATE: 'Metro Electrical' -> ghi789...

Summary: 2 created, 1 skipped, 0 errors

---

WHEN INVOKED

1. Parse the business scenario and table list from arguments
2. Check auth: verify pac org who works for the target environment. If not connected, tell the user to run pac auth create --environment <url>
3. Discover schema from Microsoft Learn AND live Dataverse (Phase 2)
4. Present a brief data plan: tables, record counts, relationships, ownership, and 2-3 sample records. Keep this concise — a short table, not walls of text.
5. On approval (or if the user said "just do it"), generate the full dataset and import script
6. Run the import script
7. Verify imported records with a FetchXML query

Quick mode

If the user provides a clear scenario with tables (e.g., "generate 10 accounts and 20 contacts for a plumbing company in Sydney"), skip the approval step and go straight to generation + import. Present the data plan inline as you generate.

No arguments

If no arguments provided, ask for:

• Business scenario (industry, region, process)
• Tables to populate
• Approximate volume per table
• Ownership requirements (specific user/team, or default)