"The data source supplied to the function is invalid" error when patching a collection to a collection.

[trjackson77]

Hi, I'm getting a runtime error when using the pattern for bulk patching datasources demonstrated by @RezaDorrani  in this youtube https://www.youtube.com/watch?v=VzrarcM9W5k&t=922s.  It works fine in one app, then I tried to implement it in another, the code appeared to be fine but the patch didn't work and I had the following runtime error:

"The data source supplied to the function is invalid"

The first collection is created in the code snippet below, it is essentially a filtered sub-collection of the patched collection. The collection to patch to is the main app collection and works just fine in all other cases. Looking in the forums this error seems to relate to dropdowns and any advice offered for clearing the error seems not to be effective here.

Code below... 

ClearCollect(
        colBuildStocktakeSelected,
        galAHProductList.AllItems
    );
    UpdateIf(
        colBuildStocktakeSelected,
        true,
        {RecordSelected:true}
    );
    Patch(colSeqALL,colBuildStocktakeSelected)


/*
Old Loop Code
ForAll(
    galAHProductList.AllItems,
    Patch(
        colSeqALL,
        LookUp(colSeqALL,ProductCode=lblAHProductCode.Text),
        {RecordSelected: true}
    )
)
*/

Any pointers in the right direction would be most welcome!

Tim.

[RandyHayes]

@trjackson77 

You are most likely seeing this on the first Patch statement encountered in your Formula. 

  Patch(colSeqALL, colBuildStocktakeSelected)

To do such an action, the schema of the source and the table must match. Otherwise it considers the first argument to be a datasource...and if there is no such named data source, then you will see that error.

You don't need all those collections to accomplish what you want.

Please consider changing your Formula to the following:

ClearCollect(colBuildStocktakeSelected,
    ForAll(
        galAHProductList.AllItems As _item,
        Patch(_item, {RecordSelected:true})
    )
)

I would still question the need for the colBuildStocktakeSelected collection, but in general, looking at the formula you provided, this is the equivalent. 

Once you move your apps away from collections for everything and only for when appropriately needed, these type of schema confusions will go away.

I hope this is helpful for you.

[trjackson77]

@RandyHayes, many thanks for your reply, it's much appreciated.  Your suggestion above is indeed the solution implemented originally. The trouble was that the ForAll loop was taking a long time to execute, giving a negative user experience.

Regarding the matching of the schema, the collection (colBuild...) is based on a gallery whose data source is the collection to be patched (colSeq...).  Am I right in thinking that the schema should match?

Regarding the use of collections, I take your point.  However, delegation issues and intermittent connection problems have put me on this route.

By patching the temp collection to my data source collection rather than creating a ForAll loop to patch has boosted performance in other apps.  I just can't see why it won't work in this case, unless you are telling me that the schemas won't necessarily match when creating a collection from a gallery?

Thanks again for looking at this.

Tim.

[RusselThomas · Accepted solution]

Hi @trjackson77 ,

Disclaimer: Bulk patch updating syntax has had me confused for many, many years.  Today is no exception - but I think I might have some insights that could help.

First, I'm assuming that your galAHProductList Items: property is set to showing some sort of filtered view of colSeqAll items?

No way to know what it is, but for the purposes of discussion, let's assume it's something like 

FirstN(colSeqAll, 10)

Now - maybe you want to collect the galAHProductList.AllItems because there are controls inside your gallery with data you want that are not in the original data from colSeqAll - but for now just be very aware that;

ClearCollect(colBuildStocktakeSelectedV1, FirstN(colSeqAll, 10))

//is VERY different to 

ClearCollect(colBuildStocktakeSelectedV2, galAHProductList.AllItems)   
//where Items: on galAHProductList is FirstN(colSeqAll, 10)

if you run the above and compare colBuildStocktakeSelectedV1 with colBuildStocktakeSelectedV2 you'll see what I mean.

In your example, you're collecting your gallery.AllItems into a collection - updating that collection, and then patching the updated collection back to a third collection (colSeqAll).

In this, I believe @RandyHayes is correct - your ultimate destination schema (colSeqAll) does not match your source schema (colBuildStocktakeSelected, which comes from galAHProductList.AllItems)

But, if I understand the question context, the idea is to patch the updated records back to source, not back to your gallery - so in theory you just need to align your source schema with your final destination. 

The way I understand it you need to stipulate your changerecords along with your baserecords - where;

