Combo-box (one pulled from LookUp Column) will not patch to SharePoint List

[jharville1]

Hello! I have two combo-boxes ('Country' & 'Review Material') that will not patch to my SharePoint list, but all other fields in my form successfully do... Here is a screenshot of the form (there are a few other fields that aren't shown but these aren't important to the underlying issue):

*The 'Country' combo-box only allows for a single selection, and the items are pulled from a "Choice" column in my SharePoint List

     -The name of the 'Country' field's combo-box is called "nationBox"

*The 'Review Material' combo-box allows for multiple selections, and the items within that combo-box are pulled from a Lookup Column

     -The name of the 'Review Material" field's combo-box is called "reviewComboBox"

This is the Patch() Statement that I currently have:

***(side note: the 'Item' property of my EditForm is set to varRecord)

It is supposed to patch all of the fields into a list called 'cfa test list' (and also mark the FormStatus as "Submitted"), but it didn't patch any of the items selected from the "Country" and "Review Material" combo-boxes in my form to its perspective columns in my SharePoint List ...

I added Country: nationBox.Selected to the Patch() statement hoping it would add the item selected from my form's "Country" combo-box to the Country column in my SharePoint list, and even though it didn't throw any errors ... it didn't patch any info to the column and it still shows up blank.

(I've also tried Review Material: reviewComboBox.SelectedItems.Result to patch info to 'Review Material' but that didn't work either)

---------------------------------------------------------------------------------------------------------------------------

Does anybody know what lines of code will be able to patch the *one* item selected in my "Country" field (combo-box) of my form AND the *multiple* items selected in my "Review Material" field (combo-box) of my form to my 'cfa test list' (SharePoint List)?

[WarrenBelz]

@jharville1 ,

I have no problems helping - we just need to at some point have victory or defeat (I prefer the former). I might point out that what you are trying to do was probably never envisaged by the Power Apps designers and I am flying a bit blind as I would never attempt it myself.

Firstly, replacing the Patch with colDD will do exactly what you have encountered as it is a table containing all possible values you could select - it should however have the ID - that was the idea of the collection. As as the combo box SelectedItems are based on colDD, it will also have the Id available (I just tested my model here and the Id is certainly available in the SelectedItems of the Combo Box).

So taking this another debugging step, select some items in the Combo Box and put in a button with this

ClearCollect(colWhatever,reviewComboBox.SelectedItems)

and see what is inside the Collection.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[jharville1]

@WarrenBelz 

I agree with your statement. As long as you believe victory is within sight (which I believe to be true right now), I am willing to do whatever it takes on my side to get this to be successful. I built in the button and attached the code as you mentioned, and this was the result (notice there is no ID):

---

^^ in this example, January was selected from the 'Review Materials' combo-box

And you are correct, colDD does include both the Result & ID of all possible values you could select from the combo-box. However, colReviewItems (the collection that stores whatever Review Material is selected in the combo-box) holds a Result but does not hold an ID :

Is maybe the reason why you said the combo box's SelectedItems is based on colDD (and therefore should have the ID available) because you placed the code in the Items property of the combo-box? Not the OnChange property of the combo-box like I have on my end?         ~The reason I had to put the code in the OnChange property of the combo-box was to avoid the Behavioral error I was getting~

So a quick summary, my combo box's Selected Items aren't pulling the ID from the items selected from the combo-box. Is this possibly because I implemented the collection code for colDD in my OnChange property of the combo-box instead of the Items property of the combo-box (like you had on your end)?

[WarrenBelz]

@jharville1 ,

Can I please summarise this as we are going in ever-decreasing circles.

• You need to make a collection colDD and put it as the Items (not the DefaultSelectedItems) of the Combo Box. This needs to be done before you need to use this control. Screen OnVisible would probably be a good place.
• The DefaultSelectedItems you might as well forget (we had not discussed this to date) as you need to retrieve a Table form the data source - it will be {Value:ThisItem.FieldName} in some form, but that is not part of this discussion.
• Once you have coDD as your Items, the Id will certainly be available - see the test below

You then need to Patch this back to the data source with a format something like

