ForAll Patch gallery/collection to excel table

[NielsNachtegaal]

Hi,

I'm trying to put data from a gallery to an excel table on onedrive. I made a template with columns, but when I try to patch or collect it will only fill the first row. If I make a table with multiple rows it puts the whole gallery next after the last row(empty).

using Collect(MDOverzicht;myCollection) and 

ForAll(
 myCollection;
 Patch(
 MDOverzicht;
 Defaults(MDOverzicht);  { Order: Order }  // or {Order: myCollection[@Order]})
)

what am I doing wrong? (hope I made some sense)

[BCBuizer]

Hallo @NielsNachtegaal ,

Since you have defined { Order: Order }, this is the only column that will be updated .

You can find an example of how to perform your required actions here: 

https://www.matthewdevaney.com/7-ways-to-use-the-patch-function-in-power-apps-cheat-sheet/#4.-Create...

In your case this will turn out to be something like the below:

Patch(
    MDOverzicht,
    ForAll(
        Sequence(CountRows(myCollection)),
        Defaults(MDOverzicht)
    ),
    myCollection
);

[NielsNachtegaal]

Thanks for the quick response. The given example has the same result. I end up with one row instead the whole collection has about 12. So if I add 11 more empty rows it then patches after row 12. Columns isn't the issue atm. It's just patching one row all the time while I beg for the whole gallery items.

[BCBuizer]

Hallo @NielsNachtegaal ,

Can you please share an example of the collection you are trying to patch? Perhaps some mandatory fields are missing which will cause the patch to fail for that item, making it look as if only a single item was added.

[NielsNachtegaal]

Hi BCBuizer,

ok the collection made:

Clear(myCollection);;
ForAll(
       BrowseGallery1.AllItems; Collect(myCollection; {Order: Value(Order)})
      )

This shows right when testing in a gallery:

 

but after patching the result with the table is:

 

Again, it's not about the columns as that would just be adding them inbetween the { } so most are empty right now for quick testing purpose. 

 

[BCBuizer]

HI @NielsNachtegaal ,

Indeed the data doesn't seem to be the issue. The weird thing is that when I'm using the same code, it does create multiple new items in the data source (SP list):

Patch(
    Test,
    ForAll(
        Sequence(CountRows(colTest)),
        Defaults(Test)
    ),
    colTest
);

Can you share your Patch code?

[NielsNachtegaal]

Not a problem, 

Patch(
    MDOverzicht,
    ForAll(
        Sequence(CountRows(myCollection)),
        Defaults(MDOverzicht)
    ),
     myCollection
)

It gave me the same results as I tried before the "example" you showed in the first place.

  For example if I make a table with 5 empty rows, the result is like this:

clearing a table with pApps results in one row. And so, back to the whole issue 😕

 

[SebS · Accepted solution]

Hi @NielsNachtegaal 

The Code you used in the last example will work. The issues You experience now, which is very common if You play with the table too much.

Excel Online connector in moment You connect to SpreadSheet will automatically create _PowerAppID_ Column now cause You play too much with it, it's corrupted This Column is not visible.

Recreate the Table in a new Excel file, remove Excel Online Connector from Power Apps, Connect to the new File and then use your New code.

Also, make sure the Excel file is closed when You are patching.

You should not need to use any sequence ForAll + patch should work fine the issue you experience  is related to Table and the bugged ID column

This is why using Excel as a Data Base is not recommended SharePoint is 100% better and You can use Power Automate to create CSV files from the data

Hope this helps

[NielsNachtegaal]

Hi SebS,

That seemed to do the trick. I made a new Excel table and now seems to work. And yes sharepoint is a better option 😛 Thanks for the response and I will accept your response as solution. 

[NielsNachtegaal]

Thanks Sebs, that seemed to be the issue. I made a new excel table and now functions the right way.