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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

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

Top Kudoed Authors
Users online (3,076)