Add Dataverse

📋 Shared Instructions: [shared-instructions.md](${PLUGIN_ROOT}/shared/shared-instructions.md) - Cross-cutting concerns.

References:

• dataverse-reference.md - Picklist fields, virtual fields, lookups, file/image columns, form patterns (CRITICAL)
• api-authentication-reference.md - Dataverse API auth, token, publisher prefix
• table-management-reference.md - Query, create, extend tables and columns
• data-architecture-reference.md - Relationship types, dependency tiers

Add Dataverse

Two paths: existing tables (skip to Step 5) or new tables (full workflow).

Workflow

1. Plan → 2. Setup API Auth → 3. Review Existing Tables → 4. Create Tables → 5. Add Data Source → 6. Review Generated Files → 7. Build

---

Step 1: Plan

Check memory bank for project context. Ask the user:

1. Which Dataverse table(s) do they need? (e.g., account, contact, cr123_customentity) 2. Do the tables already exist in their environment, or do they need to create new ones?

If tables already exist: Skip to Step 5.

If creating new tables:

• Ask about the data they need and design an appropriate schema
• Use standard Dataverse tables when appropriate (contact for people, account for organizations)
• Build a dependency graph -- see data-architecture-reference.md for tier classification
• Enter plan mode with EnterPlanMode, present ER model with tables, columns, relationships, and creation order
• Get approval with ExitPlanMode

Step 2: Setup API Auth (if creating tables)

See api-authentication-reference.md for full details.

az account show   # Verify Azure CLI logged in

# Find your Dataverse environment URL:
# In make.powerapps.com → Settings → Developer resources → Web API endpoint
# It looks like: https://<org-name>.crm.dynamics.com/api/data/v9.2/
# Use the base URL: https://<org-name>.crm.dynamics.com

$api = Initialize-DataverseApi -EnvironmentUrl "https://<org>.crm.dynamics.com"
$headers = $api.Headers
$baseUrl = $api.BaseUrl
$publisherPrefix = $api.PublisherPrefix

Requires System Administrator or System Customizer security role.

Step 3: Review Existing Tables (if creating tables)

Always query existing tables first before creating:

$existingTables = Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions?`$filter=IsCustomEntity eq true&`$select=SchemaName,LogicalName,DisplayName" -Headers $headers

See table-management-reference.md for Find-SimilarTables, Compare-TableSchemas, and Build-TableNameMapping functions.

Present findings to user with AskUserQuestion:

• Tables that can be reused (already exist with matching columns)
• Tables that need extension (exist but missing columns)
• Tables that must be created (no match found)

Step 4: Create Tables (if creating tables)

Get explicit confirmation before creating. Create in dependency order:

• Tier 0: Reference tables (no dependencies)
• Tier 1: Primary entities (reference Tier 0)
• Tier 2: Dependent tables (reference Tier 1)

Use safe functions from table-management-reference.md:

• New-DataverseTableIfNotExists
• Add-DataverseColumnIfNotExists
• Add-DataverseLookupIfNotExists (from data-architecture-reference.md)

Step 5: Add Data Source

For each table:

npx power-apps add-data-source -a dataverse -t <table-logical-name>

Can add multiple tables by running the command for each one.

Step 6: Review Generated Files

The command generates:

• src/generated/models/{Table}Model.ts -- TypeScript interfaces, plus {Table}FileColumnName, {Table}ImageColumnName, {Table}UploadColumnName union types if the table has file/image columns
• src/generated/services/{Table}Service.ts -- CRUD methods (create, get, getAll, update, delete) plus upload, downloadFile, downloadImage, deleteFileOrImage if file/image columns exist

Show the user a usage example:

import { AccountsService } from "../generated/services/AccountsService";

const result = await AccountsService.getAll({
  select: ["name", "accountnumber"],
  filter: "statecode eq 0",
  orderBy: ["name asc"],
  top: 50
});
const accounts = result.data || [];

Key rules:

• Use generated services (e.g., AccountsService.getAll()), not fetch/axios
• Check result.data for actual data
• Don't edit generated files unless needed
• Read [dataverse-reference.md](./references/dataverse-reference.md) before writing any Dataverse code -- picklist fields, virtual fields, lookups, and file/image columns all have critical gotchas

Step 7: Build

npm run build

Fix TypeScript errors before proceeding. Do NOT deploy yet.

Update Memory Bank

Record which tables were added (or created), generated files, and any schema notes.

