Repeating Table and Dropdown

[AshleyFoozi]

Hi All. I have seen a similar question here which has not been adequately answered. 

I have a repeating table in a gallery (using Shane Young's brilliant Youtube videos). In the row I have several fields; some text boxes and others drop downs. After filling the first row and creating the next row, I noticed that the selected drop down values go back to the first value of the Items. When looking at the collection the values are correct. Some users might think that their entry hasn't been saved. I have other repeating tables without drop downs and they work perfectly. Anybody have any suggestions?

First row entry.......

Second Row entry.......

[RezaDorrani · Accepted solution]

Hi @AshleyFoozi 

Make sure the default value for that dropdown is being set based on the collection item

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AshleyFoozi]

Brilliant @Reza 

Thanks for the help.

[vwyankee]

---

@RezaDorrani wrote:

Hi @AshleyFoozi 

Make sure the default value for that dropdown is being set based on the collection item

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

---

I have the same issue and can't seem to remedy it. I've found a couple threads indicating that I need to use "ThisItem.collectioncolumn" or "{value: collectioncolumn}" but neither are working for me. With either (or just using the collection column as the default value) I have a warning that "This formula uses scope, which is not presently supported for evaluation." I'm unable to find any examples with this warning and the issue I'm seeing. Some of my comboboxes are using a SharePoint list as a lookup data source while others are using a SQL database. The text boxes are fine but every combobox is reset after adding a new row just as the original poster here shows which makes me curious if he was able to fix his or just said thanks for the answer.

I am using patch with "ThisItem" as noted as well in this other other discussion.

[vwyankee]

Completely redesigning the way the repeating table works I thought I was in the clear with simply having the gallery show the collection and the top line resetting to add the next row, but have the same issue once again where all lookup fields are blank when putting those rows back in the top row for editing.

One difference this time is I'm not getting any warnings at all and the formula preview shows me the correct data but it's just not showing in the field. Extremely frustrated I'm about to put in a ticket with MS support as I interpret this as something not working that should be, especially since the preview is showing what we want to show in the input box. Here are a couple examples where I'm using the gallery and the collection identifier for the same field's Default property.

[vwyankee]

