Help Building it Right

[Anonymous]

Its been 6 months since building my first successful app. I'm now poised to build a second app that is only a reference app (lookup - no form or data entry) built from SharePoint lists(s). But, unlike the first app, this data has (I believe) nested data? I'm unsure how to build the data correctly for proper filtering. Right now I'm considering the following structure:

Column 'A': "Logo" (hyperlink or image)

Column 'B': "Brand" (single line)

Column 'C': "Link" (hyperlink)

Column 'D': "Scope" (two choices, multiple possible, Choice)

Column 'E': "Category" (nine choices, multiple possible, Lookup) [This would be a separate List, containing only the nine categories.]

Column 'F': "Type" (many, multiple possible, Lookup) [as this data is a sub of "Category" this would be a separate list with a Lookup column to "the Category" list the type belongs to.

Column 'G': "keywords" (single line)

Columns D to F will be used to filter the data (columns A-C) to the user through a series of checkboxes, or the user can searching via text box as is typical, where the keywords will help narrow results. Thus, the user can type in a word or narrow the results via the checkboxes, or both. 

Is the structure of columns 'E' and 'F' correct given this scenario? Having three different Sharepoint Lists (two as a Lookup into the third) the proper way to build this data for PowerApps? 

[RandyHayes]

@Anonymous 

This will somewhat depend on your use of the lists.  If you are only concerned with the presentation of data from the list in your app and no one else will be interacting with the lists other then your app, then I would skip all the Choice columns and Lookup columns and lean toward text columns.  These are a little easier to work with in PowerApps.

As for the variable nature of the information in your Lookup and Choice columns, consider having a separate list for each (i.e. Category and Type) and then just use those lists to "feed" your primary list.  This, again, will be dependent on your list needs and use.

Your bigger issue will come with column A - if you are using a purely hyperlink or image type definition is SharePoint, then you can work with this.  If you are using the modern interface "image" type, you will run into issues.

It appears that the primary purpose of the list is to have an image and metadata - I would lean toward using a Document Library instead for this list.

I hope this is helpful for you.

[LRVinNC]

You wouldn't need 3 lists, just two.  Category and Type can be in a single list.  Your app can lookup unique category values in one column of that list and unique type values within the category from a second column of the same list.  

You can use the Distinct function to eliminate duplicate category values and provide only the unique values for Category in your app for the user to select from for filtering.  You can do the same for Type.  Use dependent dropdowns to limit the Type within the selected Category.  See my recent post for an example of dependent dropdowns:  https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-dropdown-choices-based-on-previous-in...

[Anonymous]

@RandyHayes I'll have a student worker inputting content and building up the data, so I'd prefer to have the choices identified in which they choose from predetermined values. I've run into the image issue previously in the last app and successfully resolved it with no problem. 

@LRVinNC if I understand correctly, make one SharePoint list, let's call it "Type_Category" with two columns: "Product_Type" as a single line of text with the more unique values, and "Product_Category" as a single line of text, with the nine choices, but still entered as a single line of text (not a Choice column type) exactly matching for each. Then in the SharePoint list that will feed the app, let's call it "SourceList", bring the Type_Category list in as a lookup, using the Product_Type column, allowing multiple, adding the Product_Category column to show the additional field?

See attached screenshots. I should not worry about the category duplicates because the app will filter correctly using the distinct function.

IF I wanted to show the Category in the gallery as the sub-title, is there a way to use the distinct function there as well? I ask because I recall running into an issue getting a gallery to show items from a lookup/choice column before. 

[RandyHayes]

@Anonymous 

I'm a little confused - you seem to have already worked out how you want to go about your app data.  I mentioned some general ways to work around the issues you will experience with lookups, choices and other complex column types in PowerApps.

But your questions now seem a little more related to functions and formulas to use to work with the data.

Your assumption is correct that you will not be able to use the Distinct function with the Lookup column types properly without going through additional steps.

My suggestion of the additional lists are meant to flatten your list types to text, which is easier to deal with in PowerApps.  That is not to say that you do not have predetermined values.  In fact, PowerApps was built to make that very easy.  For example, if you use an Edit form for your list (assuming text type for Category and a separate list with the Categories in it), you can simply change the control type in the Form to be an "Allowed Values" control and then set the Allowed Values to be that Categories List....all done!

Forms have become more accommodating to Lookups and Choices, but you need to go with the flow with them and not try to make them do something they are not.  But, I say "keep is super simple" the more complex you try to do your formulas (not that your app can't be complex, but the more you try to "develop" in a no-code platform) the more difficult it will be.  PowerApps likes simplicity as well...all the way down to the data.

So, if you are asking for general guidance on setting the data sources for your new app, then the general guidance that myself and @LRVinNC gave should give you some options to get that underway.

If you have specifics on that, or if you have a question on a formula you are working out, please post back with that and we'll get you to that next step.

[Anonymous]

@RandyHayes as mentioned, a student will be entering the data (from the backend via Sharepoint) once everything is set up. As the original post states, there will be no form or way to edit the data via the app (by design) so I'm not sure why you are suggesting that be added. The question is ensuring I have the data from SharePoint built right as I found myself deleting and moving columns a bit to get the structure situated when building the last app, but it didn't have nested data and this one does. I'm asking in the last post if the proposed structure described and in the screenshots is was what was meant by @LRVinNC in their suggestion.  Thank you.

[RandyHayes]

@Anonymous 

So the key is this statement - "(from the backend via SharePoint)"

In my opening response I mentioned :

This will somewhat depend on your use of the lists.  If you are only concerned with the presentation of data from the list in your app and no one else will be interacting with the lists other then your app, then I would skip all the Choice columns and Lookup columns and lean toward text columns. 

So, if you are going to do it through SharePoint entry, then the use of Lookups and Choices is more essential.  As just a personal preference, when confronted with similar scenarios, I opt to do an App to do entry to SharePoint rather than interact with it directly.  Most find a more polished app a little easier to work with than the SharePoint interface.  My opening statement was sort of biased toward that point.

So, if your goal is to provide an easier to use experience directly in SharePoint, then the complex columns will be needed as they are there for that reason (among others).  Since your Type is reliant on the Category, and your old option in SharePoint is to "bring over" one of the looked up columns (i.e. there is no logic of filtering a lookup from another), you will need to have the Type and Category options all in your looked up list (Type_Category), but you already seem to have this down as your screen shots show your category and types in that list and your primary list appears to have the looked up type and "brought over" category already.

This is all very usable in PowerApps for your "lookup" app, but it will be a pain to deal with the multiple selected (as it appears you allow multiple type selections in your list), but it is able to be done.

Where I would still consider (because I try to lean toward the simple) it to get rid of the lookups and choices and then build an simple entry app for the student to use to get the data in.  However, this also creates an additional list (after looking closer at your screenshots) in that you allow multiple selections in the lookup.  To provide that, you would need another cross-reference list...not the end of the world, but another extra layer.

My only suggestion beyond this is to evaluate first what you want your final app to look like.  Working backward from that you can assess the complexity of designing the app to provide that result and then weight that against the complexity of shifting to either a simpler front-end (i.e. build small entry app for student) vs. retain that complexity in the datasource and adjust accordingly in the app.  Not knowing your end product the way you do, I can only offer suggestions based on years of experience and hundreds of apps - but even then, "your mileage may vary"!

If you have a mock up of the end app to share, that might give a little more advice. 

[Anonymous]

@RandyHayes 

The student entering data is very familiar with SharePoint, having entered many items that feed our other app, thus "training" them to use an app form is not desired, plus they need to save the logo a particular way on the site, before attaching it to the list, and I don't want other students getting the idea they can edit the app or its data. Hence, no forms or editing via the app, not even a hint of it. 

Attached is a mock-up, did that first. Please note that the data displayed is junk pulled in just to play around with the layout, the title and sub-title are not set up, but essentially the gallery results are filtered by the options above:

• The Scope checkboxes will default to checked to show all records, only narrowing the results by unchecking one of them (and I assume none if both are unchecked - which is fine).
• Search should filter all columns, this is how we did the last app, and is working well.
• The dropdowns as indicated will default as blank, allowing all records unless a selection is made, and are more for advanced searching or browsing the categories and types without using the search bar. I may have to add a third, thus Categories>Type>Sub-type, so just imagine the third drop-down, but I'm trying to avoid a third.

The previous app (and another - so I guess this makes this app number three) had to deal with multiples, and that does seem to be a topic around here of some note. I have memories of .Value and can problem solve the filter command later I imagine, so long as the SharePoint List set up, as previously outlined, will get me there?   

[RandyHayes]

@Anonymous 

Cool - this is all fine!

You are stating that Search will be used in this app, so I am going to assume that delegation/record limit is not an issue and that your list will not exceed 2000 records.

What you will want to get up to speed on (if not already) is the AddColumns function.

With the AddColumns, you can work with the Lookup columns a little better.  So, for example, if you want to be able to search a lookup column, you can craft a formula such as this:

Search(
    AddColumns(
        Filter(yourList, criteriaBasedOnCheckBoxesHere),
        "lookupValue", yourLookupColumn.Value
    ),
    yourTextInputControl.Text,
    "lookupValue"
)

This will give you the ability to search on single value lookup columns.

If you need to consider multi-select lookup columns, then you can utilize something like this:

Search(
    AddColumns(
        Filter(yourList, criteriaBasedOnCheckBoxesHere),
        "lookupValue", Concat(yourLookupColumn, Value & "|")
    ),
    yourTextInputControl.Text,
    "lookupValue"
)

This will concat the values of all the lookup items (bar separated - not important the separator) and that will then be searched.

You will want to set the Category dropdown to Distinct off of the looked up list (Category_List) and then have your Type dropdown filter the Category_List based on the selected Category in the Category dropdown (I believe that was the order of your lookup list).  

So...it's a couple extra steps to keep in mind, but it is generally straightforward.

[RandyHayes]

@Anonymous 

How are you doing on this?  Still working through it or past it?