Filter DataTable based on DataField

[chris61]

Hello,

We have 2 Sharepoint lists:

List A contains projects. There is facility to select a 'Path' for each proejct e.g. 1-Start, 2-Progress, 3-Review etc. so for example there could be several projects assigned with '1-Start' or '2-Progress' or '3-Review'.

List B contains time records booked against Projects from List A. In PowerApps the user selects which project they are working on and it populates List B.

In PowerApps users click to add a new record. This opens a new screen with an EditForm, users fill in fields from List B to create a new record. One of the fields is a dropdown with List A Projects - however all of them are listed.

What we would like to do is filter the available dropdown options in PowerApps so that the user sees Projects at 1-Start and 2-Progress but not the projects at '3-Review'.

[chris61]

Something of an update:

The new form in PowerApps has a dropdown for the Project for the user to select. This points at List 1 currently with a filter e.g.:

Filter(List1, Project_x0020_Path.Value="2-Progress").Title

Excepting the fact this produces a delegation warning (which can be got around by using a collection) this is returning "2-Progress" records. When the form is submitted using "SubmitForm" function Sharepoint does not pick up the Project selected when the new record is created in List 2.

I cannot see a way to get the record ID for the Project selected.

[chris61 · Accepted solution]

Figured this out:

Create a collection in PowerApps of the items from the List A showing only the fields you need using ShowColumns.

Point the dropdown field of the Sharepoint lookup on the form to a filtered version of the collection.

Instead of SubmitForm use Patch to patch all of the required fields to Sharepoint.

When it  comes to the LookUp/Dropdown field use the following:

Project:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp(Projects',Title=First(Dropdown1.SelectedItems).Title,ID),
Value:First(Dropdown1.SelectedItems).Title}});

[v-xida-msft]

Hi @chris61 ,

Could you please share a bit more about the dropdown field (with List A Projects) in your Edit form? Which column does it connect to in your SP List 2? A LookUp column?

Further, could you please share a bit more about the formula within the Update property of the Project data card which contains the dropdown field in your Edit form?

I assume that the dropdown field (with List A Projects) that you mentioned in your Edit form is a LookUp type column in your SP List 2, which references values from the Title column in your List 1, is it true?

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the dropdown field control to following:

Filter(List1, Project_x0020_Path.Value = "2-Progress").Title

Set the Update property of the corresponding Data card which contains above dropdown field to following:

{
  Id: LookUp(List1, Title = ComboBox1.Selected.Value, ID),  /* <-- ComboBox1 represents the dropdown field that you mentioned */
  Value: ComboBox1.Selected.Value
}

Above formula may cause a Delegation warning issue, in order to fix this issue, please take a try with the following workaround:

Set the OnVisible property fo the first screen of your app to following:

ClearCollect(RecordsCollection, List1)

Set the Items property of the dropdown field control to following:

Filter(RecordsCollection, Project_x0020_Path.Value = "2-Progress").Title

Set the Update property of the corresponding Data card which contains above dropdown field to following:

{
  Id: LookUp(List1, Title = ComboBox1.Selected.Value, ID),  /* <-- ComboBox1 represents the dropdown field that you mentioned */
  Value: ComboBox1.Selected.Value
}

In addition, if you want to display Projects at 1-Start and 2-Progress but not the projects at '3-Review' within the dropdown field, please set the Items proeprty of the dropdown field control to following:

Filter(RecordsCollection, Project_x0020_Path.Value = "1-Start" || Project_x0020_Path.Value = "2-Progress").Title

Please also check and see if my response within the following threads would help in your scenario:

https://powerusers.microsoft.com/t5/Expressions-and-Formulas/Submit-form-not-working-with-choices-va...

Best regards,