Patch functioning not posting collection/gallery items to Sharepoint List

[Ela_Hsn]

Hi All,

I'd appreciate your help with a simple list I'm trying to update with collection items.

I created a List in Sharepoint "Expense Report" & used the "Customize form with powerapps" option to create a more advance form.

I created a collection to capture multiple entries from a single user but now I'm having issues posting those entries into the SP list. I get a runtime error that reads: "The requested operation is invalid. Server response:Expense Report failed: Object must implement IConvertible".

I have another list for the category & expense type columns that i created for the cascading dropdowns 

Button adding item to collection ("add item"):

Collect(

//* Category & expense type are choice columns &cascading drop-downs in powerapps. Expense type depends on category
    ExpItems,
     {
Category_Col:CategoryDrpDwn.Selected.Value, //* choice column
ExpType_Col:ExpTypeDrpdwn.Selected.Title, //*single line of text column
ExpAmount_Col: Value(ExpAmount.Text)
    }
)

This is my patch function, which references columns in the collection (gets called when user clicks "Submit button"):
Patch(
    'Expense Report',
         Defaults('Expense Report'),
      {
Description: DataCardValue8.Text, //*This input is from the form & does not get passed into collection
'Expense Date': DataCardValue6.SelectedDate, //*This input is from the form & does not get passed into collection
Category: cCategory, //*cCategory is the collection column
'Expense Type': cExptyp, //*cExptyp is the collection column
Amount: cAmount //*cAmount is the collection column
      }
)

I'd really appreciate your assistance!

[WarrenBelz]

Hi @Ela_Hsn ,

Firstly, that error is a connection-level error and is often solved by deleting and re-connecting the data source, saving and exiting and then re-opening. However, I am assuming your Patch code needs to save all the items in the collection. I have also standardised the field names below

Collect(
   ExpItems,
   {
      cCategory: CategoryDrpDwn.Selected.Value,
      cExpTyp: ExpTypeDrpdwn.Selected.Title,
      cAmount: Value(ExpAmount.Text)
    }
)

and the Patch

Patch(
   'Expense Report',
   ForAll(
      ExpItems As aItems,   
      {
         Description: DataCardValue8.Text,
         'Expense Date': DataCardValue6.SelectedDate,
         Category.Value: aItems.cCategory,
         'Expense Type': aItems.cExptyp,
         Amount: aItems.cAmount
      }
   )
)

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.

Visit my blog Practical Power Apps

[Ela_Hsn]

Hi @WarrenBelz ,

Thank you for assisting me!

I tried the solution you gave above & i'm getting errors. I have to admit also I'm still very new to Powerapps so I might be incorrectly doing this but the errors I'm getting say:

1. ForAll has some invalid arguments

2. That the operations used are incorrect.

I have to mention that I'm using a gallery to display the data I add to the collection.

Please see screenshots attached. Thank you

[WarrenBelz]

Hi @Ela_Hsn ,

The errors are the values you provided

I assumed from your post that you have fields in your List 'Expense Report' called 'Expense Type' and Amount and fields in your Collection called cCategory, cExpTyp and cAmount and also that Category was a Choice column in your List. I think all of this is incorrect - you need to add the actual names as the structure for the Patch is correct.

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.

Visit my blog Practical Power Apps

[Ela_Hsn]

Hi @WarrenBelz 

Thank you for your assistance. Yes, that is correct, my list has those fields.
Here's what I did, I have a Dropdown input field for Category & another for Expense type.

The Expensetype category depends on the Category dropdown. The Category field is a choice field in my SP List & the expense Type is a single line of text.

I created 3 input fields in the PowerApps form to collect the input from the user namely: CategordyDropDwn, ExpTypeDropDwn & Amount (text input field)

The I created a collection using a gallery & added 3 labels for Category, Expense type & Amount & named them: cCategory, cExpType & cAmount to display the items in the collection/gallery.

Now I'm trying to pass those items in the collection/gallery to the SP List using cCategory, cExpType & cAmount to reference them & it doesn't work.

I removed the "Value" (From Category.Value: aItems.cCategory, ) to match the fields I have in my SP list & I encountered an error that reads: "Invalid argument type (Table). Expecting a Record value instead."

When I add it I get errors that there's an expected operator.

I've attached more screenshots of my SP List structure & PowerApps layout

[WarrenBelz]

Hi @Ela_Hsn ,

The syntax structure provided is correct (it is not missing any bracket, commas etc) - so we can do this all at once, you have five fields in your 'Expense Report' list

• Description
• 'Expense Date'
• Category
• 'Expense Type'
• Amount

What type of fields are these ?

Now to ExpItems.  Firstly is this a collection or a gallery ? You have five references you are using

• DataCardValue8 (outside)
• 'Expense Date' (also outside)
• cCategory
• cExptyp
• cAmount

For the last three, are they fields in the collection/gallery or controls (what type) in there ?

Can you please limit your response to these specific questions for the moment.

[Ela_Hsn]

Hi @WarrenBelz 

Ok, on the Expense Report list:

• Description - Text datatype
• 'Expense Date' - Date & Time data type 
• Category - Choice data type
• 'Expense Type' - Single line of text data type
• Amount - Currency datatype

ExpItems:
I created a collection that I'm using a gallery to display. In the collection I'm only capturing & recording: category, expense type & amount.

• cCategory - Is a label control that I use to show the dropdown value that I capture here:
  
  cCategory: CategoryDrpDwn.Selected.Value
  
  - In the "Text" section of this label I have:
  • ThisItem.cCategory
• cExptyp - Is a Label control that I use to show ExpenseType that I capture here:
  
  cExpTyp: ExpTypeDrpdwn.Selected.Title,
  
  - In the Text section of this label I have:
  ThisItem.cExpTyp
• cAmount - Is also a Label where I show the number I captured using a text input field here:
  cAmount: Value(ExpAmount.Text)
  
  In the Text section of this label I have:
  • ThisItem.cAmount
    
    Screenshots attached.

[WarrenBelz · Accepted solution]

HI @Ela_Hsn ,

OK - the main issue here is that you are patching from a collection, but referring to controls in a gallery, which it knows nothing about (even though they are showing values form it). Please look at the below logic - you need to "line up" the input fields name to the names in the List and ensure that you are passing the correct data type. The left side is the list, the right side has the first two values static as they are from outside the collection and the last three as fields in the collection. Please follow the logic and correct any field or control names that are incorrect.

Patch(
   'Expense Report',
   ForAll(
      ExpItems As aItems,   
      {
         Description: DataCardValue8.Text,             //Control outside collecion - Text to List Text
         'Expense Date': DataCardValue6.SelectedDate,  //Control outside collecion - Date to List Date
         Category.Value: aItems.Category,              //Field in Collection - Text to List Choice
         'Expense Type': aItems.'Expense Type',        //Field in Collection - Text to List Text
         Amount: aItems.Amount                         //Field in Collection - Number to list number
      }
   )
)

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.

Visit my blog Practical Power Apps

[Ela_Hsn]

Thank you so much @WarrenBelz your assistance really helped me understand what I was doing wrong.
I realised that I had named my Collection items & gallery items the same & I was referencing the wrong ones. Thank you.