API Authentication Reference

Uses Dataverse OData Web API with Azure CLI authentication (az account get-access-token).

Prerequisites

Ensure Azure CLI is authenticated before proceeding:

# Verify Azure CLI is logged in
az account show

# If not logged in, run:
az login

Get Environment URL

Find your Dataverse environment URL in make.powerapps.com:

Settings → Developer resources → Web API endpoint

It looks like https://<org-name>.crm.dynamics.com/api/data/v9.2/. Use the base URL: https://<org-name>.crm.dynamics.com.

Get Access Token

$envUrl = "https://<org>.crm.dynamics.com"  # Replace with your org URL
$token = (az account get-access-token --resource $envUrl --query accessToken -o tsv)

Set Up API Headers

$headers = @{
    "Authorization" = "Bearer $token"
    "Content-Type" = "application/json"
    "OData-MaxVersion" = "4.0"
    "OData-Version" = "4.0"
    "Prefer" = "return=representation"
}

$baseUrl = "$envUrl/api/data/v9.2"

API Headers Reference

Get Default Publisher Prefix

The publisher prefix is used for naming custom tables and columns. Fetch it dynamically:

function Get-DefaultPublisherPrefix {
    param(
        [Parameter(Mandatory=$true)]
        [string]$BaseUrl,

        [Parameter(Mandatory=$true)]
        [hashtable]$Headers
    )

    $defaultPublisher = Invoke-RestMethod -Uri "$BaseUrl/publishers?`$filter=friendlyname eq 'CDS Default Publisher'&`$select=customizationprefix,friendlyname" -Headers $Headers

    if ($defaultPublisher.value.Count -eq 0) {
        throw "Could not find CDS Default Publisher in the environment"
    }

    $prefix = $defaultPublisher.value[0].customizationprefix
    Write-Host "Customization Prefix: $prefix" -ForegroundColor Cyan
    return $prefix
}

Complete Setup Script

function Initialize-DataverseApi {
    param(
        [Parameter(Mandatory=$true)]
        [string]$EnvironmentUrl,

        [string]$SolutionName = $null
    )

    $token = (az account get-access-token --resource $EnvironmentUrl --query accessToken -o tsv)

    if (-not $token) {
        throw "Failed to get access token. Make sure you're logged in with 'az login'"
    }

    $headers = @{
        "Authorization" = "Bearer $token"
        "Content-Type" = "application/json"
        "OData-MaxVersion" = "4.0"
        "OData-Version" = "4.0"
        "Prefer" = "return=representation"
    }

    if ($SolutionName) {
        $headers["MSCRM.SolutionUniqueName"] = $SolutionName
    }

    $baseUrl = "$EnvironmentUrl/api/data/v9.2"
    $publisherPrefix = Get-DefaultPublisherPrefix -BaseUrl $baseUrl -Headers $headers

    return @{
        Headers = $headers
        BaseUrl = $baseUrl
        PublisherPrefix = $publisherPrefix
    }
}

Token Refresh

Access tokens expire after ~1 hour. For long-running scripts:

function Get-FreshToken {
    param([string]$EnvironmentUrl)
    return (az account get-access-token --resource $EnvironmentUrl --query accessToken -o tsv)
}

function Invoke-DataverseApi {
    param(
        [string]$Uri,
        [string]$Method = "Get",
        [hashtable]$Headers,
        [string]$Body = $null,
        [string]$EnvironmentUrl
    )

    try {
        if ($Body) {
            return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers -Body $Body
        } else {
            return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers
        }
    } catch {
        if ($_.Exception.Response.StatusCode -eq 401) {
            Write-Host "Token expired, refreshing..." -ForegroundColor Yellow
            $newToken = Get-FreshToken -EnvironmentUrl $EnvironmentUrl
            $Headers["Authorization"] = "Bearer $newToken"

            if ($Body) {
                return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers -Body $Body
            } else {
                return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers
            }
        }
        throw
    }
}

Verify Connection

try {
    $whoami = Invoke-RestMethod -Uri "$baseUrl/WhoAmI" -Headers $headers
    Write-Host "Connected as: $($whoami.UserId)" -ForegroundColor Green
} catch {
    Write-Host "Connection failed: $($_.Exception.Message)" -ForegroundColor Red
}

Required Permissions

To create tables and manage schema, you need one of these Dataverse security roles:

• System Administrator - Full access
• System Customizer - Can create and modify tables

