{"kind":"AgentDefinition","metadata":{"namespace":"community","name":"dataverse-python-advanced-features","version":"0.1.0"},"spec":{"agents_md":"# Dataverse SDK for Python - Advanced Features Guide\n\n## Overview\nComprehensive guide to advanced Dataverse SDK features including enums, complex filtering, SQL queries, metadata operations, and production patterns. Based on official Microsoft walkthrough examples.\n\n## 1. Working with Option Sets \u0026 Picklists\n\n### Using IntEnum for Type Safety\n```python\nfrom enum import IntEnum\nfrom PowerPlatform.Dataverse.client import DataverseClient\n\n# Define enum for picklist\nclass Priority(IntEnum):\n    LOW = 1\n    MEDIUM = 2\n    HIGH = 3\n\nclass Priority(IntEnum):\n    COLD = 1\n    WARM = 2\n    HOT = 3\n\n# Create record with enum value\nrecord_data = {\n    \"new_title\": \"Important Task\",\n    \"new_priority\": Priority.HIGH,  # Automatically converted to int\n}\n\nids = client.create(\"new_tasktable\", record_data)\n```\n\n### Handling Formatted Values\n```python\n# When retrieving records, picklist values are returned as integers\nrecord = client.get(\"new_tasktable\", record_id)\n\npriority_int = record.get(\"new_priority\")  # Returns: 3\npriority_formatted = record.get(\"new_priority@OData.Community.Display.V1.FormattedValue\")  # Returns: \"High\"\n\nprint(f\"Priority (Raw): {priority_int}\")\nprint(f\"Priority (Formatted): {priority_formatted}\")\n```\n\n### Creating Tables with Enum Columns\n```python\nfrom enum import IntEnum\n\nclass TaskStatus(IntEnum):\n    NOT_STARTED = 0\n    IN_PROGRESS = 1\n    COMPLETED = 2\n\nclass TaskPriority(IntEnum):\n    LOW = 1\n    MEDIUM = 2\n    HIGH = 3\n\n# Pass enum classes as column types\ncolumns = {\n    \"new_Title\": \"string\",\n    \"new_Description\": \"string\",\n    \"new_Status\": TaskStatus,      # Creates option set column\n    \"new_Priority\": TaskPriority,  # Creates option set column\n    \"new_Amount\": \"decimal\",\n    \"new_DueDate\": \"datetime\"\n}\n\ntable_info = client.create_table(\n    \"new_TaskManagement\",\n    primary_column_schema_name=\"new_Title\",\n    columns=columns\n)\n\nprint(f\"Created table with {len(columns)} columns including enums\")\n```\n\n---\n\n## 2. Advanced Filtering \u0026 Querying\n\n### Complex OData Filters\n```python\n# Simple equality\nfilter1 = \"name eq 'Contoso'\"\n\n# Comparison operators\nfilter2 = \"creditlimit gt 50000\"\nfilter3 = \"createdon lt 2024-01-01\"\n\n# String operations\nfilter4 = \"contains(name, 'Ltd')\"\nfilter5 = \"startswith(name, 'Con')\"\nfilter6 = \"endswith(name, 'Ltd')\"\n\n# Multiple conditions with AND\nfilter7 = \"(name eq 'Contoso') and (creditlimit gt 50000)\"\n\n# Multiple conditions with OR\nfilter8 = \"(industrycode eq 1) or (industrycode eq 2)\"\n\n# Negation\nfilter9 = \"not(statecode eq 1)\"\n\n# Complex nested conditions\nfilter10 = \"(creditlimit gt 50000) and ((industrycode eq 1) or (industrycode eq 2))\"\n\n# Using in get() calls\nresults = client.get(\"account\", filter=filter10, select=[\"name\", \"creditlimit\"])\n```\n\n### Retrieve with Related Records (Expand)\n```python\n# Expand parent account information\naccounts = client.get(\n    \"account\",\n    filter=\"creditlimit gt 100000\",\n    expand=[\"parentaccountid($select=name,creditlimit)\"],\n    select=[\"accountid\", \"name\", \"creditlimit\", \"parentaccountid\"]\n)\n\nfor page in accounts:\n    for account in page:\n        parent_name = account.get(\"_parentaccountid_value\")\n        print(f\"Account: {account['name']}, Parent: {parent_name}\")\n```\n\n### SQL Queries for Complex Analysis\n```python\n# SQL queries are read-only but powerful for analytics\nsql = \"\"\"\nSELECT \n    a.name as AccountName,\n    a.creditlimit,\n    COUNT(c.contactid) as ContactCount\nFROM account a\nLEFT JOIN contact c ON a.accountid = c.parentcustomerid\nWHERE a.creditlimit \u003e 50000\nGROUP BY a.accountid, a.name, a.creditlimit\nORDER BY ContactCount DESC\n\"\"\"\n\nresults = client.query_sql(sql)\nfor row in results:\n    print(f\"{row['AccountName']}: {row['ContactCount']} contacts\")\n```\n\n### Paging with SQL Queries\n```python\n# SQL queries return paginated results by default\nsql = \"SELECT TOP 10000 name, creditlimit FROM account ORDER BY name\"\n\nall_results = []\nfor page in client.query_sql(sql):\n    all_results.extend(page)\n    print(f\"Retrieved {len(page)} rows\")\n\nprint(f\"Total: {len(all_results)} rows\")\n```\n\n---\n\n## 3. Metadata Operations\n\n### Creating Complex Tables\n```python\nfrom enum import IntEnum\nfrom datetime import datetime\n\nclass TaskStatus(IntEnum):\n    NEW = 1\n    OPEN = 2\n    CLOSED = 3\n\n# Create table with diverse column types\ncolumns = {\n    \"new_Subject\": \"string\",\n    \"new_Description\": \"string\",\n    \"new_Category\": \"string\",\n    \"new_Priority\": \"int\",\n    \"new_Status\": TaskStatus,\n    \"new_EstimatedHours\": \"decimal\",\n    \"new_DueDate\": \"datetime\",\n    \"new_IsOverdue\": \"bool\",\n    \"new_Notes\": \"string\"\n}\n\ntable_info = client.create_table(\n    \"new_WorkItem\",\n    primary_column_schema_name=\"new_Subject\",\n    columns=columns\n)\n\nprint(f\"✓ Created table: {table_info['table_schema_name']}\")\nprint(f\"  Primary Key: {table_info['primary_id_attribute']}\")\nprint(f\"  Columns: {', '.join(table_info.get('columns_created', []))}\")\n```\n\n### Inspecting Table Metadata\n```python\n# Get detailed table information\ntable_info = client.get_table_info(\"account\")\n\nprint(f\"Schema Name: {table_info.get('table_schema_name')}\")\nprint(f\"Logical Name: {table_info.get('table_logical_name')}\")\nprint(f\"Display Name: {table_info.get('table_display_name')}\")\nprint(f\"Entity Set: {table_info.get('entity_set_name')}\")\nprint(f\"Primary ID: {table_info.get('primary_id_attribute')}\")\nprint(f\"Primary Name: {table_info.get('primary_name_attribute')}\")\n```\n\n### Listing All Tables in Organization\n```python\n# Retrieve all tables (may be large result set)\nall_tables = []\nfor page in client.list_tables():\n    all_tables.extend(page)\n    print(f\"Retrieved {len(page)} tables in this page\")\n\nprint(f\"\\nTotal tables: {len(all_tables)}\")\n\n# Filter for custom tables\ncustom_tables = [t for t in all_tables if t['table_schema_name'].startswith('new_')]\nprint(f\"Custom tables: {len(custom_tables)}\")\nfor table in custom_tables[:5]:\n    print(f\"  - {table['table_schema_name']}\")\n```\n\n### Managing Columns Dynamically\n```python\n# Add columns to existing table\nclient.create_columns(\"new_TaskTable\", {\n    \"new_Department\": \"string\",\n    \"new_Budget\": \"decimal\",\n    \"new_ApprovedDate\": \"datetime\"\n})\n\n# Delete specific columns\nclient.delete_columns(\"new_TaskTable\", [\n    \"new_OldField1\",\n    \"new_OldField2\"\n])\n\n# Delete entire table\nclient.delete_table(\"new_TaskTable\")\n```\n\n---\n\n## 4. Single vs. Multiple Record Operations\n\n### Single Record Operations\n```python\n# Create single\nrecord_id = client.create(\"account\", {\"name\": \"Contoso\"})[0]\n\n# Get single by ID\naccount = client.get(\"account\", record_id)\n\n# Update single\nclient.update(\"account\", record_id, {\"creditlimit\": 100000})\n\n# Delete single\nclient.delete(\"account\", record_id)\n```\n\n### Multiple Record Operations\n\n#### Create Multiple Records\n```python\n# Create list of records\nrecords = [\n    {\"name\": \"Company A\", \"creditlimit\": 50000},\n    {\"name\": \"Company B\", \"creditlimit\": 75000},\n    {\"name\": \"Company C\", \"creditlimit\": 100000},\n]\n\ncreated_ids = client.create(\"account\", records)\nprint(f\"Created {len(created_ids)} records: {created_ids}\")\n```\n\n#### Update Multiple Records (Broadcast)\n```python\n# Apply same update to multiple records\naccount_ids = [\"id1\", \"id2\", \"id3\"]\nclient.update(\"account\", account_ids, {\n    \"industrycode\": 1,  # Retail\n    \"accountmanagerid\": \"manager-guid\"\n})\nprint(f\"Updated {len(account_ids)} records with same data\")\n```\n\n#### Delete Multiple Records\n```python\n# Delete multiple records with optimized bulk delete\nrecord_ids = [\"id1\", \"id2\", \"id3\", \"id4\", \"id5\"]\nclient.delete(\"account\", record_ids, use_bulk_delete=True)\nprint(f\"Deleted {len(record_ids)} records\")\n```\n\n---\n\n## 5. Data Manipulation Patterns\n\n### Retrieve, Modify, Update Pattern\n```python\n# Retrieve single record\naccount = client.get(\"account\", record_id)\n\n# Modify locally\noriginal_amount = account.get(\"creditlimit\", 0)\nnew_amount = original_amount + 10000\n\n# Update back\nclient.update(\"account\", record_id, {\"creditlimit\": new_amount})\nprint(f\"Updated creditlimit: {original_amount} → {new_amount}\")\n```\n\n### Batch Processing Pattern\n```python\n# Retrieve in batches with paging\nbatch_size = 100\nprocessed = 0\n\nfor page in client.get(\"account\", top=batch_size, filter=\"statecode eq 0\"):\n    # Process each page\n    batch_updates = []\n    for account in page:\n        if account.get(\"creditlimit\", 0) \u003e 100000:\n            batch_updates.append({\n                \"id\": account['accountid'],\n                \"accountmanagerid\": \"senior-manager-guid\"\n            })\n    \n    # Batch update\n    for update in batch_updates:\n        client.update(\"account\", update['id'], {\"accountmanagerid\": update['accountmanagerid']})\n        processed += 1\n\nprint(f\"Processed {processed} accounts\")\n```\n\n### Conditional Operations Pattern\n```python\nfrom PowerPlatform.Dataverse.core.errors import DataverseError\n\ndef safe_update(table, record_id, data, check_field=None, check_value=None):\n    \"\"\"Update with pre-condition check.\"\"\"\n    try:\n        if check_field and check_value:\n            # Verify condition before updating\n            record = client.get(table, record_id, select=[check_field])\n            if record.get(check_field) != check_value:\n                print(f\"Condition not met: {check_field} != {check_value}\")\n                return False\n        \n        client.update(table, record_id, data)\n        return True\n    except DataverseError as e:\n        print(f\"Update failed: {e}\")\n        return False\n\n# Usage\nsafe_update(\"account\", account_id, {\"creditlimit\": 100000}, \"statecode\", 0)\n```\n\n---\n\n## 6. Formatted Values \u0026 Display\n\n### Retrieving Formatted Values\n```python\n# When you retrieve a record with option set or money fields,\n# you can request formatted values for display\n\nrecord = client.get(\n    \"account\",\n    record_id,\n    select=[\"name\", \"creditlimit\", \"industrycode\"]\n)\n\n# Raw values\nname = record.get(\"name\")  # \"Contoso Ltd\"\nlimit = record.get(\"creditlimit\")  # 100000.00\nindustry = record.get(\"industrycode\")  # 1\n\n# Formatted values (returned in OData response)\nlimit_formatted = record.get(\"creditlimit@OData.Community.Display.V1.FormattedValue\")\nindustry_formatted = record.get(\"industrycode@OData.Community.Display.V1.FormattedValue\")\n\nprint(f\"Name: {name}\")\nprint(f\"Credit Limit: {limit_formatted or limit}\")  # \"100,000.00\" or 100000.00\nprint(f\"Industry: {industry_formatted or industry}\")  # \"Technology\" or 1\n```\n\n---\n\n## 7. Performance Optimization\n\n### Column Selection Strategy\n```python\n# ❌ Retrieve all columns (slow, uses more bandwidth)\naccount = client.get(\"account\", record_id)\n\n# ✅ Retrieve only needed columns (fast, efficient)\naccount = client.get(\n    \"account\",\n    record_id,\n    select=[\"accountid\", \"name\", \"creditlimit\", \"telephone1\"]\n)\n```\n\n### Filtering on Server\n```python\n# ❌ Retrieve all, filter locally (inefficient)\nall_accounts = []\nfor page in client.get(\"account\"):\n    all_accounts.extend(page)\nlarge_accounts = [a for a in all_accounts if a.get(\"creditlimit\", 0) \u003e 100000]\n\n# ✅ Filter on server, retrieve only matches (efficient)\nlarge_accounts = []\nfor page in client.get(\"account\", filter=\"creditlimit gt 100000\"):\n    large_accounts.extend(page)\n```\n\n### Paging Large Result Sets\n```python\n# ❌ Load all results at once (memory intensive)\nall_accounts = list(client.get(\"account\"))\n\n# ✅ Process in pages (memory efficient)\nprocessed = 0\nfor page in client.get(\"account\", top=1000):\n    for account in page:\n        process_account(account)\n        processed += 1\n    print(f\"Processed: {processed}\")\n```\n\n### Batch Operations\n```python\n# ❌ Individual creates in loop (slow)\nfor account_data in accounts:\n    client.create(\"account\", account_data)\n\n# ✅ Batch create (fast, optimized)\ncreated_ids = client.create(\"account\", accounts)\n```\n\n---\n\n## 8. Error Handling in Advanced Scenarios\n\n### Handling Metadata Errors\n```python\nfrom PowerPlatform.Dataverse.core.errors import MetadataError\n\ntry:\n    table_info = client.create_table(\"new_CustomTable\", {\"name\": \"string\"})\nexcept MetadataError as e:\n    print(f\"Metadata operation failed: {e}\")\n    # Handle table creation specific errors\n```\n\n### Handling Validation Errors\n```python\nfrom PowerPlatform.Dataverse.core.errors import ValidationError\n\ntry:\n    client.create(\"account\", {\"name\": None})  # Invalid: name required\nexcept ValidationError as e:\n    print(f\"Validation error: {e}\")\n    # Handle validation specific errors\n```\n\n### Handling HTTP Errors\n```python\nfrom PowerPlatform.Dataverse.core.errors import HttpError\n\ntry:\n    client.get(\"account\", \"invalid-guid\")\nexcept HttpError as e:\n    if \"404\" in str(e):\n        print(\"Record not found\")\n    elif \"403\" in str(e):\n        print(\"Access denied\")\n    else:\n        print(f\"HTTP error: {e}\")\n```\n\n### Handling SQL Errors\n```python\nfrom PowerPlatform.Dataverse.core.errors import SQLParseError\n\ntry:\n    results = client.query_sql(\"SELECT INVALID SYNTAX\")\nexcept SQLParseError as e:\n    print(f\"SQL parse error: {e}\")\n```\n\n---\n\n## 9. Working with Relationships\n\n### Creating Related Records\n```python\n# Create parent account\nparent_ids = client.create(\"account\", {\n    \"name\": \"Parent Company\",\n    \"creditlimit\": 500000\n})\nparent_id = parent_ids[0]\n\n# Create child accounts with parent reference\nchildren = [\n    {\"name\": \"Subsidiary A\", \"parentaccountid\": parent_id},\n    {\"name\": \"Subsidiary B\", \"parentaccountid\": parent_id},\n    {\"name\": \"Subsidiary C\", \"parentaccountid\": parent_id},\n]\nchild_ids = client.create(\"account\", children)\nprint(f\"Created {len(child_ids)} child accounts\")\n```\n\n### Querying Related Records\n```python\n# Get account with child accounts\naccount = client.get(\"account\", account_id)\n\n# Query child accounts\nchildren = client.get(\n    \"account\",\n    filter=f\"parentaccountid eq {account_id}\",\n    select=[\"accountid\", \"name\", \"creditlimit\"]\n)\n\nfor page in children:\n    for child in page:\n        print(f\"  - {child['name']}: ${child['creditlimit']}\")\n```\n\n---\n\n## 10. Cleanup \u0026 Housekeeping\n\n### Clearing SDK Cache\n```python\n# After bulk operations, clear metadata cache\nclient.flush_cache()\n\n# Useful after:\n# - Massive delete operations\n# - Table/column creation or deletion\n# - Metadata synchronization across environments\n```\n\n### Safe Table Deletion\n```python\nfrom PowerPlatform.Dataverse.core.errors import MetadataError\n\ndef delete_table_safe(table_name):\n    \"\"\"Delete table with error handling.\"\"\"\n    try:\n        # Verify table exists\n        table_info = client.get_table_info(table_name)\n        if not table_info:\n            print(f\"Table {table_name} not found\")\n            return False\n        \n        # Delete\n        client.delete_table(table_name)\n        print(f\"✓ Deleted table: {table_name}\")\n        \n        # Clear cache\n        client.flush_cache()\n        return True\n        \n    except MetadataError as e:\n        print(f\"❌ Failed to delete table: {e}\")\n        return False\n\ndelete_table_safe(\"new_TempTable\")\n```\n\n---\n\n## 11. Comprehensive Example: Full Workflow\n\n```python\nfrom enum import IntEnum\nfrom azure.identity import InteractiveBrowserCredential\nfrom PowerPlatform.Dataverse.client import DataverseClient\nfrom PowerPlatform.Dataverse.core.errors import DataverseError, MetadataError\n\nclass TaskStatus(IntEnum):\n    NEW = 1\n    IN_PROGRESS = 2\n    COMPLETED = 3\n\nclass TaskPriority(IntEnum):\n    LOW = 1\n    MEDIUM = 2\n    HIGH = 3\n\n# Setup\ncredential = InteractiveBrowserCredential()\nclient = DataverseClient(\"https://yourorg.crm.dynamics.com\", credential)\n\ntry:\n    # 1. Create table\n    print(\"Creating table...\")\n    table_info = client.create_table(\n        \"new_ProjectTask\",\n        primary_column_schema_name=\"new_Title\",\n        columns={\n            \"new_Description\": \"string\",\n            \"new_Status\": TaskStatus,\n            \"new_Priority\": TaskPriority,\n            \"new_DueDate\": \"datetime\",\n            \"new_EstimatedHours\": \"decimal\"\n        }\n    )\n    print(f\"✓ Created table: {table_info['table_schema_name']}\")\n    \n    # 2. Create records\n    print(\"\\nCreating tasks...\")\n    tasks = [\n        {\n            \"new_Title\": \"Design system\",\n            \"new_Description\": \"Create design system architecture\",\n            \"new_Status\": TaskStatus.NEW,\n            \"new_Priority\": TaskPriority.HIGH,\n            \"new_EstimatedHours\": 40.0\n        },\n        {\n            \"new_Title\": \"Implement UI\",\n            \"new_Description\": \"Build React components\",\n            \"new_Status\": TaskStatus.IN_PROGRESS,\n            \"new_Priority\": TaskPriority.HIGH,\n            \"new_EstimatedHours\": 80.0\n        },\n        {\n            \"new_Title\": \"Write tests\",\n            \"new_Description\": \"Unit and integration tests\",\n            \"new_Status\": TaskStatus.NEW,\n            \"new_Priority\": TaskPriority.MEDIUM,\n            \"new_EstimatedHours\": 30.0\n        }\n    ]\n    task_ids = client.create(\"new_ProjectTask\", tasks)\n    print(f\"✓ Created {len(task_ids)} tasks\")\n    \n    # 3. Query and filter\n    print(\"\\nQuerying high-priority tasks...\")\n    high_priority = client.get(\n        \"new_ProjectTask\",\n        filter=\"new_priority eq 3\",\n        select=[\"new_Title\", \"new_Priority\", \"new_EstimatedHours\"]\n    )\n    for page in high_priority:\n        for task in page:\n            print(f\"  - {task['new_title']}: {task['new_estimatedhours']} hours\")\n    \n    # 4. Update records\n    print(\"\\nUpdating task status...\")\n    client.update(\"new_ProjectTask\", task_ids[1], {\n        \"new_Status\": TaskStatus.COMPLETED,\n        \"new_EstimatedHours\": 85.5\n    })\n    print(\"✓ Updated task status\")\n    \n    # 5. Cleanup\n    print(\"\\nCleaning up...\")\n    client.delete_table(\"new_ProjectTask\")\n    print(\"✓ Deleted table\")\n    \n    # Clear cache\n    client.flush_cache()\n    \nexcept (MetadataError, DataverseError) as e:\n    print(f\"❌ Error: {e}\")\n```\n\n---\n\n## Reference\n- [Official Walkthrough Example](https://github.com/microsoft/PowerPlatform-DataverseClient-Python/blob/main/examples/advanced/walkthrough.py)\n- [OData Filter Syntax](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query-data-web-api)\n- [Table/Column Metadata](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/create-update-entity-definitions-using-web-api)\n","description":"Comprehensive guide to advanced Dataverse SDK features including enums, complex filtering, SQL queries, metadata operations, and production patterns. Based on official Microsoft walkthrough examples.","import":{"commit_sha":"541b7819d8c3545c6df122491af4fa1eae415779","imported_at":"2026-05-18T20:05:35Z","license_text":"MIT License\n\nCopyright GitHub, Inc.\n\nPermission is hereby granted, free of charge, to any person obtaining a copy\nof this software and associated documentation files (the \"Software\"), to deal\nin the Software without restriction, including without limitation the rights\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\ncopies of the Software, and to permit persons to whom the Software is\nfurnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all\ncopies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\nSOFTWARE.","owner":"github","repo":"github/awesome-copilot","source_url":"https://github.com/github/awesome-copilot/blob/541b7819d8c3545c6df122491af4fa1eae415779/instructions/dataverse-python-advanced-features.instructions.md"},"manifest":{}},"content_hash":[25,219,8,156,236,119,119,109,88,220,131,123,251,60,57,177,9,110,137,111,95,216,212,12,87,217,33,202,16,189,161,65],"trust_level":"unsigned","yanked":false}