Instruction file imported from migoamigoea-star/ioi-docs-uploads (.github/instructions/canvas-powerfx-formulas.instructions.md). Copyright stays with the author.

⚠️ DEPRECATED — This file has been consolidated into .github/instructions/canvas-pa-yaml.instructions.md (Sections C through H). See that file for all Power Fx formula rules, SharePoint schemas, formula patterns, connector rules, and compliance checklists. Do not create new references to this file. Update existing cross-references to point to canvas-pa-yaml.instructions.md.

Canvas App Power Fx Formula & SharePoint Schema Instructions

Scope: All .pa.yaml files in the IOI Domino → M365 migration project. Every Power Fx formula must use real SharePoint column names, follow IOI naming conventions, and comply with .pa.yaml Schema v3.0.

When a screenshot exists, it is a visual reference only. Do not derive final control names, field names, formulas, variables, data sources, or label text from the screenshot unless they also match the real app schema/behavior.

These rules are mandatory whenever a screen is created or modified. Do not defer formula/schema compliance to a later audit pass.

🆕 Cross-Reference: design/IOI-CANVAS-SCREEN-SPEC.md is the companion Screen Requirement Spec covering layout dimensions, typography, color system, container strategy, control properties, responsive patterns, validation, people integration, and PAC CLI commands. Use this instructions file for formula syntax; use the Screen Spec for screen-level design standards.

Severity levels:

• CRITICAL — Wrong column name or type will cause runtime error. Must fix before merge.
• IMPORTANT — Violates conventions, causes maintenance burden. Fix in same sprint.
• SUGGESTION — Best practice improvement. Plan for future iteration.

1. Formula Writing Rules

F1: All Formulas Start with =

• Severity: CRITICAL
• Every Power Fx expression in a .pa.yaml property MUST begin with =

# CORRECT
Text: =ThisItem.Title
Visible: =varShowPanel
Fill: =gblITTheme.PrimaryColor

# WRONG — missing = prefix
Text: ThisItem.Title

F2: Use Multiline Block Scalar for Complex Formulas

• Severity: CRITICAL
• When a formula contains # (YAML comment) or : (YAML mapping), use |- block scalar
• Single-line formulas are fine for simple expressions

# SIMPLE — single line is OK
Visible: =varShowPanel
Text: ="IOI IT Support"

# COMPLEX — multiline required (contains : and multiple lines)
OnVisible: |-
  =Set(gblITTheme, { PrimaryColor: RGBA(0,32,70,1) });
  ClearCollect(colKPIs, { Label: "Total", Value: Text(CountRows(Filter(MainDB_IT, FormCode = "ITSSR"))) })

F3: No C-Style Escaping in Formulas

• Severity: CRITICAL
• \n is NOT a newline in Power Fx — it's a literal backslash-n
• Use YAML multiline block scalars (|, |-, |+) for multi-line content
• Power Fx does NOT support \t, \r, \n escape sequences

F4: Chaining Multiple Actions with ;

• Severity: IMPORTANT
• In behavior formulas (OnSelect, OnVisible, OnHidden), chain actions with ;
• Actions execute in order; next action waits for current to complete

OnSelect: |-
  =Set(varLoading, true);
  Patch(MainDB_IT, ThisItem, { CurrentStatus: { Value: "Approved" } });
  Set(varLoading, false);
  Navigate(scrSuccess, ScreenTransition.Fade)

F5: Prefer Declarative Over Imperative

• Severity: IMPORTANT
• Use formula-driven properties (auto-recalculate) instead of imperative Set() + UpdateContext()
• Only use imperative logic in On... behavior formulas when state mutation is required

# GOOD — declarative, auto-updates when data changes
Items: =Filter(MainDB_IT, FormCode = "ITSSR" && CurrentStatus.Value = "Active")

# BAD — imperative, requires manual refresh
# OnVisible: Set(colItems, Filter(MainDB_IT, FormCode = "ITSSR" && CurrentStatus.Value = "Active"))
# Items: colItems

2. Naming Conventions