Patch(
   'cfa test list',
  { ItemID in here },
  (
      reviewItem:
      ForAll(
         reviewComboBox.SelectedItems As aPatch,
         {
            Value:aPatch.Result,
            Id:aPatch.Id
         }
      )
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[jharville1]

@WarrenBelz 

Per your first bullet, I did make a collection (colDD) and put it as the OnChange (not Items) of the Combo Box. Per your third bullet, the ID is currently available for colDD. The problem is that colDD contains all possible values you could select from the combo-box, rather than what was actually selected.

When filling out the form, I only selected January from 'Review Materials' combo-box, but colDD is displaying all possible values that could be selected from the combo-box.  See photo below:

I don't want to Patch colDD because it doesn't reflect only the items selected from the combo-box. I do have another collection (colReviewItems) that does successfully hold all items selected from the combo-box. Here is a screenshot of colReviewItems if I select January from my 'Review Materials' combo-box (notice how it successfully stores the review material(s) selected, but doesn't have an ID column like colDD collection does):

Both colDD and colReviewItem are created in the OnChange of my combo-box:

Instead of having colDD show all items that can be selected from the combo-box, how would I be able to populate colDD with only items that also exist in colReviewItems? If I select only January from 'Review Materials' combo-box, I want colDD to look something like this:

[WarrenBelz]

@jharville1 ,

colDD is the Items of your combo box and you select your choices from this. As I mentioned you can generate this at Screen OnVisible. You will be patching reviewComboBox.SelectedItems (you do not need another collection) - that is why you need the Id to be available when you do this. On that subject, to get rid of the As Statement do this on Screen OnVisible (all I have changed here is the name of the Id column)

With(
   {
      wDD: 
      Distinct(
         Choices('cfa test list'.reviewItem),
         Value
      )
   },
   ForAll(
      wDD,
      Collect(
         colDD,
         AddColumns(
            wDD,
            "IdRef",
            LookUp(
               'cfa review materials',
               Title = Result
            ).ID
         )
      )
   )
)

then your Patch (it would help greatly if you posted your code in Text)

Patch(
   'cfa test list',
   varRecord,
   {
       . . . Your Other Fields Here . . .
      reviewItem:
      ForAll(
         reviewComboBox.SelectedItems,
         {
            Value:Result,
            Id:IdRef
         }
      )
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[jharville1]

@WarrenBelz 

I put your code in the OnVisible of my EditScreen as you said, and there were no errors:

The Patch also works as well!      ~In this example, I selected January from 'Review Materials' combo-box:

However, the only issue is the contents of my 'Review Materials' combo-box aren't Distinct anymore. The combo-box's contents are actually an endless loop of items in my 'cfa review materials' SharePoint list (which actually has more instances of each review material than the original 3 of each that exist in my SharePoint list).

^^  I have tried both colDD and colDD.Result in the Items property of the combo-box, but both are still displaying endless loops of items in my 'cfa review materials' SharePoint List, instead of the supposed one of each item name in my 'cfa review materials' SharePoint List.

Do I need to include another "Distinct" function in the Items property of the combo-box (on top of the one we already have in the OnVisible of the EditScreen)?

^^ That's what I had about a week ago when the combo-box successfully displayed one of each review material name (using the Distinct function in the items property), but the Patch wasn't working...

[WarrenBelz]

@jharville1 ,

Just for a bit of a logic exercise, have a think about why you are getting duplicate values. Part of attempting something as complex as this is understanding exactly what the elements of the process are doing (and I am genuinely trying to help your Power Apps journey here). Have a look at the process in collecting colDD and ask yourself what line of code needs to go at the top to address this. I will give it to you as soon as you respond if you cannot work it out.

[jharville1]

@WarrenBelz 

So within the With() statement, we are creating/instantiating the wDD variable to only show unique values of the reviewItem column in 'cfa test list' (by using the Distinct() function). This part looks okay.

In the ForAll() statement, we are using each of all records in wDD to create a collection. I suspect this is where the issue is coming from. Let's say there are 7 records in the wDD variable, the collection loop will be ran 7 times, therefore creating 7 sets of duplicate values that will be displayed in the 'Review Materials' combo-box. Is this correct? After looking at the code following that section, everything inside the Collect() statement looks good.

So in a sense, we'd only want to parse through wDD once, not for however many records exist in that variable, correct? I'm not sure exactly what function would be used to achieve this desired result. I don't think we'd need another Distinct() ... would the function be placed between the ForAll() and the wDD variable?

I appreciate the response and opportunity to decipher the code and test myself. I've learned substantially more from PowerApps forums than classes and other online resources I've used, and things like this help strengthen my skillset and prepare myself better for future projects. Thank you

[WarrenBelz · Accepted solution]

Hi @jharville1 ,

Even simpler than all of that - you just need the line at the top - then every collect start with an empty set.

Clear(colDD);
With(
   {
      wDD: 
      Distinct(
         Choices('cfa test list'.reviewItem),
         Value
      )
   },
   ForAll(
      wDD,
      Collect(
         colDD,
         AddColumns(
            wDD,
            "IdRef",
            LookUp(
               'cfa review materials',
               Title = Result
            ).ID
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[jharville1]

@WarrenBelz 

Your solution makes perfect sense, but somehow when I apply the code to my OnVisible of my EditScreen I still get the same issue (what seems like an endless repeat of list contents):

^^ If my list contents in 'cfa review materials' is the months of the year, I see January over 30+ times in the combo, when I'd only like to see it once.

(ex: January, February, March, April, ........ January, February, March, April, ........ and so on)

Should the Clear() be going within another property? It seems like that extensive collection right now is happening all in one iteration through the code, not multiple (since we applied the Clear() function in the beginning)