ClearCollect table from SharePoint Choice and Person columns

[Anonymous]

Hi,

I have a SharePoint List [@Records] that is over 30,000 entries

• 6 columns are the "choice" type (ch1, ch2, ch3, ch4, ch5, ch6)
• 1 column is a "person" type (pn1)

The goal is to have 7 cascading dropdowns (one for each column mentioned above) to delegate the gallery. So I'm trying to ClearCollect the table that would collect every choice combination (around 400 possible combinations) and build the cascading dropdown from it. ClearCollect table I'm trying to get would look like this:

ch1	ch2	ch3	ch4	ch5	ch6	pn1

Unfortunately my formula is not doing it:

ClearCollect(colTable,AddColumns(Records,"ch1", Choices(Records.ch1),"ch2",Choices(Records.ch2),"ch3",Choices(Records.ch3),"ch4",Choices(Records.ch4),"ch5",Choices(Records.ch5),"ch6",Choices(Records.ch6),"pn1",Choices(Records.pn1.DisplayName)))

Any advise is extremely appreciated! 

[RandyHayes]

@Anonymous 

It is not clear why you would want to collect these in a collection.  If you have individual dropdowns for each column in order to filter your records, then each dropdown should have its own Items property based on Choices.

Ex. the CH1 dropdown Items property would be : Choices(Records.ch1)

Then all the other dropdowns would have the same formula except for their corresponding column.

I hope this is helpful for you.

[Anonymous]

@RandyHayes 

Thanks for your response! 

That's what I have at the moment as a workaround Sort(Choices(Records.ch1),Value) because I also have them sorted A-Z. The problem is that I'm unable to have the "cascading flow" of my dropdowns with this formula. Basically I'd want the options within "ch2" dropdown to adjust based on "ch1" dropdown selection.

I also tried Filter(Choices(Records.ch2),Value in Filter(AddColumns(Records,"T1",ch2.Value), ch1.Value=Dropdown_Ch1.Selected.Value).T1) Items property in my Dropdown_Ch2 to have the cascading flow, but it only works on smaller dataset and it only returns fraction of values with this huge dataset

[RandyHayes]

@Anonymous 

Well your first issue with that is you are using the AddColumns function - this is not a delegable function and will only deal with the maximum number of records that you have set in your advanced settings.

I understood that you wanted these dropdowns to assist the user in narrowing down the rows in the resultant Gallery list.  So, I am not exactly understanding your cascading flow in this situation.  Can you elaborate on how you see this playing out?

[Anonymous]

@RandyHayes 

Thanks for heads up about the AddColumns function not being delegable! It worked great in my other App but clearly in this case the big data is the issue. 

The dropdown flow would be (in that order): 

• Region
• District
• State
• City
• Store
• Department
• Manager

A user would select the Region, and that would filter all the Districts available for that Region. Then once the District is selected that should filter the next dropdown that contains all the States available within that District.. And so until the last dropdown with the Manager is selected. Then a Gallery is shown with all the records filtered by these 7 dropdowns. 

The Gallery being filtered by these 7 dropdowns is ready and works great. It's just the cascading part that I'm really scrambling with. 

So I'm trying to have a ClearCollect colTable that would collect (and by the way hopefully it's possible) all these columns choices (with no duplicate rows) in a table format similar to the table below: 

ch1	ch2	ch3	ch4	ch5	ch6	pn1
West	5	OR	Eugene	DS23032	Dept A	John Doe
West	5	OR	Portland	DS23007	Dept C	Jane Doe
East	1	NY	Buffalo	DS01576	Dept A	Josh Doe

Then I'd have a table with approximately 400 rows to work with, and that shouldn't be a problem for delegation.

Hope that answers your question!