Clearcollect with Lookup

[AVTS]

Experts,

I have following table ,

Table name  oki  and columns are follows

mm ,planta, plantb , plantc, plantd

TableB name  emailedc and columns are follows

email , plant

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 

 

[WarrenBelz]

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

[AVTS]

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

[WarrenBelz]

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

[AVTS]

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

[WarrenBelz]

@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

[AVTS]

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
))
);

[WarrenBelz · Accepted solution]

@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