Lookup and display employee picture in PowerApps gallery using multiple SharePoint lists as data sources

[Marc_Gutzwiller]

I am having a problem where I want to show the picture of an employee in a gallery based on their email address. I have three data sources: "CO2DatenApp", "MitarbeiterListePersAngaben", and "MABilderApp".

The "CO2DatenApp" data source contains a row with the employee's email address ("CreaterMail"). The "MitarbeiterListePersAngaben" data source contains a row with the employee's email address ("GeschäftsMail") and employee number ("PersonalNummer"). The "MABilderApp" data source contains a row with the employee number ("IDMitarbeiter") and the employee's picture ("Name").

To show the picture of the employee in the gallery, I need to use a formula that looks up the employee's email address in "MitarbeiterListePersAngaben" to get their employee number, and then looks up the employee number in "MABilderApp" to get their picture.

How can I achieve that? 

Thx for your help!

[JohnM86]

You can achieve this by creating a formula that combines the data from all three data sources.

First, you need to use the "CO2DatenApp" data source to get the employee's email address. Let's assume the name of the field containing the email address is "Email".

Next, you need to use the "MitarbeiterListePersAngaben" data source to get the employee number associated with the email address. You can use the formula below to do this:

Lookup(MitarbeiterListePersAngaben, GeschäftsMail = Email, PersonalNummer)

This formula uses the "Lookup" function to find the row in the "MitarbeiterListePersAngaben" data source where the "GeschäftsMail" field matches the email address from the "CO2DatenApp" data source. It then returns the value in the "PersonalNummer" field for that row.

Finally, you need to use the "MABilderApp" data source to get the picture associated with the employee number. You can use the formula below to do this:

LookUp(MABilderApp, IDMitarbeiter = Lookup(MitarbeiterListePersAngaben, GeschäftsMail = Email, PersonalNummer), Name)

This formula uses the "LookUp" function to find the row in the "MABilderApp" data source where the "IDMitarbeiter" field matches the employee number returned by the previous formula. It then returns the value in the "Name" field for that row, which should be the picture of the employee.

You can use this final formula in the "Items" property of your gallery control to display the picture of the employee.

[WarrenBelz]

Hi @Marc_Gutzwiller ,

Try this

With(
   {
      wEmpNo:
      Lookup(
         MitarbeiterListePersAngaben,
         GeschäftsMail = ThisItem.CreaterMail
      ).PersonalNummer
   },
   LookUp(
      MABilderApp,
      EmployeeNo = wEmpNo
   ).Name
)

@JohnM86  Strange that I got something very close to your response by entering this query in ChatGPT . . .

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Marc_Gutzwiller]

@WarrenBelz Thank you very much for your solution. It works technically fine and with a text field it gives me the correct name of the image file. but with an image it doesn't show me any image. what could be the reason? I also asked chatgtp 😉 and he suggested concatenate, but with the same result... 
thx for your support!

[WarrenBelz]

@Marc_Gutzwiller ,

Please do not rely on chatgpt at this point unless it is a simple query - I have experimented with a number of posts and it often (mostly really) leads off on the wrong path and particularly the code often also contains invalid references (like Concatenate with simply joins two text values in a single string). It also requires clear and concise questions to get a decent result. There are some people responding with it on the forum which often leads to this confusion.

But back to your question - I assume that is a SharePoint Library you are looking in (why use Name) or is it a List, in which case what type of field is Name ?

[Marc_Gutzwiller]

@WarrenBelz 
thanks for the tip, i already had this experience with my question here - and for a hobby coder like me without clean basics it becomes difficult to distinguish in which direction it should go...

yes, you are right, the images are in a sharepoint library.

[WarrenBelz · Accepted solution]

Hi @Marc_Gutzwiller ,

Assuming MABilderApp is your Library - this should work

With(
   {
      wEmpNo:
      Lookup(
         MitarbeiterListePersAngaben,
         GeschäftsMail = ThisItem.CreaterMail
      ).PersonalNummer
   },
   LookUp(
      MABilderApp,
      EmployeeNo = wEmpNo
   ).'{Thumbnail}'.Large
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Marc_Gutzwiller]

@WarrenBelz 
thank you verry much!