---
applyTo: "CodeApps/**,PowerPagesSPA/**"
---

# Dataverse CRUD Operations with Power Platform Code Apps

This guide provides comprehensive instructions for implementing Create, Read, Update, and Delete (CRUD) operations with Microsoft Dataverse in Power Apps Code Apps and Power Pages SPAs.

---

## Table of Contents

1. [Overview](#1-overview)
2. [Generated Services](#2-generated-services)
3. [Critical Data Type Handling](#3-critical-data-type-handling)
4. [CRUD Operations](#4-crud-operations)
5. [Service Wrapper Pattern](#5-service-wrapper-pattern)
6. [Error Handling](#6-error-handling)
7. [Best Practices](#7-best-practices)
8. [Date and Time Handling](#8-date-and-time-handling)
9. [User Identity and SystemUsers](#9-user-identity-and-systemusers)
10. [Choice and Multi-Select Fields](#10-choice-and-multi-select-fields)
11. [Lookup Fields](#11-lookup-fields)
12. [Adding Custom Data Sources](#12-adding-custom-data-sources)
13. [Soft Delete vs Hard Delete](#13-soft-delete-vs-hard-delete)

---

## 1. Overview

### Architecture

Power Platform generates TypeScript services and models for Dataverse tables using the PAC CLI command:
```bash
pac code add-data-source -a dataverse -t tablename
```

This creates:
- `src/generated/models/TableNameModel.ts` - TypeScript type definitions
- `src/generated/services/TableNameService.ts` - CRUD service methods
- `src/.power/schemas/appschemas/dataSourcesInfo.ts` - Data source configuration

### Key Components

- **Generated Services**: Auto-generated CRUD methods for each Dataverse table
- **Type Models**: TypeScript interfaces matching Dataverse schema
- **Service Wrappers**: Custom service layer to adapt generated services to application models

---

## 2. Generated Services

### Service Structure

Each generated service provides these methods:

```typescript
export class TableNameService {
  // Create a new record
  static async create(record: Omit<TableNameBase, 'tableid'>): Promise<IOperationResult<TableName>>
  
  // Update an existing record
  static async update(id: string, changedFields: Partial<Omit<TableNameBase, 'tableid'>>): Promise<IOperationResult<TableName>>
  
  // Delete a record
  static async delete(id: string): Promise<void>
  
  // Get a single record by ID
  static async get(id: string, options?: IGetOptions): Promise<IOperationResult<TableName>>
  
  // Get multiple records with filtering
  static async getAll(options?: IGetAllOptions): Promise<IOperationResult<TableName[]>>
  
  // Get entity metadata
  static getMetadata(options?: GetEntityMetadataOptions): Promise<IOperationResult<Partial<EntityMetadata>>>
}
```

### Query Options

```typescript
// Get single record options
interface IGetOptions {
  select?: string[];  // Specify columns to retrieve
}

// Get multiple records options
interface IGetAllOptions {
  maxPageSize?: number;      // Maximum records per page
  select?: string[];         // Columns to retrieve
  filter?: string;           // OData filter expression
  orderBy?: string[];        // Sort order
  top?: number;              // Limit results
  skip?: number;             // Pagination offset
  skipToken?: string;        // Continuation token
}
```

### Example: Fetching Records

```typescript
// Get all active records, sorted by name
const result = await TableService.getAll({
  filter: 'statecode eq 0',
  orderBy: ['name asc'],
  select: ['tableid', 'name', 'createdon'],
  top: 100
});

if (result.data) {
  const records = result.data;
}
```

---

## 3. Critical Data Type Handling

### ⚠️ The Decimal Problem

**CRITICAL**: Generated TypeScript models define Dataverse `Decimal` fields as `string`, but Dataverse's OData API expects `number` values.

#### Generated Model (WRONG for runtime)
```typescript
export interface TableBase {
  tableid: string;
  name: string;
  amount: string;  // ❌ TypeScript says string, but Dataverse needs number
}
```

#### Runtime Behavior

When sending data to Dataverse:
- ✅ **CORRECT**: Send `{ amount: 25.50 }` (as number)
- ❌ **WRONG**: Send `{ amount: "25.50" }` (as string)

Sending a string will cause this error:
```
Error 0x80048d19: Cannot convert a value to target type 'Edm.Decimal' 
because of conflict between input format string/number and parameter 
'IEEE754Compatible' false/true.
```

### Solution: Type Casting

Use `as any` to bypass TypeScript's incorrect type checking:

```typescript
// ✅ CORRECT: Send number, cast to bypass TypeScript
const record = {
  name: 'Test',
  amount: 25.50 as any,  // TypeScript thinks string, but we send number
  statecode: 0
};

await TableService.create(record);
```

### Fields Affected

This applies to ALL Dataverse data types that are numeric but typed as string:
- `Decimal` → Send as `number`
- `Money` → Send as `number`
- `Float` → Send as `number`
- `Integer` → Send as `number` or `string` (both work, but number is preferred)
- `Choice` (OptionSet) → Send as `number` (the option value)

---

## 4. CRUD Operations

### 4.1 CREATE

Creating a new record in Dataverse.

#### Basic Create

```typescript
export const createRecord = async (data: CreateRecordData): Promise<Record | null> => {
  try {
    // Map application model to Dataverse model
    const dvData = {
      name: data.name,
      amount: data.amount as any,           // ✅ Number field
      category: data.category as any,       // ✅ Choice field
      statecode: 0,                         // Active state
    } as Omit<TableBase, 'tableid'>;
    
    const result = await TableService.create(dvData);
    
    if (result.data) {
      // Map back to application model
      return {
        id: result.data.tableid,
        name: result.data.name,
        amount: parseFloat(result.data.amount),  // Convert string to number
        category: parseInt(String(result.data.category), 10),
        createdon: new Date(),
        statecode: 0,
      };
    }
    
    return null;
  } catch (error) {
    throw error;
  }
};
```

#### Create with Lookup (Foreign Key)

Use `@odata.bind` syntax for lookups:

```typescript
export const createWithLookup = async (data: CreateData): Promise<Record | null> => {
  try {
    const dvData = {
      name: data.name,
      amount: data.amount as any,
      // Lookup to parent record
      'parentid@odata.bind': `/parenttable(${data.parentId})`,
      statecode: 0,
    } as Omit<TableBase, 'tableid'>;
    
    const result = await TableService.create(dvData);
    
    if (result.data) {
      return mapToAppModel(result.data);
    }
    
    return null;
  } catch (error) {
    throw error;
  }
};
```

### 4.2 READ

Reading records from Dataverse.

#### Get Single Record

```typescript
export const getRecordById = async (id: string): Promise<Record | null> => {
  try {
    const result = await TableService.get(id, {
      select: ['tableid', 'name', 'amount', 'createdon']
    });
    
    if (result.data) {
      return {
        id: result.data.tableid,
        name: result.data.name,
        amount: parseFloat(result.data.amount),
        createdon: result.data.createdon ? new Date(result.data.createdon) : undefined,
      };
    }
    
    return null;
  } catch (error) {
    return null;
  }
};
```

#### Get Multiple Records with Filter

```typescript
export const getAllActiveRecords = async (): Promise<Record[]> => {
  try {
    const result = await TableService.getAll({
      filter: 'statecode eq 0',
      orderBy: ['createdon desc'],
      select: ['tableid', 'name', 'amount'],
      top: 100
    });
    
    if (result.data) {
      return result.data.map((dv) => ({
        id: dv.tableid,
        name: dv.name,
        amount: parseFloat(dv.amount),
      }));
    }
    
    return [];
  } catch (error) {
    return [];
  }
};
```

#### Get Records by Lookup

```typescript
export const getRecordsByParent = async (parentId: string): Promise<Record[]> => {
  try {
    const result = await TableService.getAll({
      filter: `_parentid_value eq '${parentId}' and statecode eq 0`,
      orderBy: ['createdon desc'],
    });
    
    if (result.data) {
      return result.data.map(mapToAppModel);
    }
    
    return [];
  } catch (error) {
    return [];
  }
};
```

### 4.3 UPDATE

Updating existing records in Dataverse.

#### Basic Update

```typescript
export const updateRecord = async (
  id: string, 
  data: Partial<UpdateRecordData>
): Promise<boolean> => {
  try {
    // Only include fields that are being updated
    const changes: Partial<Omit<TableBase, 'tableid'>> = {};
    
    if (data.name !== undefined) changes.name = data.name;
    if (data.amount !== undefined) changes.amount = data.amount as any;
    if (data.category !== undefined) changes.category = data.category as any;
    
    const result = await TableService.update(id, changes);
    return result.success;
  } catch (error) {
    return false;
  }
};
```

#### Update with State Change

```typescript
export const deactivateRecord = async (id: string): Promise<boolean> => {
  try {
    const changes = {
      statecode: 1,     // Inactive
      statuscode: 2     // Inactive status reason
    };
    
    const result = await TableService.update(id, changes);
    return result.success;
  } catch (error) {
    return false;
  }
};
```

### 4.4 DELETE

Deleting records from Dataverse.

#### Hard Delete

```typescript
export const deleteRecord = async (id: string): Promise<boolean> => {
  try {
    await TableService.delete(id);
    return true;
  } catch (error) {
    return false;
  }
};
```

#### Soft Delete (Recommended)

Instead of hard deleting, deactivate the record:

```typescript
export const softDeleteRecord = async (id: string): Promise<boolean> => {
  try {
    const changes = {
      statecode: 1,  // Inactive
      statuscode: 2  // Inactive status reason
    };
    
    const result = await TableService.update(id, changes);
    return result.success;
  } catch (error) {
    return false;
  }
};
```

---

## 5. Service Wrapper Pattern

### Why Create Wrappers?

1. **Type Safety**: Convert between Dataverse models and application models
2. **Data Transformation**: Handle string-to-number conversions for Decimal fields
3. **Error Handling**: Centralize error management
4. **Abstraction**: Hide Dataverse-specific details from UI components

### Wrapper Structure

```typescript
// src/services/dataverse.service.ts
import { TableService } from '@/generated/services/TableService';
import type { TableBase } from '@/generated/models/TableModel';
import type { AppRecord, CreateAppRecord } from '@/models';

// ============================================================================
// CREATE
// ============================================================================

export const createAppRecord = async (data: CreateAppRecord): Promise<AppRecord | null> => {
  try {
    const dvData = {
      name: data.name,
      amount: data.amount as any,  // ✅ Handle Decimal
      statecode: 0,
    } as Omit<TableBase, 'tableid'>;
    
    const result = await TableService.create(dvData);
    
    if (result.data) {
      return mapDvToApp(result.data);
    }
    
    return null;
  } catch (error) {
    throw error;
  }
};

// ============================================================================
// READ
// ============================================================================

export const getAllAppRecords = async (): Promise<AppRecord[]> => {
  try {
    const result = await TableService.getAll({
      filter: 'statecode eq 0',
      orderBy: ['name'],
    });
    
    if (result.data) {
      return result.data.map(mapDvToApp);
    }
    
    return [];
  } catch (error) {
    throw error;
  }
};

// ============================================================================
// UPDATE
// ============================================================================

export const updateAppRecord = async (
  id: string, 
  data: Partial<CreateAppRecord>
): Promise<boolean> => {
  try {
    const changes: Partial<Omit<TableBase, 'tableid'>> = {};
    if (data.name !== undefined) changes.name = data.name;
    if (data.amount !== undefined) changes.amount = data.amount as any;
    
    const result = await TableService.update(id, changes);
    return result.success;
  } catch (error) {
    return false;
  }
};

// ============================================================================
// DELETE
// ============================================================================

export const deleteAppRecord = async (id: string): Promise<boolean> => {
  try {
    await TableService.delete(id);
    return true;
  } catch (error) {
    return false;
  }
};

// ============================================================================
// HELPER FUNCTIONS
// ============================================================================

const mapDvToApp = (dv: any): AppRecord => {
  return {
    id: dv.tableid,
    name: dv.name,
    amount: parseFloat(dv.amount),  // ✅ Convert string to number
    createdon: dv.createdon ? new Date(dv.createdon) : undefined,
    statecode: dv.statecode ? parseInt(String(dv.statecode), 10) : 0,
  };
};
```

---

## 6. Error Handling

### Operation Results

Generated services return `IOperationResult<T>`:

```typescript
interface IOperationResult<T> {
  success: boolean;
  data?: T;
  error?: any;
}
```

### Error Handling Pattern

```typescript
export const createRecord = async (data: CreateData): Promise<Record | null> => {
  try {
    const dvData = mapToDvModel(data);
    const result = await TableService.create(dvData);
    
    // Check if operation succeeded
    if (result.success && result.data) {
      return mapToAppModel(result.data);
    }
    
    // Operation succeeded but no data returned
    return null;
  } catch (error) {
    // Handle specific errors
    if (error instanceof Error) {
      // Log or rethrow for UI handling
      throw new Error(`Failed to create record: ${error.message}`);
    }
    throw error;
  }
};
```

### Common Dataverse Errors

| Error Code | Description | Solution |
|------------|-------------|----------|
| `0x80048d19` | Type conversion error (string to Decimal) | Send numeric values, not strings |
| `0x80040217` | Record not found | Check if record exists before update/delete |
| `0x80040237` | Duplicate detection | Handle duplicate key violations |
| `0x8004032b` | No write access | Check user permissions |

---

## 7. Best Practices

### 7.1 Type Safety

```typescript
// ✅ DO: Create strongly typed interfaces
interface CreateRecord {
  name: string;
  amount: number;      // Application uses number
  category: number;
}

interface DataverseRecord {
  tableid: string;
  name: string;
  amount: string;      // Dataverse model uses string
  category: string;
}

// ✅ DO: Use explicit mapping functions
const mapToDataverse = (app: CreateRecord): Omit<DataverseRecord, 'tableid'> => {
  return {
    name: app.name,
    amount: app.amount as any,      // Cast to bypass TypeScript
    category: app.category as any,
  };
};
```

### 7.2 Data Validation

```typescript
// ✅ DO: Validate before sending to Dataverse
export const createRecord = async (data: CreateData): Promise<Record | null> => {
  // Validate inputs
  if (!data.name?.trim()) {
    throw new Error('Name is required');
  }
  
  if (data.amount <= 0) {
    throw new Error('Amount must be positive');
  }
  
  // Proceed with creation
  const dvData = mapToDataverse(data);
  const result = await TableService.create(dvData);
  
  return result.data ? mapToApp(result.data) : null;
};
```

### 7.3 Filtering Best Practices

```typescript
// ✅ DO: Use parameterized filters for dynamic values
const filter = `statecode eq 0 and createdon ge ${startDate.toISOString()}`;

// ✅ DO: Escape single quotes in string values
const searchName = userInput.replace(/'/g, "''");
const filter = `contains(name, '${searchName}')`;

// ✅ DO: Use GUIDs without quotes for lookups
const filter = `_parentid_value eq '${parentId}'`;

// ❌ DON'T: Use JavaScript operators in OData filters
const filter = `amount > 100`;  // ❌ Use 'gt' not '>'
const filter = `amount gt 100`; // ✅ Correct
```

### 7.4 Performance Optimization

```typescript
// ✅ DO: Use select to retrieve only needed fields
const result = await TableService.getAll({
  select: ['tableid', 'name', 'amount'],  // Only get what you need
  filter: 'statecode eq 0',
  top: 100  // Limit results
});

// ❌ DON'T: Retrieve all fields if not needed
const result = await TableService.getAll({
  filter: 'statecode eq 0'  // Gets all columns
});
```

### 7.5 State Management

```typescript
// ✅ DO: Use soft delete (deactivate) instead of hard delete
export const deactivateRecord = async (id: string): Promise<boolean> => {
  const changes = {
    statecode: 1,
    statuscode: 2
  };
  return await updateRecord(id, changes);
};

// ✅ DO: Filter out inactive records in queries
const result = await TableService.getAll({
  filter: 'statecode eq 0'  // Only active records
});
```

### 7.6 React Integration

```typescript
// ✅ DO: Handle loading and error states in components
const [records, setRecords] = useState<Record[]>([]);
const [loading, setLoading] = useState(true);
const [error, setError] = useState<string | null>(null);

useEffect(() => {
  const loadRecords = async () => {
    try {
      setLoading(true);
      setError(null);
      const data = await getAllRecords();
      setRecords(data);
    } catch (err) {
      setError(err instanceof Error ? err.message : 'Unknown error');
    } finally {
      setLoading(false);
    }
  };
  
  loadRecords();
}, []);
```

### 7.7 Caching Considerations

```typescript
// ✅ DO: Implement local state updates for better UX
const handleCreate = async (data: CreateData) => {
  try {
    const newRecord = await createRecord(data);
    if (newRecord) {
      // Optimistic update - add to local state immediately
      setRecords([...records, newRecord]);
    }
  } catch (error) {
    // Handle error and revert if needed
    alert('Failed to create record');
  }
};
```

---

## 8. Date and Time Handling

### ⚠️ The UTC Conversion Problem

**CRITICAL**: Using `toISOString()` converts dates to UTC, which can shift the date by one day depending on the user's timezone.

#### The Problem

```typescript
// ❌ WRONG: toISOString() converts to UTC
const date = new Date('2026-02-19');  // Local: Feb 19
const filterDate = date.toISOString().split('T')[0];  // May become "2026-02-18" in UTC!

const result = await BookingService.getAll({
  filter: `date eq ${filterDate}`  // Shows Feb 18 bookings instead of Feb 19!
});
```

#### The Solution

Use local date components to construct date strings:

```typescript
// ✅ CORRECT: Use local date components
const formatLocalDate = (date: Date): string => {
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  return `${year}-${month}-${day}`;
};

const filterDate = formatLocalDate(new Date());  // Always local date

const result = await BookingService.getAll({
  filter: `date eq ${filterDate}`  // Correct local date
});
```

### Date Filtering Patterns

```typescript
// ✅ DO: Use local date formatting for date-only fields
const localDate = formatLocalDate(selectedDate);
const filter = `datefield eq ${localDate}`;

// ✅ DO: For datetime comparisons, be explicit about time
const startOfDay = `${formatLocalDate(date)}T00:00:00Z`;
const endOfDay = `${formatLocalDate(date)}T23:59:59Z`;
const filter = `createdon ge ${startOfDay} and createdon le ${endOfDay}`;

// ❌ DON'T: Use toISOString() for date-only comparisons
const filter = `datefield eq ${date.toISOString().split('T')[0]}`;  // May be wrong day!
```

---

## 9. User Identity and SystemUsers

### ⚠️ The User ID Mismatch Problem

**CRITICAL**: Azure AD `objectId` (from `usePower().user`) is NOT the same as Dataverse `systemuserid`. You must query the `systemusers` table to get the correct Dataverse user ID.

#### The Problem

```typescript
// ❌ WRONG: Azure AD objectId doesn't match Dataverse systemuserid
const { user } = usePower();
const azureObjectId = user?.objectId;  // e.g., "abc-123-def"

// This filter won't work - IDs don't match!
const result = await BookingService.getAll({
  filter: `_booked_by_value eq '${azureObjectId}'`  // No results!
});
```

#### The Solution

Query `systemusers` table by `domainname` to get the Dataverse `systemuserid`:

```typescript
// ✅ CORRECT: Query systemusers to get Dataverse user ID
import { SystemusersService } from '@/generated/services/SystemusersService';

export const getCurrentUser = async (userPrincipalName: string): Promise<CurrentUser | null> => {
  try {
    // Query systemusers by domainname (which matches Azure AD userPrincipalName)
    const result = await SystemusersService.getAll({
      filter: `domainname eq '${userPrincipalName}'`,
      select: ['systemuserid', 'fullname', 'firstname', 'lastname', 'domainname'],
      top: 1
    });

    if (result.data && result.data.length > 0) {
      const dvUser = result.data[0];
      return {
        id: dvUser.systemuserid,           // ✅ This is the Dataverse user ID
        fullName: dvUser.fullname ?? '',
        firstName: dvUser.firstname ?? '',
        lastName: dvUser.lastname ?? '',
        email: dvUser.domainname ?? '',
      };
    }
    return null;
  } catch (error) {
    console.error('[UserService] Error getting current user:', error);
    return null;
  }
};
```

### Using the Dataverse User ID

```typescript
// In your component or service
const { user } = usePower();

useEffect(() => {
  const loadUserData = async () => {
    if (user?.userPrincipalName) {
      // Get Dataverse user ID
      const dvUser = await getCurrentUser(user.userPrincipalName);
      
      if (dvUser) {
        // Now use the correct ID for filtering
        const bookings = await BookingService.getAll({
          filter: `_booked_by_value eq '${dvUser.id}'`  // ✅ Correct ID!
        });
      }
    }
  };
  loadUserData();
}, [user]);
```

### Getting User Display Names

For lookup fields pointing to users, you may need to fetch display names:

```typescript
// Batch fetch user names for multiple user IDs
export const getUserFullNames = async (userIds: string[]): Promise<Map<string, string>> => {
  const nameMap = new Map<string, string>();
  
  if (userIds.length === 0) return nameMap;
  
  // Remove duplicates
  const uniqueIds = [...new Set(userIds)];
  
  // Build filter for multiple IDs
  const idFilters = uniqueIds.map(id => `systemuserid eq '${id}'`).join(' or ');
  
  try {
    const result = await SystemusersService.getAll({
      filter: idFilters,
      select: ['systemuserid', 'fullname']
    });
    
    if (result.data) {
      result.data.forEach(user => {
        nameMap.set(user.systemuserid, user.fullname ?? 'Unknown');
      });
    }
  } catch (error) {
    console.error('[UserService] Error fetching user names:', error);
  }
  
  return nameMap;
};
```

---

## 10. Choice and Multi-Select Fields

### Single Choice Fields

Choice fields are stored as numeric values in Dataverse:

```typescript
// ✅ Reading a choice field
const floor = dvRecord.floor as number;  // e.g., 0, 1, 2

// ✅ Writing a choice field
const dvData = {
  floor: Floor.GroundFloor as any,  // Send numeric value
};

// ✅ Define enum to match Dataverse choice values
enum Floor {
  GroundFloor = 0,
  FirstFloor = 1,
  SecondFloor = 2
}
```

### ⚠️ Multi-Select Choice Fields

**CRITICAL**: Multi-select choice fields are returned as comma-separated strings, NOT arrays.

#### The Problem

```typescript
// Multi-select comes as: "0,1,2" not [0, 1, 2]
console.log(dvRecord.workspacesetup);  // "0,1,2,3"
```

#### The Solution

Parse the comma-separated string into an array of numbers:

```typescript
// ✅ CORRECT: Parse multi-select choice values
const parseMultiSelectChoice = <T extends number>(
  value: string | number | null | undefined,
  validValues: T[]
): T[] => {
  const result: T[] = [];
  
  if (value === undefined || value === null) return result;
  
  // Convert to string and split by comma
  const valueStr = String(value);
  const values = valueStr.split(',')
    .map(v => parseInt(v.trim(), 10))
    .filter(v => !isNaN(v));
  
  // Filter to only valid enum values
  values.forEach(val => {
    if (validValues.includes(val as T)) {
      result.push(val as T);
    }
  });
  
  return result;
};

// Usage example
enum WorkspaceSetup {
  DualMonitors = 0,
  USBCDock = 1,
  ErgonomicChair = 2,
  StandingDesk = 3
}

const validSetupValues = [
  WorkspaceSetup.DualMonitors,
  WorkspaceSetup.USBCDock,
  WorkspaceSetup.ErgonomicChair,
  WorkspaceSetup.StandingDesk
];

// Map Dataverse record to app model
const mapDvToDesk = (dvDesk: DvDesk): Desk => {
  const amenities = parseMultiSelectChoice(
    dvDesk.devlpe_workspacesetup,
    validSetupValues
  );
  
  return {
    id: dvDesk.deskid,
    name: dvDesk.name,
    amenities,  // Now it's a proper array: [0, 1, 2]
  };
};
```

### Displaying Multi-Select Values

```typescript
// Map numeric values to display labels
const getWorkspaceSetupLabel = (setup: WorkspaceSetup): string => {
  switch (setup) {
    case WorkspaceSetup.DualMonitors: return 'Dual Monitors';
    case WorkspaceSetup.USBCDock: return 'USB-C Dock';
    case WorkspaceSetup.ErgonomicChair: return 'Ergonomic Chair';
    case WorkspaceSetup.StandingDesk: return 'Standing Desk';
    default: return 'Unknown';
  }
};

// In component
<div>
  {desk.amenities.map(a => (
    <Badge key={a}>{getWorkspaceSetupLabel(a)}</Badge>
  ))}
</div>
```

---

## 11. Lookup Fields

### Reading Lookup Values

Lookup fields in Dataverse are returned with special naming conventions:

```typescript
// The lookup field name in Dataverse: "booked_by" (referencing systemusers)
// In API response:
{
  "_booked_by_value": "guid-of-related-record",               // The GUID
  "_booked_by_value@OData.Community.Display.V1.FormattedValue": "John Doe"  // Display name
}

// ✅ Access the GUID
const userId = dvRecord._booked_by_value;

// ✅ Access the display name (if available)
const userName = (dvRecord as any)['_booked_by_value@OData.Community.Display.V1.FormattedValue'];
```

### Writing Lookup Values

Use `@odata.bind` syntax when creating or updating records with lookups:

```typescript
// ✅ CORRECT: Use @odata.bind for lookups
const createBooking = async (deskId: string, userId: string): Promise<Booking | null> => {
  const dvData = {
    'desk@odata.bind': `/devlpe_cddesks(${deskId})`,      // Lookup to desk table
    'booked_by@odata.bind': `/systemusers(${userId})`,    // Lookup to users table
    date: formatLocalDate(new Date()),
    statecode: 0,
  } as any;
  
  const result = await BookingService.create(dvData);
  return result.data ? mapToApp(result.data) : null;
};
```

### Filtering by Lookup

```typescript
// Filter by lookup field value (use _fieldname_value format)
const result = await BookingService.getAll({
  filter: `_booked_by_value eq '${userId}' and _desk_value eq '${deskId}'`
});
```

---

## 12. Adding Custom Data Sources

### When to Add Data Sources

You may need to query system tables like `systemusers` that aren't automatically included. Add them via:

1. **PAC CLI** (creates generated files):
   ```bash
   pac code add-data-source -a dataverse -t systemusers
   ```

2. **Manual Configuration** (if PAC CLI doesn't support the table):

#### Step 1: Update power.config.json

```json
{
  "dataSources": [
    {
      "name": "systemusers",
      "type": "dataverse",
      "configuration": {
        "table": "systemusers"
      }
    }
  ]
}
```

#### Step 2: Update dataSourcesInfo.ts

```typescript
// src/.power/schemas/appschemas/dataSourcesInfo.ts

export const dataSourcesInfo: DataSourceInfo[] = [
  // ... existing data sources
  {
    module: "DataverseTables",
    name: "systemusers",
    datasetType: "table",
    tableName: "systemusers",
    relationship: []
  }
];
```

#### Step 3: Create Service (if no generated service)

```typescript
// src/generated/services/SystemusersService.ts
import { usePower } from '@/PowerProvider';

// Use dynamic approach if generated service not available
export const querySystemUsers = async (filter: string): Promise<any[]> => {
  // Implementation depends on available APIs
};
```

---

## 13. Soft Delete vs Hard Delete

### Understanding State Codes

Dataverse records have `statecode` (State) and `statuscode` (Status Reason):

| statecode | Meaning |
|-----------|---------|
| 0 | Active |
| 1 | Inactive |

### Soft Delete (Recommended Default)

Deactivate records instead of deleting:

```typescript
export const softDeleteRecord = async (id: string): Promise<boolean> => {
  try {
    const changes = {
      statecode: 1,   // Inactive
      statuscode: 2   // Inactive status
    };
    const result = await TableService.update(id, changes);
    return result.success;
  } catch (error) {
    return false;
  }
};

// Query only active records
const result = await TableService.getAll({
  filter: 'statecode eq 0'
});
```

### ⚠️ When Soft Delete Causes Problems

**CRITICAL**: Soft delete can cause issues with unique constraints or business logic that checks for existing records.

#### The Problem

```typescript
// Scenario: User books desk A for Feb 20, then cancels (soft delete)
// Later: User tries to book desk A for Feb 20 again
// Issue: Query finds the inactive booking and blocks new booking!

const existingBooking = await BookingService.getAll({
  filter: `_desk_value eq '${deskId}' and date eq ${date}`
  // Returns the INACTIVE booking - blocking new creation!
});

if (existingBooking.data?.length > 0) {
  throw new Error('Booking already exists');  // False positive!
}
```

#### Solution 1: Filter by Active State

```typescript
// ✅ Always include statecode filter when checking for existing records
const existingBooking = await BookingService.getAll({
  filter: `_desk_value eq '${deskId}' and date eq ${date} and statecode eq 0`
});
```

#### Solution 2: Use Hard Delete

For certain scenarios (like cancellable bookings), hard delete may be more appropriate:

```typescript
// ✅ Use hard delete when inactive records shouldn't affect business logic
export const cancelBooking = async (bookingId: string): Promise<boolean> => {
  try {
    await BookingService.delete(bookingId);  // Hard delete
    return true;
  } catch (error) {
    console.error('[BookingService] Error cancelling booking:', error);
    return false;
  }
};
```

### Decision Guide

| Scenario | Recommendation |
|----------|----------------|
| Audit trail required | Soft delete |
| Record has unique constraints | Hard delete OR always filter by statecode |
| User may need to "undo" | Soft delete |
| Temporary/transactional data | Hard delete |
| Record affects availability checks | Hard delete OR strict statecode filtering |

---

## 14. Select Considerations

### When Select Can Cause Issues

Using `select` to limit returned fields can sometimes exclude fields you need:

```typescript
// ❌ May miss fields you need
const result = await DeskService.getAll({
  select: ['deskid', 'name'],  // Missing 'workspacesetup'!
  filter: 'statecode eq 0'
});

// workspacesetup will be undefined!
console.log(result.data[0].workspacesetup);  // undefined
```

### Best Practice

For critical data, consider omitting `select` during development to ensure all fields are returned, then optimize later:

```typescript
// ✅ Development: Get all fields to understand the data
const result = await DeskService.getAll({
  filter: 'statecode eq 0'
});

// Log to see what's available
console.log('[Debug] Full record:', result.data[0]);

// ✅ Production: Add select once you know what you need
const result = await DeskService.getAll({
  select: ['deskid', 'name', 'floor', 'workspacesetup', 'is_blocked'],
  filter: 'statecode eq 0'
});
```

---

## Summary Checklist

✅ Use generated services from PAC CLI  
✅ Always cast Decimal fields to `as any` when sending to Dataverse  
✅ Convert string Decimal values back to numbers when reading  
✅ Use `@odata.bind` syntax for lookups  
✅ Implement service wrapper layer for type mapping  
✅ Handle errors gracefully with try-catch  
✅ **Use local date formatting, NOT `toISOString()`**  
✅ **Query `systemusers` by `domainname` to get Dataverse user IDs**  
✅ **Parse multi-select choice fields as comma-separated strings**  
✅ **Choose soft vs hard delete based on business requirements**  
✅ **Always filter by `statecode eq 0` when checking for existing records**  
✅ Use `select` and `top` to optimize performance (but verify fields are returned)  
✅ Validate data before sending to Dataverse

---

## Reference Example

See the working implementation in:
- `src/services/booking.service.ts` - CRUD operations with lookups and date handling
- `src/services/desk.service.ts` - Multi-select choice field parsing
- `src/services/user.service.ts` - SystemUsers integration and user ID resolution

**Key Lessons**:
1. **Decimal type mismatch**: Always send numeric values to Dataverse for Decimal fields
2. **Date handling**: Use local date components, never `toISOString()` for date-only fields
3. **User identity**: Azure AD objectId ≠ Dataverse systemuserid—query `systemusers` table
4. **Multi-select choices**: Parse comma-separated strings like "0,1,2" into arrays
5. **Soft delete pitfalls**: Consider hard delete for records affecting availability/uniqueness checks