Data Architecture Reference

Relationship Types

• 1:N (One-to-Many): Parent table referenced by child via Lookup. Parent must exist first.
• N:N (Many-to-Many): Junction table created automatically. Both tables must exist first.
• Self-Referential: Table references itself. Table must exist before adding self-lookup.

Dependency Tiers

Create tables in order by their dependencies:

• Tier 0: Reference/lookup tables (no dependencies) - Category, Status, Department
• Tier 1: Primary entities (reference Tier 0) - Product->Category, Employee->Department
• Tier 2: Dependent/transaction tables (reference Tier 1) - Order->Customer, OrderLine->Order
• Tier 3: Deeply nested tables (rare)

Common Relationship Patterns

Adding Lookups

function Add-DataverseLookup {
    param(
        [string]$SourceTable,       # Table getting the lookup column
        [string]$TargetTable,       # Table being referenced
        [string]$SchemaName,        # Lookup column schema name
        [string]$DisplayName        # Lookup column display name
    )

    $lookup = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata"
        "SchemaName" = "${publisherPrefix}_${TargetTable}_${SourceTable}"
        "ReferencedEntity" = $TargetTable
        "ReferencingEntity" = $SourceTable
        "Lookup" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.LookupAttributeMetadata"
            "SchemaName" = $SchemaName
            "DisplayName" = @{
                "@odata.type" = "Microsoft.Dynamics.CRM.Label"
                "LocalizedLabels" = @(@{
                    "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"
                    "Label" = $DisplayName
                    "LanguageCode" = 1033
                })
            }
        }
        "CascadeConfiguration" = @{
            "Assign" = "NoCascade"
            "Delete" = "RemoveLink"
            "Merge" = "NoCascade"
            "Reparent" = "NoCascade"
            "Share" = "NoCascade"
            "Unshare" = "NoCascade"
        }
    }

    $body = $lookup | ConvertTo-Json -Depth 10
    Invoke-RestMethod -Uri "$baseUrl/RelationshipDefinitions" -Method Post -Headers $headers -Body $body
}

function Add-DataverseLookupIfNotExists {
    param(
        [string]$SourceTable,
        [string]$TargetTable,
        [string]$SchemaName,
        [string]$DisplayName
    )

    if (Test-ColumnExists -TableLogicalName $SourceTable -ColumnLogicalName $SchemaName.ToLower()) {
        Write-Host "    [SKIP] Lookup '$SchemaName' already exists on '$SourceTable'" -ForegroundColor Yellow
        return
    }

    Write-Host "    [CREATE] Adding lookup '$SchemaName' on '$SourceTable' -> '$TargetTable'..." -ForegroundColor Cyan
    Add-DataverseLookup -SourceTable $SourceTable -TargetTable $TargetTable -SchemaName $SchemaName -DisplayName $DisplayName
    Write-Host "    [OK] Lookup created" -ForegroundColor Green
}

Validation Rules

Before table creation, validate: 1. No circular dependencies 2. All referenced tables exist 3. Lookup targets have primary keys 4. Self-references: create table first, then add self-lookup

Dataverse Reference

Critical patterns for working with Dataverse in Power Apps code apps. Read this before writing any Dataverse code.

Choice/Picklist Fields - CRITICAL

Choice fields (PicklistType) store integer values, not string labels. The schema defines both:

• enum: String labels for display (e.g., "Active", "Inactive")
• x-ms-enum-values: Numeric values used by API (e.g., 0, 1)

See Types of columns - Choice

The generated models include enum mappings you can import:

// Generated in models - maps numeric value to string label
import { TableNameFieldName } from '../generated/models/TableNameModel';
// e.g., { 0: 'Active', 1: 'Inactive', 2: 'Pending' }
const label = TableNameFieldName[numericValue];

// CORRECT - Define enum constants with numeric values from schema
const Status = {
  Active: 0,
  Inactive: 1,
  Pending: 2
} as const;

// CORRECT - Filter using numeric values
const activeRecords = records.filter(r => r.statuscode === Status.Active);

// CORRECT - Create with numeric choice value
const newRecord: any = {
  'prefix_name': 'My Record',
  'prefix_category': 100000000,  // Numeric value, NOT "Category Name"
  'statuscode': Status.Active
};

// CORRECT - Convert numeric to label for display
const getStatusLabel = (status?: number): string => {
  switch (status) {
    case Status.Active: return 'Active';
    case Status.Inactive: return 'Inactive';
    case Status.Pending: return 'Pending';
    default: return 'Unknown';
  }
};