For anyone else that may have a similar problem as I have... It turned out to be a pretty simple solution after getting insight from Microsoft support. I had a different issue and while on the call I asked about this one and you have to use the same schema as your datasource. The simpler way to put it - if your datasource for your dropdown/combobox is specifying a column and/or filters to grab the values for the choices you need to use the same lookup to get the correct value to show back in the field after it's saved to the collection. Because we're working with tables/records (whether it's a SharePoint list, SQL table, Excel file, etc) we need to use the DefaultSelectedItems property instead of the Default property. Okay, time for examples to clarify.... I'm going to call the repeating table fields that are drop-downs or comboboxes choice fields for simplicity.

If you're using the method from Shane Young's Repeating Table videos where you're adding a new collection input row after saving the first row you'll want to set the DefaultSelectedItems property to filter the the datasource using the collection value as the equalizer. If we have a choice field pointing to a SharePoint list with the name Units of Measure, the Items property is set to: 'Units of Measure'.Title so we can get all the values in the Title column as our choices, right? Right. In this case our DefaultSelectedItems property needs to lookup to that same data source and find the choice that's been saved to the collection (named MyCollection) with the collection column name of ColUM. The formula to get this value: Filter('Units of Measure', Title = ThisItem.ColUM)

Translated in layman's terms: Filter the data source 'Units of Measure' where Title is equal to the value saved for this item/row in the ColUM column. It's the same using SQL and Excel data sources.

A SQL example formula: Filter('[dbo].[Units of Measure]', Title = ThisItem.ColUM)

See below, if you're using the method of separate (non-connected) input controls and a gallery to show the results (like my redesign) so that the top row is always the input "form" and you see the results in the gallery and then put the gallery/collection row back in the top line for editing... maybe I should make a YouTube video of that to compliment Shane's? 

It's a little different since you need to specify the gallery item selected (vs ThisItem in a collection): Filter('Units of Measure', Title = MyGallery.Selected.ColUM)

I've found that in this design you can either the collection or the gallery as the equalizer. In other words, this formula for the same thing works too where GalUM is the name of the label control within MyGallery: Filter('Units of Measure', Title = MyGallery.Selected.GalUM.Text) 

Of course, with all that explained I still have two fields from a SharePoint list data source that I can't get to work with the same method because they have multiple results and the distinct and first functions don't seem to be helping me. It's a back and forth love/hate relationship with PowerApps... I'll update when I get that sorted!

For lookups where there may be multiple results you need to use the Distinct function with a Result for your logical test. If the above example had multiple rows with the same values in the Title field we would make the formula: Filter(Distinct('Units of Measure'.Title, Title), Result = MyGallery.Selected.ColUM) 

Translated in layman's terms: Filter the data source 'Units of Measure' where Title is equal to the value saved for this item/row in the ColUM column and show me just one result value if there are multiple matches.

[Kat27]

I have tried this example and not sure what I am missing or doing incorrectly.  I am still not able to collect my data from a dropdown in my Gallery.  

ActiveProjects.PlanName = SharepointList, Column Name

ddPlanName = Gallery dropdown field selecting from the PlanName column in SharePoint list

Items = ActiveProjects.PlanName

I have tried multiple combinations for the Default of the MyGallery.ddPlanName and nothing works.  See below of a few examples of what I have tried in my defaults for the ddPlanName.  

Filter(ActiveProjects,ddPlanName)

Filter(ActiveProjects,PlanName)

Filter(ActiveProjects,ddPlanName.SelectedText.Value)

Thank you for your kind assistance.

[vwyankee]

@Kat27 - It appears you're missing the equalizer to tell it what to show. You'll want your DefaultSelectedItems property of the drop-down ddPlanName to be either:

Reference the Collection Column for PlanName - let's say you have it named colPlanName:

Filter(ActiveProjects, PlanName = ThisItem.colPlanName)

Translated as - Filter the ActiveProjects SharePoint list and give me the PlanName column value that matches the value saved in my collection for the column colPlanName of this row.

or 

Reference the gallery selected item:

Filter(ActiveProjects, PlanName = MyGallery.Selected.ddPlanName.Text)

Translated as - Filter the ActiveProjects SharePoint list and give me the PlanName column value that matches the value shown in the control ddPlanName of the gallery item/row selected.

While either should work I would go with the first option as it get get a bit confusing referencing things by the gallery and not the collection.

[Kat27]

My apologies.  I’m still missing something for this to work.  I did try the Gallery formula (See below).  But I received error.  I hope I have enough information below for you to see any issues.

My Gallery name is “GalleryCodeRight” My Column Names are PlanName and Hours.  Both are dropdown fields.  I’m only working on one at a time to get this corrected.  When I complete the Hours field, I have to figure out how to sum the dropdown values for the total to appear in the ECPLDetailsForm.

My dropdown now shows nothing to select.  No error with the Items: Filter(ActiveProjects, PlanName = ThisItem.PlanName)  However, no selection available. 

OnSelect – NewForm

NewForm(ECPLDetailsForm);ClearCollect(ECPLCollection,{PlanName: "", Hours:0.00}); ResetForm(ECPLDetailsForm);Navigate(CodeRightScreen,ScreenTransition.Cover)

Selected Item (no error) but no items to select in dropdown:  Filter(ActiveProjects, PlanName = ThisItem.PlanName)

Default: ThisItem.PlanName

I tried the Gallery formula in the Items, However received error:  Filter(ActiveProjects, PlanName = GalleryCodeRight.Selected.ddPlanName.Text) 

OnSelect command (SaveIcon): Patch(ECPLCollection, ThisItem, {PlanName: ddPlanName.SelectedText.Value, Hours: Value(ddHours.SelectedText.Value)}); Collect(ECPLCollection, {PlanName: "", Hours: 0.00})

[vwyankee]

Okay, it appears we got some wires crossed on what to use where. I can say If you follow Shane's video you'll get everything right as your concept, but he doesn't include any look-up controls in his gallery, which sounds like that's all you need left. With the additional info I see what your collection name and columns are and presuming the concept is to gather information in the gallery items to populate the hours in the form... Let's try this...

For your gallery drop-down (or combo-box) controls the Items property needs to look to the source of the choices. If you're using a SP list as the choices lookup then your first post was correct to have the gallery ddPlanName.Items set to ActiveProjects.PlanName. The DefaultSelecteditems property of the same control (ddPlanName) should be Filter(ActiveProjects, PlanName = ThisItem.PlanName). The Default property should be blank (for any drop-down/combo-box control).

The Hours control field (guessing just a text label or input box) in ECPLDetailsForm just needs to have the Default property set to Sum(ECPLCollection, Hours).

Let me know if that helps.