Distinct on Lookup of many-to-many relationship

(0) Share

Report

Posted on by SLMR

518 Moderator

Good morning,

i have following data-model:

Customer (1-n) --> Projekt (1-n) --> Case (n:m) -->employee (Benutzer).

In a canvas app i need a gallery where only the customers are visible an employee is working for.

It is no problem to get the cases and therewith also the information about the projekts and customers:

LookUp(Benutzers,Fullname=User().FullName).'Cases (cr8ec_Projekt_Zugehoering_ref)'

But as this is on case level i will have multiple entries for the same customer.

As i can directly display the customer info in the gallery via (ThisItem.Projekt.Customer.name) it should be possible to filter the lookup somehow against the customers.

I think i will have to go to the projekts first via a next lookup.

But i am not complety sure on how to do this.

Any idea is welcome

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @SLMR ,

Could you please share more details about your scenario? Do you want to filter customers that current logged in user is working for?

If this is the case, please try below formula:

Distinct(AddColumns(LookUp(Benutzers,Fullname=User().FullName).'Cases (cr8ec_Projekt_Zugehoering_ref)',"Customer1",ThisRecord.Projekt.Customer.name),Customer1)

Best regards,

Was this reply helpful? Yes No
SLMR 518 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks a lot for the quick answer.
Works like charm for my application.

Just another short question:
Is it possible to alter the expression so that i can get the real customer entity.

With your solution i can fill my gallery as i want. I can get all other information of the selected customer by using lookup, so it is fine.
But getting the entity maybe interesting for the future.

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @SLMR ,

What do you mean by get real Customer entity? After you have unique customer names, you can simply use below formula to get rows from Customer entity:

ForAll( Distinct(AddColumns(LookUp(Benutzers,Fullname=User().FullName).'Cases (cr8ec_Projekt_Zugehoering_ref)',"Customer1",ThisRecord.Projekt.Customer.name),Customer1) As CC, LookUp(Customer, name = CC.Value) )

Best regards,

Was this reply helpful? Yes No
SLMR 518 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks again.
That saves me a lot of time.
Have a nice day

Was this reply helpful? Yes No