// WRONG - String comparison fails (TypeScript error: number vs string)
records.filter(r => r.statuscode === 'Active');

// WRONG - API rejects string values
{ 'prefix_category': 'Electronics' }  // Error: Cannot convert 'Electronics' to Edm.Int32

MultiSelect Choice (MultiSelectPicklistType): stores multiple integer values. Not supported in workflows, business rules, charts, rollups, or calculated columns.

Virtual/Formatted Fields - CRITICAL

Fields ending in name (e.g., prefix_statusname, prefix_categoryname) are often VirtualType -- computed, read-only fields that cannot be selected in OData queries. They cause errors like:

"Could not find a property named 'prefix_fieldname' on type 'Microsoft.Dynamics.CRM.prefix_tablename'"

// WRONG - Virtual fields cannot be queried
select: ['prefix_status', 'prefix_statusname']  // statusname will fail

// CORRECT - Only select actual fields, convert to labels in code
select: ['prefix_status']
// Then use getStatusLabel(record.prefix_status) for display

Check the generated model's x-ms-dataverse-type: if it's VirtualType, don't include in select.

Formatted Values (Server-Side Formatting) - IMPORTANT

Instead of formatting dates, choice labels, and currency client-side, request formatted values from the server using the Prefer header:

Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

Or request all annotations (includes lookup metadata too):

Prefer: odata.include-annotations="*"

See Formatted values

The response includes both raw and formatted values side-by-side:

{
  "revenue": 20000.0000,
  "revenue@OData.Community.Display.V1.FormattedValue": "$20,000.00",
  "customertypecode": 1,
  "customertypecode@OData.Community.Display.V1.FormattedValue": "Competitor",
  "modifiedon": "2023-04-07T21:59:01Z",
  "modifiedon@OData.Community.Display.V1.FormattedValue": "4/7/2023 2:59 PM",
  "_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
  "_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)"
}

What gets formatted:

Lookup metadata annotations (useful for polymorphic lookups like Owner, Customer):

• _fieldname_value@Microsoft.Dynamics.CRM.lookuplogicalname -- which table the record belongs to (e.g., "systemuser" or "team")
• _fieldname_value@Microsoft.Dynamics.CRM.associatednavigationproperty -- navigation property name for $expand

When to use formatted values vs client-side formatting:

• Use formatted values when displaying data as-is (dates, labels, currency) -- respects user locale and timezone
• Use client-side formatting when you need custom display logic (e.g., relative dates, custom label mapping, conditional formatting)

Lookup Fields - CRITICAL

Lookup columns represent many-to-one (N:1) relationships. The Web API exposes three properties per lookup:

See Lookup properties

// CORRECT - Read the related record's GUID via _value property
const result = await AccountsService.getAll({
  select: ['name', '_primarycontactid_value', 'primarycontactidname']
});
for (const account of result.data || []) {
  if (account._primarycontactid_value) {
    const contact = await ContactsService.get(account._primarycontactid_value);
  }
}

// WRONG - Navigation property is an object, not a GUID
await ContactsService.get(account.primarycontactid); // object, not string

// Display name only (no extra query needed)
<p>Contact: {account.primarycontactidname}</p>

Common lookup fields: _primarycontactid_value, _customerid_value, _ownerid_value, _parentaccountid_value, _transactioncurrencyid_value

Special lookup types:

• Customer: references Account OR Contact
• Owner: references User OR Team (every user-owned table has one)

Setting Lookups (Creating/Updating Records)

Lookup properties (_fieldname_value) are read-only. To set a relationship, use the single-valued navigation property with @odata.bind:

// CORRECT - Use @odata.bind for lookup fields
const newRecord: any = {
  'prefix_name': 'My Record',
  'prefix_ParentAccount@odata.bind': `/accounts(${accountGuid})`,
  'prefix_status': 100000000
};

// WRONG - _value properties are read-only, cannot be set
{ '_prefix_parentaccountid_value': accountGuid }  // May fail on create

The @odata.bind value must be an entity set path with the GUID: /<entitysetname>(<guid>)

File and Image Columns

Dataverse supports two special column types for binary content:

The generated model exports type-safe union types for the file and image columns on the table. Use these types for all columnName arguments — never pass an arbitrary string:

// Example from a table with two file columns and two image columns:
type AccountsFileColumnName  = 'cr3d5_filecol' | 'cr3d5_filecol2';
type AccountsImageColumnName = 'cr3d5_imagecol' | 'entityimage';
type AccountsUploadColumnName = AccountsFileColumnName | AccountsImageColumnName;

The generated service exposes four methods for file/image operations.

upload(id, columnName, file, fileDisplayName?)

Uploads a file or image to a record column. Accepts a standard browser File object directly.

• columnName — must be UploadColumnName (works for both file and image columns)
• fileDisplayName — optional friendly name shown in Dataverse; defaults to file.name
• Returns a result object with success, data, and error fields; for uploads, data is empty

const [uploading, setUploading] = useState(false);

const handleUpload = async () => {
  setUploading(true);
  const result = await AccountsService.upload(recordId, columnName, selectedFile, displayName);
  setUploading(false);
  if (result.error) {
    showToast('Upload failed: ' + result.error.message, 'error');
  } else {
    showToast('File uploaded successfully', 'success');
    onUploadSuccess?.();  // refresh parent list
  }
};

<button onClick={handleUpload} disabled={uploading}>
  {uploading ? 'Uploading...' : 'Upload'}
</button>

downloadFile(id, columnName)

Downloads a file column. The file bytes are returned in result.data.

• columnName — must be FileColumnName (file columns only, not image)
• Returns IOperationResult<Uint8Array> — use result.data for the raw bytes and result.fileName for the original filename

downloadImage(id, columnName, fullSize?)

Downloads an image column and returns the raw bytes. Pass fullSize: true for the original resolution; defaults to thumbnail.

• columnName — must be ImageColumnName (image columns only, not file)
• fullSize — optional boolean, default false (thumbnail)
• Returns IOperationResult<Uint8Array>

deleteFileOrImage(id, columnName)

Deletes the file or image stored in a column. Works for both file and image columns.

• columnName — must be UploadColumnName
• Returns IOperationResult<void>

const result = await AccountsService.deleteFileOrImage(recordId, columnName);
if (!result.error) {
  onUploadSuccess?.();  // refresh parent list
}

Common Patterns

• Disable during operation: Set a loading flag and disable upload/delete buttons while the call is in flight to prevent double-submits.
• Toast feedback: Show success/error after upload and delete. Auto-dismiss after ~5 seconds.
• Refresh after mutation: Call a refresh callback after upload or delete so the UI reflects the latest state.

TypeScript useState with Choice Values - CRITICAL

When using useState with enum constants, TypeScript infers literal types. Explicitly type as number:

// WRONG - TypeScript infers status as literal type 0
const [formData, setFormData] = useState({
  status: Status.Active,  // type inferred as literal 0
});
setFormData({ ...formData, status: Number(value) }); // Error: number not assignable to 0

// CORRECT - Explicitly type choice fields as number
const [formData, setFormData] = useState<{
  name: string;
  status: number;
}>({
  name: '',
  status: Status.Active,  // now typed as number
});

Common Dataverse API Errors

Column Type Quick Reference

Table Management Reference

Query Existing Custom Tables

Before creating tables, review what exists:

$existingTables = Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions?`$filter=IsCustomEntity eq true&`$select=SchemaName,LogicalName,DisplayName,Description,PrimaryNameAttribute" -Headers $headers

Write-Host "Found $($existingTables.value.Count) custom tables:" -ForegroundColor Cyan
$existingTables.value | ForEach-Object {
    $displayName = $_.DisplayName.UserLocalizedLabel.Label
    Write-Host "  - $($_.SchemaName) ($displayName)" -ForegroundColor Yellow
}

Get Table Schema Details

function Get-TableSchema {
    param([string]$TableLogicalName)

    $tableInfo = Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions(LogicalName='$TableLogicalName')?`$expand=Attributes(`$select=SchemaName,LogicalName,AttributeType,DisplayName,MaxLength)" -Headers $headers

    Write-Host "`nTable: $($tableInfo.SchemaName)" -ForegroundColor Cyan
    Write-Host "Primary Column: $($tableInfo.PrimaryNameAttribute)"

    $tableInfo.Attributes | Where-Object {
        $_.SchemaName -notmatch '^(Created|Modified|Owner|State|Status|Version|Import|Overridden|TimeZone|UTCConversion|Traversed)'
    } | ForEach-Object {
        $displayName = if ($_.DisplayName.UserLocalizedLabel) { $_.DisplayName.UserLocalizedLabel.Label } else { $_.SchemaName }
        Write-Host "    - $($_.SchemaName) ($($_.AttributeType)) - $displayName"
    }

    return $tableInfo
}