⚠️ This section is maintained in a dedicated file. For the complete, authoritative control naming reference, see: .github/instructions/canvas-pa-yaml.instructions.md (Section A)

This deprecated file covers Power Fx formula syntax and SharePoint schema only. All naming rules were updated during the 2026-06-18 universal rename (30,480 controls renamed).

N1: Control Naming — Full Convention in Dedicated File

• Severity: IMPORTANT
• The full naming convention is <ScreenCode>_<ControlTypePrefix>_<Purpose>
• Control name prefix quick reference (see dedicated file for full table):

Cross-reference: See .github/instructions/canvas-pa-yaml.instructions.md (Section A) for complete examples, ScreenCode derivation rules, component rules, duplicate prevention, and reserved keywords.

N2: Variable Naming Conventions

• Severity: IMPORTANT

N3: Theme Object Structure

• Severity: IMPORTANT
• Every screen MUST set gblITTheme in OnVisible

OnVisible: |-
  =Set(gblITTheme, {
    PrimaryColor: RGBA(0,32,70,1),
    SecondaryColor: RGBA(80,95,118,1),
    AccentColor: RGBA(37,99,235,1),
    LightBg: RGBA(248,250,253,1),
    Surface: RGBA(255,255,255,1),
    SuccessColor: RGBA(22,163,74,1),
    WarningColor: RGBA(245,158,11,1),
    DangerColor: RGBA(220,38,38,1),
    TextPrimary: RGBA(26,27,30,1),
    TextSecondary: RGBA(68,71,78,1),
    BorderColor: RGBA(196,198,207,1),
    SoftBlue: RGBA(214,227,255,1),
    PrimaryContainer: RGBA(27,54,93,1),
    SurfaceContainerLow: RGBA(244,243,247,1),
    SurfaceContainerHigh: RGBA(233,231,235,1)
  })

N4: Screen Naming Convention

• Severity: IMPORTANT
• Screen names: scr{FormCode}{ScreenType} — e.g., scrITSSRMgmtReport, scrSHEForms
• No spaces, PascalCase after prefix

2A. Connector Usage Rules

C1: Use Office365Users for Real Person Identity on Screen

• Severity: IMPORTANT
• If a field or UI element is intended to show a real person identity, prefer Office365Users over plain text when the connector is available.
• This applies especially to:
  • name
  • email
  • requestor
  • requested by
  • submitted by
  • assigned to
  • approver
  • reviewer
  • manager
  • HOD
  • checked by
  • verified by
  • prepared by

Use the SharePoint field as the persistence source, and use Office365Users for display enrichment, lookup, profile, manager, and photo behavior.

// Current user identity
User().FullName
User().Email

// Office 365 profile enrichment
Office365Users.MyProfile().DisplayName
Office365Users.MyProfile().Department
Office365Users.MyProfile().JobTitle

// Specific person by email/UPN
Office365Users.UserProfileV2(txtEmail.Text).displayName
Office365Users.UserProfileV2(txtEmail.Text).mail

// Manager lookup
Office365Users.ManagerV2(txtEmail.Text).displayName

// Search people for picker scenarios
Office365Users.SearchUserV2({
  searchTerm: txtPersonSearch.Text,
  top: 10,
  isSearchTermRequired: true
}).value

C2: Do Not Treat Name/Email Text as Canonical Identity When a People Pattern Is Intended

• Severity: IMPORTANT
• If the screen is meant to show a real employee, approver, requestor, or owner, do not rely on a disconnected plain text label when Office365Users or a SharePoint Person field is available.
• Text-only storage columns such as SubmittedBy_Email, Requestor_Email, or ApprovedBy_Name may be used as fallback fields, audit snapshots, or quick persistence values, but the preferred screen pattern is:
  • SharePoint Person field when present
  • otherwise Office365Users lookup using email/UPN
  • otherwise text fallback with explicit limitation

C3: Connector Responsibility Split

• Severity: IMPORTANT
• Use the correct connector for the correct job:

C4: Approvals Should Usually Run Through Power Automate, Not Screen-Only Logic

