cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AVTS
Super User
Super User

Clearcollect with Lookup

Experts,

 

I have following table ,

 

Table name  oki  and columns are follows

mm ,planta, plantb , plantc, plantd

 

AVTS_0-1654708949710.png

 

 

TableB name  emailedc and columns are follows

email , plant

 

 

sadsd2333.PNG

 

Existing formula 

 

ClearCollect(mmasterv1,oki)

 

With this all data is replicating to table mmasterv1

 

Requirement is i need to restrict the materials by plant . it should look at the log in user plant and plant in oki table. Tried with below formula looks not working. 

 

ClearCollect(mmasterv1,LookUp(emailedc,email=User().Email,plant = oki.planta )


);

 

Please share your expertise 

1 ACCEPTED SOLUTION

Accepted Solutions

@AVTS ,

This structure would probably be better

With(
   {
      wPlant:
      LookUp(
         emailedc,
         email = User().Email
      ).plant
   },
   ClearCollect(
      mmasterv1,
      Filter(
         oki,
         planta = wPlant ||
         plantb = wPlant ||
         plantc = wPlant ||
         plantd = wPlant
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

Hi @AVTS ,

Try Filter instead of LookUp - you are also referencing a Table in the second part

ClearCollect(
   mmasterv1,
   Filter(
      emailedc,
      email = User().Email && 
      plant in oki.planta
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

Hi @WarrenBelz  thank you tried this but plant is not coming if you add && . What could be the issue ?

Hi @AVTS ,

Actually looking at it again

ClearCollect(
   mmasterv1,
   Filter(
      emailedc,
      email = User().Email && 
      (
         plant in oki.planta ||
         plant in oki.plantb ||
         plant in oki.plantc ||
         plant in oki.plantd
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Hi @WarrenBelz  thanks for your response, 

 

With this formula collection  mmasterv1 is showing filtered data from tableb which is emailedc instead of showing filtered data from tableA which is oki.

 

Expected results , if user abc@yahoo.com logged in then collection(mmasterv1) should only the data of U051 from table1 oki.

mm
10121900
10153701

@AVTS ,

That is what I needed originally (the result you actually wanted) - try

With(
   {
      wPlant:
      LookUp(
         emailedc,
         email = User().Email
      ).Plant
   },
   Filter(
      oki,
      planta = wPlant ||
      plantb = wPlant ||
      plantc = wPlant ||
      plantd = wPlant
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Hi @WarrenBelz  clearcollect we should correct ? 

 

ClearCollect(
mmasterv1,

With(
{
wPlant:
LookUp(
emailedc,
email = User().Email
).plant
},
Filter(
oki,
planta = wPlant ||
plantb = wPlant ||
plantc = wPlant ||
plantd = wPlant
))
);

@AVTS ,

This structure would probably be better

With(
   {
      wPlant:
      LookUp(
         emailedc,
         email = User().Email
      ).plant
   },
   ClearCollect(
      mmasterv1,
      Filter(
         oki,
         planta = wPlant ||
         plantb = wPlant ||
         plantc = wPlant ||
         plantd = wPlant
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,416)