• baserecords is essentially the records as they were before they were updated.  The original source record to update should be identifiable by looking at the baserecord.
• Changerecords is the same set of records corresponding to baserecords, but with updated field data which get's passed into the patch for the corresponding baserecord.

patch(source, baserecords, changerecords)

Now - how on earth PowerApps decides how baserecords correlate to collection source records without a clear primary key is beyond me - maybe it creates a composite key or something - no clue, but it does seem to work.

If all this is true, then the way I understand the rules around batch patching, I would expect something like this to work;

ClearCollect(collectBaseRecords, Filter(colSeQALL, FirstN(colSeqAll, 10));
ClearCollect(collectChangeRecords, collectBaseRecords);
UpdateIf(collectChangeRecords, {RecordSelected:true});
Patch(colSeqAll, collectBaseRecords, collectChangeRecords)

Obviously, FirstN(colSeqAll, 10) is just a guess as to what your gallery is currently showing through it's Items: property - just replace that part of the formula with the same expression you're using in the Items: property of the gallery.

The key is, our baserecords are coming from a source and the patch is then going back to the same source.

Hope this helps,

RT

[RandyHayes]

@trjackson77 

Actually, the suggestion provided is not equivalent to your old method.  That was ForAll(data, Patch(source...  Your old method used the ForAll as a For/Loop...which it is NOT, it is a function that returns a table.  So, Patch(source, ForAll(data...  is the correct way to do a bulk patch of a datasource as the ForAll creates a table and gives that to the Patch function rather than instantiating a Patch on each iteration of the ForAll.

Data connection issues are certainly a use for collections for your primary datasource.  In other words, taking a snapshot of a datasource for a collection if the app is going to be offline is a valid use.  Delegation issues should be worked around and resolved in another way.  Use of collections and variables should be VERY limited.  The more you have the more confusing and complicated to maintain you app becomes.  Reference everything.  PowerApps is built around Excel, so follow the principles in Excel when you create a formula there - you reference other cells.  You are not writing code, you are building formulas in PowerApps.  This principle, once embraced, will completely open the level of productivity and success of your apps.

Now, here is the thing about collections in offline apps in regard to the DataSource - you should have ONE collection that is a ClearCollect of the datasource.  At this point, your schema is completely correct.  In your app, you should imagine the collection to be the datasource and work with it directly.  Do not make additional collections of the collection with any custom schemas only work with the one.  Then, when you are connected again and need to actually push the data changes to the datasource, the schema will all match and the bulk patch/collect to the datasource will go very smoothly and quickly.

Anything beyond that is just more work to design formulas for and will cause your app performance to suffer.

Bulk patching and collecting is quite easy to grasp if you follow a couple of essential principals.  One is that you need to provide primary key values if you are updating, otherwise you will be creating, and Two, that your schemas match.  If you create an offline collection directly from the datasource, the schema will match.  Anytime you start putting in additional columns or hand writing records, your schema is sure to suffer and thus bulk patching becomes a problem.

Hopefully that is clear and helpful.

[trjackson77]

Firstly thank you both ( @RandyHayes  and @RusselThomas  ) for your responses.  @RusselThomas  , I internalised and implemented your version of the code and shaved some time off the end result.  @RandyHayes , clearly I was sloppy with the details and I accept that there is a difference in Patch -> ForAll and ForAll -> Patch.  I will compare the two methods (bulk Patch and Patch-ForAll) to see which one gets me the best performance.

@RusselThomas ,Whilst I got  pattern to work, first tested the explanation to see if there was a difference in the collections.  They seem to match (see attached).  I also don't really understand why the intermediate collection is required in this instance if you are collecting against the items filter rather than the gallery.  But testing the theory by skipping the temp collection seemed to yield duplicate items rather than patching the original, so I'm guessing the data source discrepancy holds true somewhere.

So much for citizen development, looks like I'm going to have to study the basics a bit more.  Thank you both for the lesson in good design principles.

[RusselThomas]

Hi @trjackson77 

The difference between collecting from source and collecting from a gallery.AllItems that is linked to a source, is that the gallery will include all the controls inside it as part of the dataset.

Think of it as your source record columns + a bunch of columns that reflect the gallery controls.

Let's say your gallery has a text label called lblTitle that displays ThisItem.Title in its Text: property.

If you collect that gallery.AllItems you'll see all your source records plus a column called lblTitle with a table inside it. 

If you drill into that Table you'll see the properties for your text label, including the Text: property with the value for ThisItem.Title

Hope this clarifies 🙂

Kind regards,

RT