cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris61
Helper II
Helper II

Filter DataTable based on DataField

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

1 ACCEPTED SOLUTION

Accepted Solutions

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}});

View solution in original post

3 REPLIES 3
chris61
Helper II
Helper II

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.

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}});

View solution in original post

v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (46,688)