Answered

Display many to one relationship in Model Driven App grid view (filter the Many to one)

(0) Share

Report

Posted on by PowerAppsCom1

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to display a grid view of all customers along with their last purchase. How is that done in a Model Driven App? Can I bring in the last purchase with a formula column? or should I use Power BI? Or should I use Dataflow to join these tables?! But Dataflow is limited to a 30-minute update.

Please use these table/column names in your answer:

customer: id, name
purchase: id, customer_id, item_id, date

----------------------------

Similar to the above question. How do I make a form of the customer table it should include only the last purchase in the subgrid component?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

SBax 1,348 on at

Like (0)

Report
Copy link

Link copied!

What sort of volume of purchases are you expecting?

If volumes are relatively low, you could use power automate to write the details of a purchase to the customer table each time a purchase is made.

There is also the option of doing the same using JavaScript calling in the onSave event of the purchase

Happy to help either option if you think one would be suitable?

Was this reply helpful? Yes No
Verified answer

narayan225 2,547 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

@PowerAppsCom1

If you are creating a view, you can create a view from the Purchase table to list the associated customer and sort it by created.

If you need it from customer, you can create a lookup to the last purchase. You can use a flow to pupulate that column whenever the new order is created.

This should allow you to create your view and add to your form.

Cheers!

Was this reply helpful? Yes No
PowerAppsCom1 10 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply.
I don't want to go with the Power Automate and JavaScript solution since they might trough errors and I will also have duplicate data. I wonder how come this is not a build feature in Power Apps. This is probably a broad-looked feature. The example above is just 1 of the many N:1 relationship grid views I'm looking for. How can you analyze your data without this view?
How will I go when I want to check customers who didn't purchase anything in the last year or customers with total sales greater than a certain amount?

The same need can be with a School with a list of students and multiple tests and gradings
How can they analyze who completes the tests and so on?

The concept I look for is to have a grid of 1 row per customer/student etc. and pull into the view certain subtables so we can find things without opening each customer/student etc. record.

It is quite interesting that in Excel you can do all kinds of lookups and here you are so limited.

Do you know if, with the Canvas app, we can do this kind of grid?
Or maybe I should look into the Reports feature?

Was this reply helpful? Yes No
Verified answer

SBax 1,348 on at

Like (1)

Report
Copy link

Link copied!

You could use a canvas app, combine filter and first functions to accomplish this.

You could even use the creator kit "details list" component to display a list of customers, use add columns to create your last purchase alongside any other columns, and configure the list to navigate to the main form if required.

I would say this is your best option if you want a list, and don't want to use flow or JS

Was this reply helpful? Yes No