Dataverse Lookups

(0) Share

Report

Posted on by DM-29100140-0

Hi, I'm currently working on a Task List project for Real Estate. This would be like a task per property but will be logged in a table UserTaskLog. However, I kind of find it hard to deal with lookups or relationships. I have established one to many relationships with my different tables but I still don't understand how it works and I've been using gallery filters still. Do you have any recommendations?

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

KBpwr 55 on at

Like
a
(1)

Report
Copy link

Link copied!
This can be a bit tricky if you haven't done it before. I work for a big plant and have built a wide variety of "task" based apps and sort of learned as I've went. I'm sure some of the power users on here can explain things clearly, but here’s how I like to think about setting up this kind of app for tasks tied to properties and such. If you’re dealing with a bunch of properties, and each property has multiple tasks, this setup can save you from having to filter things manually every time.

First, Understanding the One-to-Many Relationship

Here’s how I like to picture the one-to-many relationship:

Each property (say, “Main St. Property”) can have many tasks tied to it, but each task only belongs to one property.

In Power Apps terms, this setup looks like this:

In your Properties table, make sure you have a unique PropertyID for each property.

In your UserTaskLog (or Tasks table), create a lookup field (we’ll call it PropertyLookup) that connects each task back to the property by referencing its PropertyID. (if you've never done this, just create a lookup column and link them (select Properties, then select PropertyID for example as the field to link to.)

So, every task in UserTaskLog has a “home” property it’s linked to.

Using the Relationship to Avoid Gallery Filters

Here’s where you can skip using gallery filters by leveraging the relationship.

Add a Property Gallery (PropertyGallery) to display the list of properties.

Add a Task Gallery (TaskGallery) right below it to display the tasks for each selected property.

Now, instead of filtering TaskGallery manually, set its Items property to:

PropertyGallery.Selected.Tasks

What this does is automatically pull in only the tasks linked to the selected property. So, if you click on “Main St. Property” in PropertyGallery, TaskGallery will only display the tasks associated with it—no need to set up a manual filter like Filter(UserTaskLog, PropertyID = PropertyGallery.Selected.PropertyID).

Example Walkthrough

Let’s say your UserTaskLog table looks something like this:

Task PropertyLookup

Fix Roof 101

Inspect Plumbing 101

Paint Walls 102

And in your Properties table, you have:

Property Name PropertyID

Main St. Property 101

Elm St. Property 102

When you set up your relationship, Power Apps “knows” that tasks with PropertyLookup 101 belong to “Main St. Property.” So, selecting “Main St. Property” in PropertyGallery will automatically bring up tasks like “Fix Roof” and “Inspect Plumbing” in TaskGallery.

Adding New Tasks without Extra Clicks

You can take it a step further by setting your form to auto-link new tasks to the selected property:

Add a form for creating new tasks (TaskForm).

In TaskForm, set the Default value for PropertyLookup to:

PropertyGallery.Selected.PropertyID

Now, if you’re viewing “Main St. Property” and add a task, it automatically links it to that property, no need to select the property each time.

Once this is set up, here’s how it works:

Select a Property in PropertyGallery → TaskGallery instantly shows only the tasks linked to that property.

Add a New Task → The task is automatically tied to the selected property without needing to select it manually.
DM-29100140-0 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @KBpwr, thanks a lot for your insight. I do understand the concept of One-to-Many relationships or the other type of relationship available for ERD. However, I can't somehow get how the powerapp works with it. See, I have created the app already and I have tables: Property,Task List, Sub Category,Checklist, UserTaskLog.

First screen is the properties screen where you can search property or which property you would create a task to.

Next screen would be the list of task list and its subcategory, and I have a button labeled proceed, that is where user should proceed creating a task list, with all the sub category and checklists under that specific task list chosen. I'm kind of confuse how to do this part.

All gallery filters we're modified on the formula even though I have created relationships between tables.

Here are the relationship:

UserTaskLog > One-to-Many > User Profile

Property > One-to-Many > Task List (should be many to many right?)

Task List > One-to-Many > Sub Category

Sub Category > One-to-Many > Checklist

User Profile > One-to-Many > Task List
ronaldwalcott 3,914 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You essentially have two kinds of lookups.

One is the descriptive attribute of an item

e.g. An image item may have a usage attribute. A relationship is set up between the two tables so the id of the usage attribute is effectively stored in the image table.

To select the usage attribute in an entry form for the image details you would use a combobox where the items property would use the Choice function, something like

Choices([@Images].cr836_UsageID)

The second type of lookup is where you need to enter child records linked to a parent record. The child record has a column containing a reference to the parent and the parent has a reference to the children.

You need to provide more details on your table structure for someone to determine the type of lookup being implemented thus how it would look visually.
DM-29100140-0 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Ronald, please refer to this structure

UserTaskLog > One-to-Many > User Profile

Property > One-to-Many > Task List (should be many to many right?)

Task List > One-to-Many > Sub Category

Sub Category > One-to-Many > Checklist

User Profile > One-to-Many > Task List
ronaldwalcott 3,914 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You would need to provide the complete data structures and indicate where you are experiencing difficulties.

You have indicated that the tables and relationships created. What errors are you seeing in the screens?
Suggested answer

DM-29100140-0 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello guys thank you for your insights. I finally figured out how lookup columns work, the current problem changed tho. I kind of not able to patch and paste global variables into a lookup column
ronaldwalcott 3,914 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You cannot just paste global variables into a lookup column. Post another question showing the errors that you are getting.