PowerApps Exchange

Expand all | Collapse all

Cannot patch Sharepoint List with multi select combobox

Jump to Best Answer
  • 1.  Cannot patch Sharepoint List with multi select combobox

    Posted 8 days ago

    Hi All,

    I am trying to figure out how to use Patch() to create a new or edit existing SharePoint List where a column is a Choice

    For my App, I have a screen which has the following OnVisible property set to:

    ClearCollect(colRequest_Type,Request_Types);
    ClearCollect(colRequest_Priority,Request_Priority);
    ClearCollect(colRequest_Products,Request_Products)

    These ClearCollects are using an excel file in OneDrive for the datasource, and the excel file contains tables.  The collections are created just fine

    The collection "colRequest_Products" is used in a combo box control named Items property. This combo box allows multiple selections, and my goal is to have these selected items stored in a Sharepoint List "choice" column with multi-select enabled.  I have the SP list column named "psr_products" and it is set to choice with multi-select enabled.

    The screen for submitting a new request has a Submit button with its OnSelect property is set to:

    If(Connection.Connected = true,
        Patch(
            'Presales Service Desk - PSR Tickets',
            Defaults('Presales Service Desk - PSR Tickets'),
            {
                Title: txtNetSuiteCUSTID.Text,
                psr_type: ddRequest_Type.Selected.Value,
                psr_priority: ddRequest_Priority.Selected.Value,
                psr_products: {'@odata.type':#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Value:cbRequest_Products.SelectedItems
            }
        )
    )
    

    Unfortunately it just doesn't seem to work. 

    If I attempt to patch without the choice column "psr_products" the record gets added to the SP list.  once I try to add the choice field "psr_products" and patch, it will not work.

    Can anybody here help with explaining how to patch a sharepoint list item (create new or edit) where one of the form controls is a combobox and multiple selected items need to be saved in sharepoint list item "Choice" column type with multi-select enabled?



  • 2.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 7 days ago
    Hello Chris,

    it won't be easy. The item for the dropdown it's not just a text. It's:
    enumerator;text value - SharePoint object.

    You have to find this object:
    lookup(choices_from_combobox, choices_from_combobox.text = selected value)

    collect found objects in the collection, send to SharePoint.

    I told you it won't be easy :).


    Cheers,







  • 3.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 6 days ago
    I'm curious why the Excel file?

    Couldn't you just use the Choices function?  Are values stored in the SP list column's setting?

    Choices function - PowerApps

    It probably doesn't solve your Patch problem however.  Have you tried just a regular EditForm with the SubmitForm function instead?

    -Jamie


    ------------------------------
    Jamie Willis-Rose
    Supervisor Technical Operations
    Discovery
    Sterling VA
    ------------------------------



  • 4.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 6 days ago
    The excel file allows me to let other users easily add new options to the tables.

    Without giving perms to SharePoint which would only confuse the user community.

    I allow access to excel file to designated users and they can easily update the table to add options to the drop down list.

    PowerApps creates a collection from the table.

    The challenge I need to solve is how to patch to a splist when multiple items have been selected.




  • 5.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 6 days ago
    Ah... Well I would vote against exposing the raw data source period and instead use a screen/form to let users edit/add options.

    You hide or disable navigation to said screen depending on the User().Email or DataSourceInfo.CreatePermission

    Good luck!

    ------------------------------
    Jamie Willis-Rose
    Supervisor Technical Operations
    Discovery
    Sterling VA
    ------------------------------



  • 6.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 6 days ago
    Great idea. I just haven’t become advanced enough in PowerApps to figure it all out.

    I am thankful communities like this exist. The PowerApp doc is ok. But I think they could provide more depth, and use case examples to help us newbies.




  • 7.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago
    Did you try taxonomy/management metadata ?





  • 8.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago

    Taxonomy/Management metadata?

    I've know clue what that means.  Perhaps in lay terms you could describe that so I understand what you are suggesting.



    ------------------------------
    Crockerfeller
    Austin, Texas
    ------------------------------



  • 9.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago
    Hello,

    It's a central dictionary for your dropdown lists (in your case. Taxonomy .... wider subject)
    you can create central repository and store values which you expose in dropdowns (multi-select as well). You can grant permissions to the users who can contribute -  so they can manage the values.
    You can share this repository across the site collection. Not only one list.
    There is a special column type: management metadata, you can connect to your global store. Values from the store would be populated into your dropdown.
    this is the page:
    <your website url>/_layouts/15/termstoremanager.aspx
    add one term set and some terms.
    then go to your list settings and add the column. Column type: Managed Metadata, and Point the value to your term store (Term Set Settings  - section).
    done.
    If you want some sort of people to be able to manage the values, simply assign them as contributors in term store settings.
    I hope I gave you some spotlight. If you need more details, feel free to contact me.







  • 10.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago
    ​Hi - same as what the other folks have said - SharePoint choice columns are fiddly and SharePoint will be looking to match the value you patch - you cant just submit a random value into one - it has to already exist to match.

    Better to either make the sharepoint column just text - as you are already controlling what people can choose to select from the excel file right? If that's no good - then create a new sharepoint list to house your datasource of choices - you can even put row level security on each list item, then in the list add your sharepoint list you are patching to add this column as a look up - the id's will be respected and you will be able to patch new items against any id values that exist in the look up.

    Hope this helps!

    ------------------------------
    Rich Burdes
    Consultant
    Christchurch
    274550142
    ------------------------------



  • 11.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago

    Hi Rich,

    Yes, I agree Choice columns are fiddly.  I did have the Choice column set up with all of the choices that the excel table had, and I enabled the option to allow adding new entries.

    However, that being said.  I agree that the easier approach would be to control this all within PowerApp itself, and have the SPList Column be configure as a text field.

    The issue I am experiencing with that method is that the patch() function errors out on the ComboBox... It seems to be trying to push a table (the combobox values come from a collection/table) where it needs to be a string.  I am assuming that if I change my SPlist's column from a "choice" type to a "text" type column, that I can avoid completely the complicated syntax needed for patching to a choice column with mutliple items selected?  e.g.  @odata.type: {...... 

    ​Seems like it would be easier to patch to a text field, but I need to figure out how to get the selected values of the combo box sent to SPList as a string:  item1; item2; item3  etc.

    ------------------------------
    Crockerfeller
    Austin, Texas
    ------------------------------



  • 12.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 5 days ago
    You didn't try taxomy. Did you?





  • 13.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 4 days ago
    From my personal experience, I would avoid taxonomies if you are newbie and/or you want users to be able to add/update the combo box values.

    If you decide to go down that route, here's a starting point: Introduction to managed metadata

    If patch() is the issue for you, have you tried SubmitForm instead?

    -Jamie




    ------------------------------
    Jamie Willis-Rose
    Supervisor Technical Operations
    Discovery
    Sterling VA
    ------------------------------



  • 14.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 4 days ago
    That is the case. With taxonomy users can modify values and they don't need to be a member of site admin or owners. 





  • 15.  RE: Cannot patch Sharepoint List with multi select combobox

    Posted 4 days ago

    Hi Jamie,

    Thank you for the reply.  Yes I have tested with SubmitForm() and I am able to get multiple values selected in the combobox written to the SPList column.

    I was shying away from using a Form as I thought it was too restrictive on layout.  I will revise that thought, and try again. 

    Using my own layout with Patch() seemed more flexible, bit not so easy in other ways.  I will try using more columns in my form layout and use SubmitForm() for this as I think it will prove to be easier in the short term.  I can always come back and update the app once I have gained more skills with PowerApps.  :)

    Thank you and everyone else for your help and replies!



    ------------------------------
    Crockerfeller
    Austin, Texas
    ------------------------------



  • 16.  RE: Cannot patch Sharepoint List with multi select combobox
    Best Answer

    Posted 3 days ago
    Edited by Christopher Crocker 3 days ago

    Hi All,

    I'm circling back on this to update everyone that I finally found the answer I needed. :) While there were some diversions along the way with discussions about Taxonomies/Metadata, and why use Excel? for my drop down or combo box choices. The real need was figuring out the syntax for the patch(). Finally....I found the solution in a tutorial that Audrie Gordon published on Oct-28-2018. 

    Audrie covers several other cool ideas in this tutorial, but the part that was most interesting to me is how she used the patch(() function to create a new SPList item where there were both combobox controls where a user would select multiple items, and also drop down controls with only a single choice chosen.

    This tutorial is genuine GOLD.  The rubber meets the road for me starting at about 29:30 where Audrie dives into the patch syntax. Using Patch() is preferred for me over a SubmitForm for an editForm because you get far more flexibility for laying out your controls when not constrained to a column based form layout.

    Reuse Forms/Galleries for multiple SharePoint lists!

    For reference, here is the 'OnSelect' formula for my Submit Button, based on the guidance provided in Audrie's video.  I hope others find it useful. 

    Clear(colPSR_products_Selected);  // Create a collection for all product choices to choose from
    Clear(colPSR_products_SelTable);  // Create a collection to be used as a table for the patch funcion
    
    // The patching is a bit complex due to the use of a combobox with ability for the user to select multiple items.
    // First we want to add the products selected in the cbRequest_Products combobox to a collection
    ForAll(
        cbRequest_Products.SelectedItems,
        Collect(
            colPSR_products_Selected,
        {
            Iterator: CountRows(colPSR_products_Selected),
            Value: Value
        }
        )
    );
    
    // Next we need to create another collection to be used as a table for patching.
    ForAll(
        colPSR_products_Selected,
        Collect(
            colPSR_products_SelTable,
            {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpanderReference",
                Id: Iterator,
                Value: Value
            }
        )
    );
    
    // Now we can patch the form field data to the SharePoint list!
    Patch('Presales Service Desk - PSR Tickets',
        //Defaults is required when adding a new item to the list
        Defaults('Presales Service Desk - PSR Tickets'),
        {
            //Title field is required so a default must be set
            Title: Concatenate(txtCompanyName.Text," - ",txtNetSuiteCUSTID.Text," - ",txtNetSuiteOPPID.Text),
    
            psr_type: LookUp(
                colPSR_type,  // colPSR_type is a collection created from the choices in the SPlist column psr_type
                Value = ddRequest_Type.Selected.Value
                ),
    
            psr_priority: LookUp(
                colPSR_priority, // colPSR_priority is a collection created from the choices in the SPlist column psr_priority
                Value = ddRequest_Priority.Selected.Value
                ),
            
            company_name: txtCompanyName.Text, 
            netsuite_custid: txtNetSuiteCUSTID.Text,
            netsuite_oppid: txtNetSuiteOPPID.Text,
            psr_products: colPSR_products_SelTable,
            description: txtDescription.Text
        }
        );
    // Reset Controls
    Reset(ddRequest_Type);
    Reset(ddRequest_Priority);
    Reset(txtCompanyName);
    Reset(txtNetSuiteCUSTID);
    Reset(txtNetSuiteOPPID);
    Reset(cbRequest_Products);
    Reset(txtDescription)
    



    ------------------------------
    Crockerfeller
    Austin, Texas
    ------------------------------