SharePoint & PowerApps Forms … Auto fill list field by retrieving Data from 2nd list

2
votes

I have 2 lists in SharePoint.... List 1: Request List & List 2: Approver List.

User selects new and PowerApps form displays where the employee enters his request.

Request List fields...
Leave Date(Date field)
Employee(Person field)
Department (Text)
Approver (Person field "AutoFill")*

* Once employee enters their name in the Request form I want the form to lookup his Approver in List 2 and then prefill the Approver field.

Approver List fields.....
Employee (Person field)
Approving Manager (Person field)
Department (Text)


Can someone suggest a good way to make this work. Lookups and Cascading lookups not the solution.

Thanks for reading my post... any help is appreciated.

Carlos... Here are some screen shots of the configuration....

PowerApps setup

Effects after adding suggested code

1
powerapps

1 Answers

1
votes

A lookup is a solution for this, but in PowerApps, not in SharePoint. Here's how you would go about implementing this:

  • On the RequestList list on SharePoint, select the "PowerApps -> Customize forms" option that will bring you to the form in the PowerApps Studio (web editor).
  • Add a new data source (in the ribbon, View -> Data sources -> Add data source), that references the ApproverList list on the same SharePoint site
  • Select the card that represents the approver field in the list (it will likely be called Approver_DataCard1 or something similar)
  • In the right-side pane, select the 'Advanced' tab, and click the padlock that says "Unlock to change properties"

Now we can start updating the properties that will perform the lookup. Click the "More options" button to show all properties of the card, and search for Default. Set its value to

If
    IsBlank(DataCardValue3.Selected),
    Blank(),
    LookUp(
        ApproverList,
        Employee.Email = DataCardValue3.Selected.Email,
        ApprovingManager))

Where DataCardValue3 is the name of the control (a combo box control) that is used to select the employee for who the request is being made.

Now every time the employee is changed, it will look at the ApproverList on SharePoint for an employee whose e-mail is the same as the one for the selected employee.

If you don't want to give the form user the option to override the default approver, you can also update the DisplayMode property of the data card for the approver to DisplayMode.View so that it will only show the approver to the user, without letting them change it.

Hope this helps!