Find Similar Tables

Search for tables with similar purposes but different names:

function Find-SimilarTables {
    param(
        [string]$Purpose,
        [array]$ExistingTables
    )

    $patterns = @{
        "category" = @("category", "categories", "type", "types", "classification")
        "product" = @("product", "products", "item", "items", "service", "services", "offering")
        "contact" = @("contact", "contacts", "submission", "inquiry", "lead", "leads")
        "team" = @("team", "employee", "staff", "member", "person", "people")
        "testimonial" = @("testimonial", "review", "feedback", "rating")
    }

    $searchTerms = $patterns[$Purpose]
    if (-not $searchTerms) { $searchTerms = @($Purpose) }

    $matches = $ExistingTables | Where-Object {
        $tableName = $_.SchemaName.ToLower()
        $displayName = $_.DisplayName.UserLocalizedLabel.Label.ToLower()

        foreach ($term in $searchTerms) {
            if ($tableName -match $term -or $displayName -match $term) {
                return $true
            }
        }
        return $false
    }

    return $matches
}

Compare Existing vs Required Tables

function Compare-TableSchemas {
    param(
        [hashtable]$RequiredTables,  # Purpose name -> array of required columns
        [array]$ExistingTables
    )

    $comparison = @{
        Reusable = @()
        Extendable = @()
        CreateNew = @()
    }

    foreach ($tablePurpose in $RequiredTables.Keys) {
        $existing = Find-SimilarTables -Purpose $tablePurpose -ExistingTables $ExistingTables | Select-Object -First 1

        if ($existing) {
            $tableSchema = Get-TableSchema -TableLogicalName $existing.LogicalName
            $existingColumns = $tableSchema.Attributes | Select-Object -ExpandProperty SchemaName
            $requiredColumns = $RequiredTables[$tablePurpose]
            $missingColumns = $requiredColumns | Where-Object { $_ -notin $existingColumns }

            if ($missingColumns.Count -eq 0) {
                $comparison.Reusable += @{
                    TablePurpose = $tablePurpose
                    ActualLogicalName = $existing.LogicalName
                    ActualSchemaName = $existing.SchemaName
                    Message = "All required columns present"
                }
            } else {
                $comparison.Extendable += @{
                    TablePurpose = $tablePurpose
                    ActualLogicalName = $existing.LogicalName
                    ActualSchemaName = $existing.SchemaName
                    MissingColumns = $missingColumns
                    Message = "Missing columns: $($missingColumns -join ', ')"
                }
            }
        } else {
            $comparison.CreateNew += @{
                TablePurpose = $tablePurpose
                NewSchemaName = "${publisherPrefix}_$tablePurpose"
                NewLogicalName = "${publisherPrefix}_$tablePurpose".ToLower()
                RequiredColumns = $RequiredTables[$tablePurpose]
            }
        }
    }

    return $comparison
}

Get Entity Set Name

Dataverse entity set names don't follow simple pluralization rules (e.g., account -> accounts, opportunity -> opportunities). Always query the actual name from API metadata:

function Get-EntitySetName {
    param(
        [string]$TableLogicalName,
        [string]$BaseUrl,
        [hashtable]$Headers
    )
    $entityDef = Invoke-RestMethod -Uri "$BaseUrl/EntityDefinitions(LogicalName='$TableLogicalName')?`$select=EntitySetName" -Headers $Headers
    return $entityDef.EntitySetName
}

Build Table Name Mapping

After comparing tables and getting user decisions, build a mapping that tracks actual logical names. Critical for correctly referencing tables throughout the workflow.

