Canvas App – Search, Filter, Custom Patch & Collections

Context & Recap

• Previous sessions: designed screens, navigation, used Form control, performed CRUD via Power Fx.
• Today’s focus:
  • Searching & filtering large galleries
  • Handling non-text columns (Choice/Lookup) with Filter()
  • Designing custom update screens (no Form control)
  • Using the Patch() function for create & update
  • Working with collection variables to speed up apps
  • Touching on validation, delegation warnings, error handling previews.

Gallery Search Basics

• Galleries auto-paginate (≈100 records per “screen scroll”).
• With thousands of records, pure scrolling is impractical; add Search + Filter UI.
• Search panel must be manually built in Canvas apps (contrast: model-driven has built-in search bar).
• Typical elements to add:
  • Text input → txtSearchAppointment (hint: "Search…", plus a decorative search icon)
  • Additional filter controls (Combo box, dropdown, etc.)
• Bind them through the Items property of the gallery—​all logic lives there.

Core Search Formula Skeleton

Search(
    Filter(Appointments, 'Doctor Email' = User().Email),
    txtSearchAppointment.Text,
    "AppointmentNumber", "Description", "Subject"   // only text columns allowed
)

• Evaluation order: inner expression first → Search returns subset → outer Filter refines.

Limitations of Search()

• Accepts only plain-text columns (Single Line of Text, Multiline, etc.).
• Cannot search complex types (Choice, Lookup, People, Yes/No, or Datetime).
• For those, use Filter() with equality or in.

Filtering Choice / Lookup Columns

1. Add a Combo Box: cmbSymptoms
   • Items → Choices(Appointment.Symptoms)
   • AllowMultipleSelection = true
2. Build composite Items formula:
   (
   If(
   IsEmpty(cmbSymptoms.SelectedItems),
   // --- No symptom chosen ---
   Filter(
   Search(
   Appointments,
   txtSearchAppointment.Text,
   "AppointmentNumber", "Description", "Subject"
   ),
   'Doctor Email' = User().Email && Status = "Open"
   ),
   // --- At least one symptom chosen ---
   Filter(
   Search(
   Appointments,
   txtSearchAppointment.Text,
   "AppointmentNumber", "Description", "Subject"
   ),
   'Doctor Email' = User().Email &&
   Status = "Open" &&
   Symptoms in cmbSymptoms.SelectedItems
   )
   )
   )
   Explanation:

• IsEmpty() prevents the “blank combo = no results” bug.
• Symptoms in cmbSymptoms.SelectedItems works because ComboBox returns a table of records.
• Status = "Open" compares display names of Option Set values; left side must be the column display name, not the enum‐type object (Appointment.Status).

Common Pitfalls & Fixes

• "Incompatible types: OptionSetValue vs OptionSetValue" → compare display name strings.
• Delegation warnings: complex Search in Filter combos may exceed 2 000-row limit; redesign or move to model-driven if queries are heavy.
• Default selection: set cmbSymptoms.DefaultSelectedItems = Filter(Choices(Appointment.Symptoms), Value = "Fever") (uses a table, not a single record).

When the Logic Grows – Architectural Advice

• Multiple nested filters quickly hit readability and delegation limits.
• Heavier scenarios (e-commerce-style facets, 10-plus filters) belong in model-driven apps where grid search, sort and Advanced Find are out-of-the-box.

Custom Update Screen (No Form Control)

1. Drop standalone controls:
   • txtPatientFirstName, txtPatientLastName, txtPatientEmail, dtDOB, btnSubmit.
2. Patch() syntax for Create:
   $(<br /> Patch(<br /> Contacts,<br /> Defaults(Contacts), // ← create mode<br /> {<br /> FirstName: txtPatientFirstName.Text,<br /> LastName: txtPatientLastName.Text,<br /> Email: txtPatientEmail.Text,<br /> 'Birth Date': dtDOB.SelectedDate<br /> }<br /> )<br /> )$
3. Add success message:

Notify(
    "Patient record created successfully: " &
        LookUp(Contacts, Email = txtPatientEmail.Text).'Patient ID',
    NotificationType.Information
)

• LookUp safely returns the first match (no need for First()); if duplicates possible, use First(Filter(...)).

Updating Instead of Creating

Patch(
    Contacts,
    LookUp(Contacts, Email = "xyz@gmail.com"),   // record to update
    { FirstName: "NewName" }
)

Collections – Local Tables for Performance

• Collect() / ClearCollect() create a collection variable (global scope, lives in memory until the app closes).
• Benefits:
  • One-time pull from Dataverse at startup; avoids repeated Web API chatter.
  • Lightning-fast sort/filter inside the app.
• Example in App.OnStart:
  $(<br /> Collect(<br /> colShifts, // collection name<br /> DoctorShifts // full table imported once<br /> )<br /> )$
• Gallery now binds to colShifts instead of DoctorShifts.

Editing a Collection Row Inline

// Button inside gallery item
Patch(
    colShifts,
    ThisItem,
    { DoctorAvailability: !DoctorAvailability }
);
Notify("Shift updated", NotificationType.Success)

• Updates only the in-memory collection → instant UI response.

Syncing Collection Back to Dataverse

ForAll(
    colShifts,
    Patch(
        DoctorShifts,            // real table
        ThisRecord,             // the corresponding row
        { DoctorAvailability: DoctorAvailability }
    )
)

• Prefer specifying the exact columns ({DoctorAvailability: …}) to avoid the "network error – cannot modify CreatedBy" issue.
• Could add a loading spinner + error handling (IfError, Notify) for production.

Variable Types Recap

• Context variable: UpdateContext({locVar: value}) – screen-local.
• Global variable: Set(glbVar, value) – app-wide single value.
• Collection variable: Collect(colName, table) – app-wide in-memory table.

Validation & Error Handling (Previewed)

• Mandatory Dataverse columns (e.g., Birth Date) still enforce server-side rules; patch fails until included.
• Use IfError( Patch( … ), Notify("Failure…", NotificationType.Error) ) pattern.
• Delegation messages (blue underline) warn that some formula parts will evaluate client-side only; consider redesign or splitting queries.

Key Take-Aways

• Combine Search() (text only) + Filter() (all types) for flexible querying.
• Use IsEmpty() guards to avoid unintended blank-filter behaviour.
• ComboBox for Choice multi-select: Choices(Entity.Column) + SelectedItems.
• Heavy filter matrices → move to model-driven, not Canvas.
• Patch() is the Swiss-army-knife for custom create/update.
• Collections drastically speed UI, but remember a final sync step to Dataverse.
• Always test with large data sets & watch delegation limits to keep results complete.