• Severity: IMPORTANT
• For production approval workflows, prefer: Canvas App -> Flow.Run(...) -> Power Automate approval action
• Use the Approvals connector and Start and wait for an approval inside the flow. This is the default enterprise pattern for maintainable approval logic.

Set(
  varApprovalResult,
  Flow_SendApproval.Run(
    Text(ThisItem.ID),
    ThisItem.Title,
    ThisItem.CurrentApprover_Email,
    txtApprovalDetails.Text
  )
)

C5: Use Office365Outlook for Email Delivery

• Severity: IMPORTANT
• Use Office365Outlook.SendEmailV2() for direct app-driven email send scenarios.
• If the email is part of workflow/state transition logic, prefer Power Automate so the send is auditable and survives app closure.
• For calendar events, meeting booking, and room reservation, prefer Power Automate with Outlook calendar actions such as Get calendars (V2), Get room lists (V2), Get rooms in room list (V2), Find meeting times (V2), and Create event (V4).

Office365Outlook.SendEmailV2(
  txtTo.Text,
  txtSubject.Text,
  txtBody.Text
)

C6: If Additional Standard Connectors Are Required, Use Them Deliberately

• Severity: SUGGESTION
• When the user explicitly approves broader connector usage, standard connectors may be used where they are the right platform-native tool.
• Still prefer the simplest supported connector first:
  • Office365Users for people
  • Office365Outlook for email
  • Power Automate + Approvals for approval workflow
• Do not introduce premium connectors unless explicitly approved by the user and allowed by the target app architecture.

C7: Official Source References

• Severity: SUGGESTION
• Use these Microsoft references when uncertain:
  • Office 365 Users in Power Apps: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-office365-users
  • Office 365 Users connector reference: https://learn.microsoft.com/en-us/connectors/office365users/
  • Office 365 Outlook in Power Apps: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-office365-outlook
  • Office 365 Outlook connector reference: https://learn.microsoft.com/en-us/connectors/office365/
  • Approvals connector reference: https://learn.microsoft.com/en-us/connectors/approvals/
  • Trigger flows from Canvas apps: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/using-logic-flows
  • Approval workflow guidance: https://learn.microsoft.com/en-us/power-automate/modern-approvals
• Use this local repo playbook for ready-to-apply examples: ioicodeapp/canvas-apps/metadata/powerfx/m365-connector-patterns.md

3. SharePoint List Schema — MainDB_IT

3.1 Architecture

The IOI project uses a single wide-column table per department portal with a FormCode discriminator column. All 23+ Domino forms write to the same MainDB_IT list, with form-specific columns prefixed by form code.

IT Portal Site: https://ioioi.sharepoint.com/sites/ioi-portal-it

3.2 MainDB_IT — Core System Columns (All Forms)

These columns exist on EVERY record. Formulas MUST use these exact internal names.

3.3 CurrentStatus Choice Values

Formulas MUST use .Value to access Choice column values.

Active, Approved, Closed, Draft, Sent, Verified, Withdrawn,
Pending ABAPER, Pending Acceptance, Pending Approval A, Pending Approval B,
Pending Authority 1, Pending Authority 2, Pending BASIS,
Pending Department Head, Pending Director, Pending ED Approval,
Pending IT Approval, Pending IT Manager, Pending ISG, Pending Review,
In Progress, Submitted, Accepted, Completed, Denied,
Open, Rescheduled, Overdue, On Hold, Cancelled

IMPORTANT: Status values vary by form. Always use Coalesce() to handle missing values:

Coalesce(ThisRecord.CurrentStatus.Value, ThisRecord.Status.Value, "Submitted")

3.4 Form-Specific Prefix Columns (MainDB_IT)

Each form stores its unique columns in MainDB_IT with a prefix matching the FormCode.

ITSSR (IT Support Service Request)

ITSSR also uses: MainDB_IT_Ext_Support (30+ columns) — see §3.5

EAF (External Access Form)

HI (Hardware Inventory)

NIR (Notes ID Request)

PRF (Program Request Form)

RS (Reset Password)

SFAR (Share Folder Access Request)

UR (User Registration)