function Build-TableNameMapping {
    param(
        [object]$ComparisonResult,
        [string]$PublisherPrefix,
        [string]$BaseUrl,
        [hashtable]$Headers
    )

    $tableMapping = @{}

    foreach ($table in $ComparisonResult.Reusable) {
        $entitySetName = Get-EntitySetName -TableLogicalName $table.ActualLogicalName -BaseUrl $BaseUrl -Headers $Headers
        $tableMapping[$table.TablePurpose] = @{
            LogicalName = $table.ActualLogicalName
            SchemaName = $table.ActualSchemaName
            EntitySetName = $entitySetName
            Source = "Reused"
        }
    }

    foreach ($table in $ComparisonResult.Extendable) {
        $entitySetName = Get-EntitySetName -TableLogicalName $table.ActualLogicalName -BaseUrl $BaseUrl -Headers $Headers
        $tableMapping[$table.TablePurpose] = @{
            LogicalName = $table.ActualLogicalName
            SchemaName = $table.ActualSchemaName
            EntitySetName = $entitySetName
            Source = "Extended"
        }
    }

    foreach ($table in $ComparisonResult.CreateNew) {
        $logicalName = "${PublisherPrefix}_$($table.TablePurpose)".ToLower()
        $schemaName = "${PublisherPrefix}_$($table.TablePurpose)"
        # EntitySetName is auto-generated by Dataverse on table creation
        # Query it after creating the table in Step 4
        $tableMapping[$table.TablePurpose] = @{
            LogicalName = $logicalName
            SchemaName = $schemaName
            EntitySetName = $null
            Source = "Created"
        }
    }

    return $tableMapping
}

After creating new tables (Step 4), backfill their entity set names:

foreach ($purpose in ($tableMap.Keys | Where-Object { $tableMap[$_].Source -eq "Created" })) {
    $tableMap[$purpose].EntitySetName = Get-EntitySetName -TableLogicalName $tableMap[$purpose].LogicalName -BaseUrl $baseUrl -Headers $headers
}

Always use `$tableMap` to get correct table names:

• For relationships: $tableMap["product"].LogicalName
• For data queries: $tableMap["category"].EntitySetName

Check If Table/Column Exists

function Test-TableExists {
    param([string]$TableLogicalName)
    try {
        Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions(LogicalName='$TableLogicalName')?`$select=LogicalName" -Headers $headers -ErrorAction Stop
        return $true
    } catch {
        if ($_.Exception.Response.StatusCode -eq 404) { return $false }
        throw
    }
}

function Test-ColumnExists {
    param([string]$TableLogicalName, [string]$ColumnLogicalName)
    try {
        Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions(LogicalName='$TableLogicalName')/Attributes(LogicalName='$ColumnLogicalName')?`$select=LogicalName" -Headers $headers -ErrorAction Stop
        return $true
    } catch {
        if ($_.Exception.Response.StatusCode -eq 404) { return $false }
        throw
    }
}

Create Table

Use `$publisherPrefix` (from Initialize-DataverseApi) for all schema names. Never hardcode prefixes.

function New-DataverseTable {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$PluralDisplayName,
        [string]$Description = "",
        [string]$PrimaryColumnName = "${publisherPrefix}_name",
        [string]$PrimaryColumnDisplayName = "Name"
    )

    $tableDefinition = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.EntityMetadata"
        "SchemaName" = $SchemaName
        "DisplayName" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.Label"
            "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $DisplayName; "LanguageCode" = 1033 })
        }
        "DisplayCollectionName" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.Label"
            "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $PluralDisplayName; "LanguageCode" = 1033 })
        }
        "Description" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.Label"
            "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $Description; "LanguageCode" = 1033 })
        }
        "OwnershipType" = "UserOwned"
        "HasNotes" = $false
        "HasActivities" = $false
        "PrimaryNameAttribute" = $PrimaryColumnName
        "Attributes" = @(
            @{
                "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"
                "SchemaName" = $PrimaryColumnName
                "AttributeType" = "String"
                "FormatName" = @{ "Value" = "Text" }
                "MaxLength" = 100
                "DisplayName" = @{
                    "@odata.type" = "Microsoft.Dynamics.CRM.Label"
                    "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $PrimaryColumnDisplayName; "LanguageCode" = 1033 })
                }
                "IsPrimaryName" = $true
            }
        )
    }

    $body = $tableDefinition | ConvertTo-Json -Depth 10
    Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions" -Method Post -Headers $headers -Body $body
}

function New-DataverseTableIfNotExists {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$PluralDisplayName,
        [string]$Description = "",
        [string]$PrimaryColumnName = "${publisherPrefix}_name",
        [string]$PrimaryColumnDisplayName = "Name"
    )

    $logicalName = $SchemaName.ToLower()
    if (Test-TableExists -TableLogicalName $logicalName) {
        Write-Host "  [SKIP] Table '$SchemaName' already exists" -ForegroundColor Yellow
        return @{ Skipped = $true }
    }

    Write-Host "  [CREATE] Creating table '$SchemaName'..." -ForegroundColor Cyan
    $result = New-DataverseTable -SchemaName $SchemaName -DisplayName $DisplayName `
        -PluralDisplayName $PluralDisplayName -Description $Description `
        -PrimaryColumnName $PrimaryColumnName -PrimaryColumnDisplayName $PrimaryColumnDisplayName
    Write-Host "  [OK] Table '$SchemaName' created" -ForegroundColor Green
    return @{ Skipped = $false; Result = $result }
}

