Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท Data and Dataverse

Relationships and Choices in Dataverse

Model related data in Dataverse with lookups, one-to-many and many-to-many relationships, and choice columns, and use them in Power Apps formulas such as ThisItem.Customer.Name.

Real data is connected: an order belongs to a customer, a project has many tasks, a person has several skills. Dataverse models these links with relationships, and fixed lists of values with choices. Using them well keeps your app simple and fast.

One-to-many relationships

The most common link: one row in a parent table relates to many rows in a child table. Customer to Orders is one-to-many.

You create it by adding a Lookup column to the child table, pointing to the parent. In the maker portal: open the child table, Columns, New column, type Lookup, choose the related table. The lookup stores the parent's GUID and displays its primary column.

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDERLINE : contains
  PRODUCT ||--o{ ORDERLINE : "is sold in"

Customer to Order and Order to OrderLine are one-to-many; Product to OrderLine too.

Relationship behavior controls what happens to children when you act on a parent:

  • Parental: delete, assign, share cascade to children (an order and its lines).
  • Referential: no cascade; you can restrict deletion or just remove the link.
  • Custom: choose individual cascade options.

Many-to-many relationships

A student has many courses, a course has many students. Dataverse supports a many-to-many relationship with a hidden intersection table. Use it for simple associations. When the link itself has data (enrolment date, grade), create your own junction table with two lookups and extra columns instead.

Using relationships in Power Apps

In a canvas app, a lookup column is a record. You navigate to parent values with dot notation:

TEXT
ThisItem.Customer.Name                    // customer name for this order
ThisItem.Customer.'Account Number'         // columns with spaces use single quotes

To set a lookup when writing, assign a record of the parent table:

TEXT
Patch(Orders, Defaults(Orders), {
  Customer: LookUp(Customers, Name = drpCustomer.Selected.Name),
  Notes: txtNotes.Text
})

For one-to-many from the parent's side, use the relationship name, which you can see in intellisense:

TEXT
ThisItem.Orders        // all orders of this customer (a table)
CountRows(ThisItem.Orders)

Using relationships avoids many separate LookUp calls and keeps formulas short.

The stand-in below models a lookup as a link and shows both directions of navigation.

JS Stand-in: lookups and one-to-many navigation

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Choice columns

A choice column offers a fixed list of values (Status: New, Active, Closed). Choices can be:

  • Local to one column, or global (shared by several tables, defined once).
  • Single or multiple (Choices column).

Each option has a label and a stored integer value, and optionally a color. Prefer a choice over free text for fixed lists. If the list needs frequent edits by business users or has extra data, use a lookup to a reference table instead.

In Power Apps a choice is a record with a Value, so you often write:

TEXT
Filter(Tickets, Status = 'Status (Tickets)'.Active)
ThisItem.Status.Value
Patch(Tickets, ThisItem, {Status: 'Status (Tickets)'.Closed})

The enumeration name includes the column display name. Intellisense helps, but names with spaces and parentheses need single quotes. For dropdowns, set Items: Choices(Tickets.Status).

The stand-in below represents a choice column with stored values and labels.

JS Stand-in: a choice column with stored values

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Design tips

  • Put a lookup on the many side only.
  • Use alternate keys to match data from imports without GUIDs.
  • Avoid very long chains (A.B.C.D.Name) in galleries; each hop can add work. Add a formula column or calculated column to expose the needed value.
  • Reuse global choices across tables so labels stay consistent.
  • Plan security: a user with access to a child may not have access to its parent. Test with a low-privilege user.

Delegation notes

Filtering on a lookup's field (Customer.Name = "Acme") is often not delegable. Filter on the lookup itself (comparing records or GUIDs) or on a column of the same table:

TEXT
Filter(Orders, Customer.Account = LookUp(Customers, Name = "Acme"))   // compare the key instead

Check delegation warnings as described in Delegation and data sources.

Common mistakes

  • Using text columns to hold customer names instead of a lookup.
  • Writing Patch(..., {Customer: "Acme"}); a lookup needs a record.
  • Comparing a choice to text: Status = "Active" fails; compare to the choice value or use Status.Value.
  • Creating many-to-many when extra fields are needed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How is a one-to-many relationship created from the child table?

    The lookup stores the parent's key on each child row.
  2. 2. How do you get the customer name for an order row in a gallery?

    A lookup column is a record, so use dot notation to reach its columns.
  3. 3. When should you create your own junction table instead of a native many-to-many?

    A custom junction table can hold extra columns for the relationship.
  4. 4. Which Items formula lists the options of a choice column in a dropdown?

    Choices(Table.Column) returns the available options.

Summary

  • One-to-many uses a lookup on the child; many-to-many uses an intersection or your own junction table.
  • Lookups are records in Power Apps: ThisItem.Customer.Name; assign a record when writing.
  • Choice columns hold fixed lists; use Choices() for dropdowns and compare to choice values.
  • Mind delegation and security when relating tables.

Next: Model-driven Apps - The Big Picture