GroupBy Filter not always showing all items from collection

[Anonymous]

I have a Power App in development where I have a couple of Dropdowns that get data from a SharePoint list.
When selecting some of the "Function" items, I get all Process assoicated with that Function.
But for a couple of the Functions I only get the first Process.

Information... 
calfunctionvalue is a calcualted (Single Text field) of the Function (Choice Field)
ndworkplan is the sharepoint list

Here are the formulas I have in the App:

Screen - OnVisible:
Collect(WorkPlanFunc,ndworkplan)

1st dropdown(Function):
Dropdown_Function Formula:
GroupBy(WorkPlanFunc.calfunctionvalue,"calfunctionvalue","itemsbycalfunctionvalue")

2nd Dropdown(Process):
Dropdown_Process Formula:
Distinct(Filter(WorkPlanFunc,calfunctionvalue=Dropdown_Function.Selected.calfunctionvalue),Process)

When selecting "Product Development" as the Function, I see all Process items assigned to that function:

When selecting "Employee Experience" I only get the first Process:

As you can see below, from the SharePoint list other processes are assigned to that function:

As I said, this issue only shows for 2 of the 22 Functions.. 

Not sure if there is a limit, even when using a Collection but..

The Product Development Function has 182 Items associated with it, Employee Expereince has 196.

[yashag2255]

Hi @Anonymous 

Can you please confirm the number of items in your SharePoint list? I am guessing that this is greater than 500 and due to delegation your app is ot able to read more than 500 records. If your number of items is less than 2000. Can you try to update a setting:

App Settings -> Advanced Settings -> Data Row Limit for non-delegable queries -> Set this value to 2000

This will fetch 2000 records from SharePoint at a single time.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[RandyHayes]

@Anonymous 

If I understand your goal here, you want the first DropDown to be a list of distinct functions and the second DropDOwn list to be the filtered (by the first DropDown) list of Process from your SharePoint List.

I'm not sure why you have a need for a calculated column for the Function column. You might be able to simplify and remove that if not needed.

However, If the above is the case, then consider changing the formulas to the following:

Screen

   OnVisible - remove the formula from there - not needed

1st DropDown

   Items Property:  GroupBy(ndworkplan, "calfunctionvalue", "itemsbycalfunctionvalue")

2nd DropDown

   Items Property:  DropDown1.Selected.itemsbycalfunctionvalue

Replace DropDown1 with the name of your 1st DropDown control.

This should give you what you are looking for.

I hope this is helpful for you.

[Anonymous]

@yashag2255  Thanks for the reply.. I had checked this previously.. The Total number of items is a little over 1500 and I've set the Data row limit to 2000 with no change.

As I said, it is strange that this is only affecting two of the 22 Functions. 

[Anonymous]

@RandyHayes   Thanks for the reply..

I created the Calculated column as I was intially having some issues with the first dropdown and read about issues using a Choice Field so figured "better safe then sorry" by eliminating that potential issue.

I did the OnVisible Collection as I was wondering if the list size of 1500+ Items was causing an issue. So figured I'd create a colelction of the data to work with. Either way, uisng the Collect, or not, gave the same results.

I did set the first dropdown back to using the orginal formula I had, which you listed :GroupBy(ndworkplan, "calfunctionvalue", "itemsbycalfunctionvalue")

But using your formula for the 2nd dropdown( Dropdown_Function.Selected.itemsbycalfunctionvalue) gives no results in the 2nd dropdown.

[RandyHayes]

@Anonymous 

So, check your record limits as well as @yashag2255 mentioned if you have a list that is that size.  You can set it to a max of 2000 items.  If your list is les than 2000, this should resolve any of those issues and you should see values in your dropdowns.

Also, you probably already did, but check that you have the proper column showing in your second dropdown.

[Anonymous]

@RandyHayes 

Thanks.. I've triple checked the Data row limit ( set at 2,000 and have 1,500 items) and the Column for the 2nd dropdown is correct..
using this formula: 
Distinct(Filter(WorkPlanFunc,calfunctionvalue=Dropdown_Function.Selected.calfunctionvalue),Process)
does bring back the processes for 20 of the 22 funtions correctly.
Only 2 functions fail to return all processes.

BTW.. the reason I started using the collect for "OnVisible" is that if I try to set the dropdown to "ndworkplan" rather then my collection, I get this warning:

Delegation warning. the highlighted part of the formual might not work correctly with column "calfunctionvalue" on large data sets.

[RandyHayes]

@Anonymous 

Yes, the delgation warning will be there because you are using a non delgable function.  However, this is only a warning and if you know that your record limit is high enough to account for the total list size, then you can just ignore it.

The original formula I gave you should be working perfectly.  If it is not, then I would go back to that level and see what is going on.  In particular, there is some issue with the GroupBy that is not grouping the way that you expect.

I don't know if you have additional columns in your list or if your Process column has duplicate values (as I see you are using distinct).  If that is the case, then I could see where issues might come in.  

But, if your list is only Function and Process columns and they are both Text columns, then you should have no issue.

I am somewhat focused on the fact that you put a "Distinct" function in there and got values.  This makes me zero in on the Value property of the DropDown control, as it means you are changing it, or it changed to "Result" rather than "Value" or, in your case "Process".

So, if the original formula is not working, then again, I'd try to resolve why that is not, because it should (and does) work just fine.

[Anonymous]

@RandyHayes   This list has many columns and multiple entries for each Function and processes. Maybe a quick reasoning might help 😉

I took this over from someone that left and I'm trying to make it easier for users to work with existing data and update as needed.

Here is an example of what the fuction "Product Development" looks like in the SP list:

As you can see, quite a mess.
So, my idea was to present them a PowerApp view that "filters" data by first the Function, then the Process.
The reason for the 2nd "Filter" is that, in the case of Product Development Fuction, there are 182 items.
So, what I hoped to do is use the 2nd dropdown to filter down to the process of the function. Then in the gallery show just those processes. The user could click a "Milestone" and see the task assoicaiated with it in the 2nd gallery, and then a 3rd gallery to view the task information..
Here is what I have so far which shows the Milestone and how many tasks are associated to it( just a blank 2nd gallery right now):

While fighting the 2nd dropdown battle, I've also been trying to figure out how to get the 1st Gallery to display the items by the Process Dropdown.
As seen above, currently the Formula for my gallery is for the 1st dropdown(Fuction)... so I also need to figure out how to use the "Process" dropdown for that gallery.

While I fight the 2nd dropdown battle, maybe you can also help with the Gallery formula??

The current gallery formula is:
GroupBy(Filter(WorkPlanFunc,Dropdown_Function.Selected.calfunctionvalue = calfunctionvalue),"Milestone_x0020_Name","ItemsByMileStoneName") which works, but not what I want..

I'd like to change the gallery to view items based on the 2nd dropdown "DropDown_Process".. but just changing the name in the formula isn't working.

I am "way" over my head on this as up till now my PowerApp creations have mainly been Customized form and I've not had any "official" PowerApps training.. yet... 

[RandyHayes]

@Anonymous 

Ah yes...the clearer picture is worth a thousand words!

Let me ask this before I give a formula that may not work... What kind of columns are Function, Process and Subprocess?  Are they Text columns or Choice columns?