Add Columns

function Add-DataverseColumn {
    param(
        [string]$TableName,
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Type,  # String, Email, Url, Memo, Integer, Money, DateTime, Boolean
        [int]$MaxLength = 100
    )

    $columnTypes = @{
        "String" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"; "AttributeType" = "String"; "FormatName" = @{ "Value" = "Text" }; "MaxLength" = $MaxLength }
        "Email" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"; "AttributeType" = "String"; "FormatName" = @{ "Value" = "Email" }; "MaxLength" = $MaxLength }
        "Url" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"; "AttributeType" = "String"; "FormatName" = @{ "Value" = "Url" }; "MaxLength" = 200 }
        "Memo" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.MemoAttributeMetadata"; "AttributeType" = "Memo"; "MaxLength" = $MaxLength }
        "Integer" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.IntegerAttributeMetadata"; "AttributeType" = "Integer"; "MinValue" = -2147483648; "MaxValue" = 2147483647 }
        "Money" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.MoneyAttributeMetadata"; "AttributeType" = "Money"; "PrecisionSource" = 2 }
        "DateTime" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.DateTimeAttributeMetadata"; "AttributeType" = "DateTime"; "Format" = "DateAndTime" }
        "Boolean" = @{ "@odata.type" = "Microsoft.Dynamics.CRM.BooleanAttributeMetadata"; "AttributeType" = "Boolean" }
    }

    $column = $columnTypes[$Type].Clone()
    $column["SchemaName"] = $SchemaName
    $column["DisplayName"] = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.Label"
        "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $DisplayName; "LanguageCode" = 1033 })
    }

    Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions(LogicalName='$TableName')/Attributes" -Method Post -Headers $headers -Body ($column | ConvertTo-Json -Depth 10)
}

function Add-DataverseColumnIfNotExists {
    param(
        [string]$TableName,
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Type,
        [int]$MaxLength = 100
    )

    if (Test-ColumnExists -TableLogicalName $TableName.ToLower() -ColumnLogicalName $SchemaName.ToLower()) {
        Write-Host "    [SKIP] Column '$SchemaName' already exists on '$TableName'" -ForegroundColor Yellow
        return @{ Skipped = $true }
    }

    Write-Host "    [CREATE] Adding column '$SchemaName' to '$TableName'..." -ForegroundColor Cyan
    Add-DataverseColumn -TableName $TableName -SchemaName $SchemaName -DisplayName $DisplayName -Type $Type -MaxLength $MaxLength
    Write-Host "    [OK] Column '$SchemaName' added" -ForegroundColor Green
}

Add Choice/Picklist Column

function Add-DataversePicklist {
    param(
        [string]$TableName,
        [string]$SchemaName,
        [string]$DisplayName,
        [hashtable[]]$Options  # Array of @{ Value = 1; Label = "Option 1" }
    )

    $optionMetadata = $Options | ForEach-Object {
        @{
            "Value" = $_.Value
            "Label" = @{
                "@odata.type" = "Microsoft.Dynamics.CRM.Label"
                "LocalizedLabels" = @(@{
                    "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"
                    "Label" = $_.Label
                    "LanguageCode" = 1033
                })
            }
        }
    }

    $column = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.PicklistAttributeMetadata"
        "SchemaName" = $SchemaName
        "AttributeType" = "Picklist"
        "DisplayName" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.Label"
            "LocalizedLabels" = @(@{ "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"; "Label" = $DisplayName; "LanguageCode" = 1033 })
        }
        "OptionSet" = @{
            "@odata.type" = "Microsoft.Dynamics.CRM.OptionSetMetadata"
            "IsGlobal" = $false
            "OptionSetType" = "Picklist"
            "Options" = $optionMetadata
        }
    }

    Invoke-RestMethod -Uri "$baseUrl/EntityDefinitions(LogicalName='$TableName')/Attributes" -Method Post -Headers $headers -Body ($column | ConvertTo-Json -Depth 10)
}