Dataverse columns and relationships
Good data design is the base of every intelligent app. The exam often gives a requirement such as “do not store the value” or “cascade the owner” and expects you to pick the exact column type or behavior.
Columns that calculate
| Type | Where it runs | Stored? | Notes |
|---|---|---|---|
| Formula | Power Fx, evaluated when read | No | Newer, preferred for simple logic |
| Calculated | Dataverse server, on save | Yes | Older, uses actions such as CONCAT, TRIMLEFT, TRIMRIGHT |
| Rollup | Background job (about hourly, or on demand) | Yes | Aggregates child records |
Calculated columns use functions such as TRIMLEFT(Number, 4) to cut the first four characters. Count the prefix length carefully: the number is the count of characters to remove.
A rollup is not instant. It is refreshed by a system job and can be recalculated manually. Use it for “total of related records”, not for real-time values.
Lookup columns
A lookup column stores a reference to a row in another table. Creating one creates a one-to-many relationship (from the target table’s point of view). Use it when the link must be part of the table definition and maintained by the platform.
For many-to-many, Dataverse uses a hidden intersect table. Choose it when both sides can have many matches, for example contacts and marketing lists.
Relationship behavior
The relationship behavior decides what happens to children when the parent changes.
| Behavior | Effect |
|---|---|
| Parental | Everything cascades: assign, share, unshare, delete, reparent |
| Referential | Link only. Deleting the parent removes the lookup value |
| Referential, Restrict Delete | Link only, and the parent cannot be deleted while children exist |
| Custom | You set each action yourself |
“When the owner changes, child records get the same owner” is Parental, because it cascades assign.
Choice and other column types
Use a Choice column for a fixed list of options, Yes/No for two states, Autonumber for generated identifiers, and File or Image for binary content. A prompt column fills a text value by using an AI prompt over other columns in the row.
Exam traps
- Calculated and formula are both “computed”, but only the formula column is not stored.
- Rollup is for related child rows. Calculated works only on the same row (and its parent lookups).
- Referential does not cascade assign. Only Parental does.
- A lookup is created on the “many” side. Do not confuse it with a rollup that goes the other way.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. A value must be shown on a form by combining two columns, must be computed when viewed, and must not be stored in the database. Which column type fits?
Show answer
Answer: B.A formula column uses Power Fx and is evaluated when it is read, so nothing is stored. A calculated column stores its value.
2. An account must show the total value of all its related opportunities. Which column type do you add?
Show answer
Answer: C.A rollup column aggregates (sum, count, min, max, average) values from related child records.
3. When the owner of a parent record changes, all child records must change owner too. Which relationship behavior do you use?
Show answer
Answer: C.Parental cascades assign, share, delete and reparent from the parent to its children.
4. A contact must point to one account, and the link must be maintained by the platform as part of the table definition. Which column type creates this?
Show answer
Answer: A.A lookup column is the many-to-one relationship; it is created together with the relationship.