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)

[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]

Thank you so much, that worked very well. Just need to add a coma after ID.

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

 Where is the best place to put the statement? I tried several spots  on the ID card (where agents enter the id that is being looked up): using OnChange and On Select, but that only worked when I saved and reopened it. DelayedOutput does not work either.. .

[WarrenBelz]

Thanks @EricD ,

Dangers of free-typing on Notepad - I have fixed now.

Firstly, I would put it on the OnChange of the AgentID text box as this event should not trigger until the Enter key is pressed or the user clicks away from the field. You should not need DelayOutput on the Control, I do not use it but this post explains it fairly well.

If you really want to be sure you are writing a valid value, try this

With(
   {
      vAgent:
      LookUp(
         EmpInfo, 
         ID='AgentID'.Text, 
         EmpName
      )
   },
   If(
      !IsBlank(vAgent),
      UpdateIf(
         YourSharePointListName,
         ID=SharePointIntegration.Selected.ID,
         {'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.

[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]

thank you so much for all of your help. needed to change it to:

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

 without the change it didnt update at all

Interestingly enough, with the change, it is actually writing to the previous row, agent name column, in the SharePoint list with the current data (not the correct data). Tried removing the refresh statement, it did not change it.

[WarrenBelz]

Thanks @EricD ,

I was more showing syntax to guide you and cannot see your data structure.

Can you please mark as Accept as Solution so others with the same question can find it more readily. It also closes the item.

[EricD]

Any idea how to get it to update the name to current record  instead of the previous record? for some reason the agent name is getting wrote to the wrong row 

example:

agent ID1    Agent Name2

Agent ID2   (blank)  

instead of the desired

agent ID1    Agent Name1

agent ID2    Agent Name2

[WarrenBelz]

@EricD ,

Are you creating a new record and then expecting to update that record before going back to SharePoint?

It is also probably why my code did not work as ID is the most reliable way of correctly identifying the record last selected in SharePoint.

[EricD]

so the problem is when writing the Name it is adding it to the previous row, rather than the current row.

I believe it is doing this because the current form has not been submitted. So to SharePoint, there is not the new row yet. So it uses the existing row instead of writing everything together on the new line

I tried moving this to the "OnClose"  portion of the SharePoint integration by doing:

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

this did not write anything back however.

[WarrenBelz]

Hi @EricD ,

That code is for a completely different issue.

If you are after ID of item after submit you need 

SahrePointForm1.LastSubmit.ID

however, why don't you simply do not put a field on the form for AgentName with the Default

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

and it will write the data the the list on SubmitForm?

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.