cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EricD
Frequent Visitor

Dependent Lookup issues

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)

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @EricD ,

This does not make a lot of sense to me as the same Lookup either in the Default of the field or in a Variable for a Patch has got to produce the same result, however try this (not ideal however)

SubmitForm(SharePointForm1);
Refresh(YourSharePointListName);
With(
   {
      vAgent:
      LookUp(
         EmpInfo, 
         ID='AgentID'.Text, 
         EmpName
      )
   },
   If(
      !IsBlank(vAgent),
      UpdateIf(
         YourSharePointListName,
         'AgentName'=SharePointForm1.LastSubmit.'AgentName',
         {'AgentName': vAgent}
      )
   );
   Refresh(YourSharePointListName)
)

 

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.

View solution in original post

22 REPLIES 22
CorbnDallas
Helper I
Helper I

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
Super User
Super User

@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.

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.

 

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.

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.

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

 

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.

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..

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,031)