Dependent Lookup issues

[EricD]

Thank you for any help: This is my first post and first Power App so please be gentle...

I have a Power App that is connected to a SharePoint list, RequestForm. I have a field where an Agent's ID is typed in. I am looking for to use  AgentID to look up the agent's name and other information from a different SharePoint list, EmpInfo. I am looking to display the retrieved name in the Power App and also save  to the list RequestForm. the PopwerApp is run directly off of the SharePoint list through SharePoint Integration and is not using collections.

I have tried multiple approaches:

1:  setting a default value for AgentName field: it would pull the Name in preview mode but not when published (it would stay blank and would not update SharePoint).

2: Setting an OnChange on the AgentID field: UpdateContext({'AgentName': LookUp(EmpInfo, ID='AgentID' , EmpName)}) or 'AgentName'=LookUp(EmpInfo, ID='AgentName', Empname)

Any help is appreciated.

An additional, related task: I want to take the AgentID and populate additional columns in RequestForm without displaying them in Power App (like the agent's supervisors email)

[CorbnDallas]

Hey Eric,

I think you want to take a look at the Office 365 Users connection:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-office365-users

Here's a great reference video from PowerApps911 too. 🙂

https://www.youtube.com/watch?v=QhGV4AhCFRc

[WarrenBelz]

@EricD ,

This has nothing to do with the Office365Users connection, so please disregard this.

You are simply trying to lookup a matching value from another List, however you cannot have Or() in a Lookup - you have to specify what you want as Lookup delivers a single value, although your description suggests you are matching on ID as below.

UpdateContext(
   {
     'AgentName': 
      LookUp(
         EmpInfo, 
         ID='AgentID', 
         EmpName
      )
   }
)

Your second question would require a Filtered collection with syntax similar to the above.

ClearCollect(
   colWhatever,
   Filter(
      EmpInfo, 
       ID='AgentID', 
   )
)

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.

[EricD]

Thank you @WarrenBelz  for your reply, I apologize for the confusion. The OR was to indicate I tried 2 different approaches when trying to use the OnChange function.

Approach 1:    UpdateContext({'AgentName': LookUp(EmpInfo, ID='AgentID' , EmpName)}) 

Approach 2:   'AgentName'=LookUp(EmpInfo, ID='AgentName', Empname)

It was not meant to be 1 statement. Sorry for not being clear.

when I use:

UpdateContext(
   {
     'AgentName': 
      LookUp(
         EmpInfo, 
         ID='AgentID', 
         EmpName
      )
   }
)

as a function for OnChange I get the "This formula has side effects and cannot be evaluated ." error message and the AgentName Field remains blank.

[WarrenBelz]

Hi @EricD ,

That is a weird error that I have not seen before - I found one reference to it here.

One thing - you do do need the ' quotes on a Variable

UpdateContext(
   {
     AgentName: 
      LookUp(
         EmpInfo, 
         ID='AgentID', 
         EmpName
      )
   }
)

Also try

UpdateContext(
   {
     AgentName: 
      LookUp(
         EmpInfo, 
         ID='AgentID'
      ).EmpName
   }
)

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.

[EricD]

Thank you for your response and the links.

I've tried both of the posted.  I get the same error

from you link, it mentioned changing variables, I have tried that as well, same error.

[WarrenBelz]

Hi @EricD ,

Going back a couple of posts I have now noticed this comment of yours and the AgentName Field remains blank.

You are only setting a variable (local) with the code - this will not update any field unless it is a label with the Variable name on it.

Also, put a label on the screen and put this in the Text property

LookUp(
   EmpInfo, 
   ID='AgentID', 
   EmpName
)
OR
LookUp(
   EmpInfo, 
   ID='AgentID'
).EmpName

also try removing the quotes around AgentID

[WarrenBelz]

Hi @EricD ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

[EricD]

Greetings,

  I'm getting closer.. baby steps...

I got it to display,  the . Text was missing. Working on getting it to save the value now.

UpdateContext(
   {
     'AgentName': 
      LookUp(
         EmpInfo, 
         ID='AgentID'.Text, 
         EmpName
      )
   }
)

 However, now I'm trying to get it to save the data with the rest of the form (data card). It uses SharePointIntegration (it was created off of a SharePoint list) so not sure how that works with Patch, since I'm not using a collection..

[WarrenBelz]

Thanks @EricD ,

I was assuming AgentID was a Variable - if a Text control, then yes, you need to reference the content with the correct syntax.

You are still only setting a Context (local screen) Variable here - how are you intending to save this value to the data?

An UpdateIf technically should work

UpdateIf(
   YourSharePointListName,
   ID=SharePointIntegration.Selected.ID,
   {
     'AgentName': 
      LookUp(
         EmpInfo, 
         ID='AgentID'.Text, 
         EmpName
      )
   }
)

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.