3.5 Extension List: MainDB_IT_Ext_Support (ITSSR Detail)

Used by ITSSR form. Link to parent via ParentID (Lookup → MainDB_IT).

3.6 Extension List: MainDB_IT_Ext_Assets (Hardware)

3.7 Extension List: MainDB_IT_Ext_Access (EAF/NIR/RS/SFAR/UR/PRF Approvals)

3.8 Extension List: MainDB_IT_Ext_SAP

3.9 Extension List: MainDB_IT_Ext_Infra

3.10 Extension List: MainDB_IT_Ext_Docs

4. Reference / Config Lists

These lists provide lookup data and configuration.

5. Multi-Department Portal Schemas

Each department has its own SharePoint site with MainDB_{DEPT} + extension lists.

6. Formula Patterns — MUST Use These Exact Patterns

F6: Filtering by FormCode

• Severity: CRITICAL
• EVERY query to MainDB_IT MUST include FormCode = "{code}" as the FIRST filter condition
• NEVER query MainDB_IT without a FormCode filter — it returns all 23+ forms' data

# CORRECT
Items: =Filter(MainDB_IT, FormCode = "ITSSR")

# CORRECT — multi-condition
Items: =Filter(MainDB_IT, FormCode = "ITSSR" && Coalesce(CurrentStatus.Value, Status.Value) = "Active")

# WRONG — returns all forms
Items: =MainDB_IT

F7: Accessing Choice Columns

• Severity: CRITICAL
• Choice columns MUST be accessed with .Value — direct reference returns the choice object, not the text

# CORRECT
Text: =ThisItem.CurrentStatus.Value

# WRONG — returns object, not text
Text: =ThisItem.CurrentStatus

F8: Safe Column Access with Coalesce

• Severity: IMPORTANT
• Use Coalesce() when a column might be blank to provide fallback values
• This is critical because not all forms populate all columns

# CORRECT — safe fallback chain
Text: =Coalesce(ThisRecord.Subject, ThisRecord.Title, Text("ITSSR-" & ThisRecord.ID))

# CORRECT — safe status with fallback
Text: =Coalesce(ThisRecord.CurrentStatus.Value, ThisRecord.Status.Value, "Submitted")

F9: Patch — Update Existing Record

• Severity: CRITICAL
• Use Patch(MainDB_IT, ThisItem, { ... }) to update the current record
• Choice columns MUST be updated as { Value: "NewStatus" } objects

OnSelect: |-
  =Patch(MainDB_IT, ThisItem, {
    CurrentStatus: { Value: "Approved" },
    ApprovedBy_Email: Lower(User().Email),
    ApprovedBy_Name: User().FullName,
    Remarks: txtRemarks.Text
  });
  Notify("Record approved.", NotificationType.Success);
  Back()

F10: Patch — Create New Record

• Severity: CRITICAL
• Use Patch(MainDB_IT, Defaults(MainDB_IT), { ... }) to create new records
• Always include FormCode, CurrentStatus, and required columns

OnSelect: |-
  =Patch(MainDB_IT, Defaults(MainDB_IT), {
    Title: txtSubject.Text,
    FormCode: "ITSSR",
    CurrentStatus: { Value: "Submitted" },
    SubmittedBy_Email: Lower(User().Email),
    SubmittedBy_Name: User().FullName,
    SubmittedDate: Now(),
    Department: txtDepartment.Text,
    Subject: txtSubject.Text,
    Requestor_Email: Lower(User().Email),
    Requestor_Name: { '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", Claims: "i:0#.f|membership|" & Lower(User().Email), DisplayName: User().FullName, Email: Lower(User().Email) }
  });
  Notify("Ticket created.", NotificationType.Success)

F11: LookUp — Find Single Record

• Severity: IMPORTANT
• Use LookUp (not First(Filter(...))) when you need one record
• More efficient and cleare

*Truncated - read the full file at https://github.com/migoamigoea-star/ioi-docs-uploads/blob/a1b8baaa6c43f0a06fd8cd7602abeebdf46a14dc/.github/instructions/canvas-powerfx-formulas.instructions.md.