Cascading dropdowns in gallery, added from collection

[KalvisT]

Hei,

I'm once again asking for your support.

So i have run into a problem and I can't find a fix for it. 

What I want to accomplish is that a user chooses a value from dropdown Seksjon, the next one is a cascading dropdown and the user must choose a value for Avdeling. Then a third is a combobox with multiple values. For the combobox Tjeneste, DefaultSelectedItems and Items property is the same so that the values in combobox are dependant from previous dropdown and all of them are selected. 

Next step is to choose a date, which usually is default selected to Today() and another dropdown Nadd SLA with 3 hardcoded values. 

After it I have button Legg til liste to create a collection and add it all to a gallery. 

After it is added to the gallery I want that the user can edit Nadd SLA dropdown and Tjeneste combobox/dropdown. (I dont know if there is a difference if I use dropdown or combobox for that value in gallery since i dont need multiple selections there)

#1 The problem I run into is that Tjeneste dropdown in gallery shows the correct amount of values that can be found in the combobox Tjeneste, but it shows only the value name from dropdown Avdeling. How can I make it so that dropdown in gallery allows to choose from all the values from Tjeneste combobox? 

#2 I would also like that Nadd SLA dropdown in gallery could be edited to choose the right color. 

Gallery is connected to a collection which is made after pressing the Legg til liste button. Is it possible to edit the values in gallery which are collected from a collection? 

The functions I use for Dropdowns and Combobox which are not in the gallery:

Seksjon - Distinct('CBO Tjenesteoversikt',Seksjon.Value) - ddSeksjon

Avdeling - Distinct(Filter('CBO Tjenesteoversikt', Seksjon.Value = ddSeksjon.Selected.Result),Avdeling) -  ddAvdeling

Tjeneste - Filter('CBO Tjenesteoversikt', Avdeling = ddAvdeling.Selected.Result) – DefaultSelectedItems Filter('CBO Tjenesteoversikt', Avdeling = ddAvdeling.Selected.Result) - cbTjeneste 

Dato – DefaultDate - Today() – Date

Nådd SLA? - ["Grønt","Gult", "Rødt"], - ddSlaN

The Legg til liste button OnSelect - 

ForAll(cbTjeneste.SelectedItems,Collect(colKT,{seksjon:ddSeksjon.SelectedText, avdeling:ddAvdeling.SelectedText, tjeneste:cbTjeneste.SelectedItems,dato:Date.SelectedDate,sla:ddSlaN.SelectedText}))

And The Gallery items:

gcbTjeneste - tjeneste

gDato - dato

gddSLA - sla

gddAvdeling - avdeling

gddSeksjon- seksjon

  

[RandyHayes]

@KalvisT 

To cascade the dropdowns it is more effective to use GroupBy.

So, your ddSeksjon Items property would be: 

GroupBy(
    AddColumns('CBO Tjenesteoversikt',
        "_seksjonValue", Seksjon.Value
    ),
    "_seksjonValue", "_avdelings"
)

Your Items property on ddAvdeling would be:

GroupBy(ddSeksjon.Selected._avdelings, 
    "Avdeling", "_Tjenester"
)

The Items property of the Tjeneste control would be:

ddAvdeling.Selected._Tjenester

The DefaultSelectedItems property of that control would be the same as the Items if you want all of them selected.

Your ForAll function is written backward and is not needed unless there is some absolute reason you need a collection for what you already have a colletion/table of in the combobox.

Can you explain more about your requirement for #2 - the Nadd SLA dropdown?

I hope this is helpful for you.

[KalvisT]

Well the idea behind the collection is that there are people that have to use this app daily and submit SLA reports. Some departments "Avdeling" have a lot of products "Tjenester". They would choose Seksjon, Avdeling and then click a button to add all the items to the collection and gallery. And after that they could edit Tjenester and SLA in the gallery before submitting the collection to sharepoint. 

The SLA dropdown would be 3 colors. Green, yellow and red. It'd be necessary to edit those 2 controls in the gallery. Is there a way to get this done? 

Now the controls dont recognize the items from collection and ThisItem.seksjon shows error : This fomula uses scope which is not presently supported for evaluation. Data type: text. 

[KalvisT]

For example. One department has 10 Tjenester. With submitting those fields to collection and gallery there are 10 rows added with all the same fields but they could edit the Tjenester control and SLA control for each of the rows. 

[RandyHayes · Accepted solution]

@KalvisT 

First, what you are seeing - "This fomula uses scope which is not presently supported for evaluation" is NOT an error.  This is normal.  The Formula editor cannot evaluate formulas that are based on ThisItem, because...what Item would it be using to evaluate?  ThisItem assumes an item from a list of items - so it just cannot do it.  Again...not an error!

A collection is an in-memory variable database table that has added features of letting you edit, add and remove records.  There is no other intrinsic value that it will provide in the scenario you mentioned.

But, regardless, the formulas I provided should give you the full cascading ability you are looking for.  If there is an issue, provide more details on any errors and, if necessary, any screenshot that would help.

As for the SLA...I am not understanding your question.  What do you mean by Editing the 2 controls.  What are the 2 controls?  And what is it about the color selection that you want to edit?  Are you just wanting to change it from one color to another, or are you indicating that you want to be able to edit the colors in it?

[KalvisT]

I hoped that there would be a way to have a workaround this but it seems not. Thanks for clearing up about collection. I'll have to find a different/better solution for this app. 
Helping out a coworker with this and he started building it with collection and a gallery. I have an idea for a better way for this.

Thansk again! 🙂

[KalvisT]

And of course thanks for the better way for cascading dropdowns!