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 ,

ReviewItem as Multiple Select is your issue - is this a Choice or Lookup field?

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 

The 'Review Material' PowerApp field allows for multiple selections.

I also typed out a quick summary below to state anything I may have left out before.

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

I have 2 fields in my form that won't patch to my SharePoint List ('Country' & 'Review Material'):

• 'Review Material' field selections should patch to "reviewItem" in my SharePoint List
• 'Country' field selections should patch to "Country" in my SharePoint List

• 'Country' is a "choice" type column in my SharePoint list. My app only lets the user select one country from the list of items displayed (the choice item options that are populated in the combo-box are typed directly into the column settings in my SharePoint list)
  • The name of the 'Country' field's combo-box is called "nationBox"
• 'reviewItem' is a "lookup" column in my SharePoint list that pulls information from another SharePoint list ('cfa review materials'). My app allows the user to select multiple items from inside that combo-box. 'Review Material' also has a Distinct function applied to it (from inside the PowerApp) to only show each review material name once (no duplicates). This should use a Result instead of a Value field.
  • The name of the 'Review Material" field's combo-box is called "reviewComboBox"

My main SharePoint list is called 'cfa test list' . This list is the list that isn't having anything patched to it from those two fields ('Country' & 'Review Material') in my PowerApps form.

Thanks for your help Warren, I really appreciate it.

[WarrenBelz]

@jharville1 ,

Before we go further on this shifting subject, is your original question on Country now solved?

Lookup columns are highly problematic when you are trying to Patch multiple values to a multiple value field unless you simply "leave alone" the Items at Choices(ListName.FieldName) and let Power Apps do the job for you. You actually have to get each ID back from the other (looked up) list and use a ForAll() statement inside the Patch to send both the ID and Value back to your field. You do not need Lookup columns unless you are giving your users SharePoint Data Sheet bulk-edit views. I have not used them for year for this and many other reasons.

What are the Items of this Combo Box and what was the original lookup choices?

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 

After extensive testing, I found out my 'Country' field was a corrupt-data card. I recreated a duplicate 'Country' field in my EditForm, transferred over all the properties/tree components from the previous 'Country' field to the duplicate, and then deleted the old one. After submitting the form, I can now see that the 'Country' field is successfully patching to my SharePoint List.

I also recreated a duplicate 'reviewItem' field in my EditForm, transferred over all the properties/tree components from the previous 'review' field to the duplicate (except I didn't use the Distinct function on the Items property ~ Distinct(Choices('cfa test list'.reviewItem), Value) ... I used the default code for the Items property, which is: Choices([@'cfa test list'].reviewItem) ), and then deleted the old/previous field.

... After submitting the form, I saw that the 'reviewItem' field successfully patched to my SharePoint List. However, I need to use the Distinct function, because if I don't, 3 of each review material will populate the 'Review Material' combo-box. 

I don't think the Lookup itself is the problem for the patching, but more-so the Distinct function applied to the Items property of reviewComboBox (the combo-box of my 'Review Materials' field).

Would possibly creating a collection to hold everything that's selected within 'Review Material' (while having the Distinct function stated above attached to the Items property) and then patching that collection to the SharePoint List work?

[WarrenBelz]

@jharville1 ,

The main difference normally with the Distinct filter is that you need to refer to the output as .Result rather than .Value, however the problem you face was what I was alluding to with a Lookup field. You need the Id to be part of the record you choose and a Distinct filter only returns one element (the Value) - so you lose it and have to get get back. Multiple choice Lookup columns present further issues, having to get back all of the Id values, then Patch in a ForAll loop. To be honest you should consider making it a Choices column and doing a Distinct collection as the Items (and simply base the collection on the field being looked up).

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 appreciate the response. I'm afraid that the Lookup is our only option though since we need to pull from a separate list that our client will manage on their end. They will update/add/delete review materials and their perspective countries, email address, and other columns to their list ('cfa review materials'), and we need to pull the review materials selected from the form (along with other form fields) to patch to our parent list, 'cfa test list'.

[WarrenBelz]

@jharville1 ,

Yes, but you still do not need the Lookup field - you can get the list from a Distinct filter in Power Apps (like you are doing) and simply Patch back to a multi-Choice field - you only then need to refer to the Value field (which you have), not Value and Id as you do with a Lookup. You might have a look at this video from Shane Young @Shanescows .

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 

Thank you for the response, I'll look into this and give it a shot!

[jharville1]

@WarrenBelz 

How would I reference another list ('cfa review materials') in my EditForm when the main list of my EditForm is 'cfa test list' ? Aren't I only allowed to reference one list at a time per form?

(I believe I've tried referencing multiple SharePoint lists in a form before and it error-ed out) ~ Wouldn't I need a Lookup field to pull the Review Material column from 'cfa review materials' and put it in my EditForm field that uses criteria from 'cfa test list' ?

I'm just curious how I'd use the Distinct function to execute on a field that pulls from another list. My use of the Distinct function right now is still operating on a field within 'cfa test list' since the Lookup column exists in that list, and we're not referencing another one. Also, do you mean creating a new 'Choice' field in my SharePoint list that allows multiple selections?

[WarrenBelz]

@jharville1 ,

The further we go down this rabbit hole, the more complex it is going to be, so I have retreated a step and pulled together some code that should get your Id back. You need a Collection (colDD in example below) that will become the

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

I have tested this on the Distinct of a field, but not on a Choices set. You will then still have Patch this back - the part with this field will look something like

{
   ReviewItems: 
   ForAll(
      ReviewComboBox.SelectedItems As aPatch,
      {
         Value: aPatch.Result,
         Id: aPatch.Id
      }
   )
}

I hope this works for you, but I simply do not use these fields as they are generally completely unnecessary.

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.