Drop down list in canvas app to change data source

[MarkB1977]

Hi all.

I have created a simple canvas app from a SharePoint list.

I plan to create approximately 30 lists, all the columns will be formatted the same across the lists. The only thing to change will be the item entries which will differ between the sites.

I don't want to create 30 apps, one for each site, what I want to do is create a drop down box where the auditor / user selects the location (the location will be the list name). Once selected the app updates the item entries to reflect the list selected.

Is this possible, I am new to powerapps.

Tia.

[mdevaney]

@Drrickryp 
Yeah, those LOOKUP columns are great in SharePoint but a pain in PowerApps...  Ditto for Yes/No columns.

[MarkB1977]

Thank you

I will have a look at the info and see if I can apply it. 

My list has approx 60 items x 30 location which is 1800 entries.

Am I right in thinking there is a 500 limit  to filtering via a power app?

In terms of the 2 x lists, I think I'm getting confused.

Couldn't I populate a new list with the location name and assign a unique code. 

Then in the main list in the location column use 'choice' to select location and force an entry so its never blank.

Then use a filter in the app to display only the location code selected by the choice codes. 

I'm relatively new to SP so not sure if a choice column can be populated from a list?

Thank you. 

[MarkB1977]

@Drrickryp @mdevaney 

Thanks for the advice chaps.

I have looked at this again with fresh eyes and I am going with your method of combining and filtering.

So I have a List called 'Locations List' were the locations are listed in full  (I haven't assigned an ID I will just used the full office name)

The main database list has been renamed to encompass all sites and has a location column which must have an entry. I haven't used lookup in the choice selection, I just copied the list text in so it mirrors the locations list.

I have updated the data source due to the name change and updated the app as required.

I have created a front page with a dropdown box which grabs the list selection from the locations list. The idea being the user selects the location (from the dropdown box using the full office name) then clicks a button to proceed to the gallery view. (I have created this additional front page so users can still use the search box in gallery view but so they do not see the entries for other offices that are not applicable to them. 

The gallery should then filter the location column in the master list to display the selected office location entries.  This is where I am struggling. I cannot get the gallery to successfully filter the results based on the dropdown list selection.

Any tip chaps?

Thank you.

[Drrickryp]

Hi @MarkB1977 

Actually, you don't need to create an ID column, SharePoint does that automatically and the ID can be referenced in PowerApps. Otherwise, I think you are on the right path. If you want to check it out, put a label on your screen with the Text property Dropdown1.Selected.ID  FYI, A dropdown or combobox control references an entire record but you can choose which column to show in the window by clicking on your dropdown control and then selecting which column you want from the Value dropdown on the right hand column below the name of the control. If your dropdown and gallery are on different screens you can use the Set() function to create a global variable that you can use to filter the gallery as follows:

Set(offname, Dropdown1.Selected.OfficeName);Navigate(ScreenGallery,Fade)

In the gallery you would have the Items property as 

Filter(SPlist, location = offname)

In my example below, the gallery is filtered in its Items property by the dropdown control's selected city.  While using a name to filter your list will work fine in the application, overall best practices are to use the ID column instead of the actual names as the foreign key in One to Many relationships.  SharePoint indexes its lists using the automatically generated ID column and as the database grows, the response times will be much faster.   If you want to better understand how to build apps based on sound database principles, I suggest that you review my series starting with https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... 

[MarkB1977]

@Drrickryp 

Thank you for the help, I am still learning the language for PA to be fair.

Here is what I have so far. Below is the first screen where the user selects the location which will filter the SP List data in the gallery. The button just uses  Navigate(BrowseScreen1,ScreenTransition.Fade) to move on.

The screen below shows the dropbox1 selection. These are pulled from a separate SP List called Locations List

The screen below shows my poor formula which wont return the value, I have tried a number of variants.

I inserted another column into the SP list, because I felt the filter wasn't working on a Choice column (which I called Locations and the choices were copied as text not looked up values). For this i put a temp column in called Locations Test to see if it would filter based on a typed entry. But still I cant get it to filter the gallery by the drop box selection.

[Drrickryp]

Hi @MarkB1977 

Don't use SelectedText in your filter formula instead use Dropdown1.Selected.thenameofthenewlocationfieldinyoursharepointlist

Be aware that the text between the Location list column in your dropdown must match exactly the text in your SPlist location column. As another aside, best practices:  Avoid spaces in naming your lists.  I use what is called Camel case.  For example I would use  localPropertyAuditDatabase instead of what you are using.  

PS: If you had used LocationID as the foreign key in your original SharePoint list to refer to your locations, you probably wouldn't be having this problem.   

[MarkB1977]

Hi @Drrickryp 

This is what I am getting now.

[Drrickryp]

Hi @MarkB1977 

You are making this much more difficult that it needs to be.  Please review the link in my last post.  I suggest that you go back and redo your database.  Add a number column to the list and call it LocationID.  Use the ID column from your Locations list to find the ID.  Although it exists, it is hidden.  To unhide it go to the SharePoint list settings > create a view and on the right hand side there is a column that has all of the metadata in the list.  The ID box will be unchecked (it is by default), Check the ID box and move it up the list.  In your gallery's Items property, you can reference the ID from the dropdown and match it to the LocationID in your Audit properties database. 

"It is amazing that you can actually create a App with PowerApps without understanding the principles of relational database design.  What is not amazing is how inefficiently the app will function with respect to creating new records, retrieving accurate and usable information, updating existing records that need to be changed, and deleting the right records without removing other valuable information from the database. "

[MarkB1977]

@Drrickryp 

Will do. I have made changes to the titles as advised and will look at further improvements and see if I can get it to work.

I agree, it should be easy, its just how my mind processes what I want something to do.

Cheers.

[MarkB1977]

Hi @Drrickryp 

I have followed your advice, watched the videos and now have the BrowseGallery1 filtering by location ID from a selection page, thank you.

What I am now struggling with is how to filter the gallery further based on a text box entry.

Under BrowseGallery1 Items I have    Filter(LocalPropertyAuditDatabase,LocationID=Dropdown1.Selected.ID)

This works perfectly and is returning consistent results, however I would like to filter the gallery further..

Any ideas please?