Patching a collection back to datasource

[Anonymous]

I have 2 weeks trying to figure out what is happening with my Patch statement. I am pretty close but my knowledge is limited in such way I am almost giving up. Already read hundred of pages between google sources and this forum trying to sort it out. Let's see if you can help me.
I am trying to patch a collection that contains all my updates back to my datasource in dataverse.
First, I use this statement to populate the collection with my DS fields
ClearCollect(CPOItemsChanges,Defaults('T-Purchase Order Items'));
Clear(CPOItemsChanges)

Then On my gallery I am able to capture all the changes into this collection by using the update or collect functions to change only these fields on the collection.
{
crcbb_tpurchaseorderitemsid:ThisItem.'T-Purchase Order Items',
crcbb_description: tdescription.Text,
crcbb_item: Value(titem.Text),
crcbb_part: tpart.Text,
crcbb_qty: Value(tqty.Text),
crcbb_taccountid: taccount.Selected,
crcbb_total: Value(ttotal.Text),
crcbb_tpurchaseorderid: GalPurchaseOrders.Selected,
crcbb_tvesselid: tvessel.Selected,
crcbb_unitvalue: Value(tunitvalue.Text)
}.

Now comes the issue. I am trying to patch this collection back to the same datasource by using this function:
Patch( 'T-Purchase Order Items', CPOItemsChanges )
and I get the error:

Now, if I drop the 3 lookup columns (
crcbb_taccountid and crcbb_tvesselid and crcbb_tpurchaseorderid ) the patch function works and all the remaining fields are updated accordingly.

Patch(
'T-Purchase Order Items',
DropColumns(CPOItemsChanges,"crcbb_tvesselid","crcbb_taccountid","crcbb_tpurchaseorderid")
);

Any idea of what is this error about?
BTW, this is the collection showing one of this lookup fields.  All those 3 fields are listed as Record.

Even knowing that I can use forall as well (with loss in performance) I tried, but got the same error. 

Then, at this stage, I think the important here is to know why that is happening.

:

Thanks,

Leo

[v-qiaqi-msft]

Hi@leonunescouto,

Could you please tell me that what is the column type of these three fields, are all of them Lookup type or Choices type and If they are Choices, are they allow multi select?

Also I want to confirm with you that if you want to update an existing record?

I think there is something syntax wrong with your formula. You have a nested collection where has a table inside a collection.

I don't think you could patch the collection directly to the Dataverse, please modify your formula as below:

Patch(
'T-Purchase Order Items',
Lookup('T-Purchase Order Items',ID=xxx), // This determines which record you want to update
{
crcbb_tpurchaseorderitemsid:ThisItem.'T-Purchase Order Items',
crcbb_description: tdescription.Text,
crcbb_item: Value(titem.Text),
crcbb_part: tpart.Text,
crcbb_qty: Value(tqty.Text),
crcbb_taccountid: taccount.Selected.Value,
crcbb_total: Value(ttotal.Text),
crcbb_tpurchaseorderid: GalPurchaseOrders.Selected.Value,
crcbb_tvesselid: tvessel.Selected.Value,
crcbb_unitvalue: Value(tunitvalue.Text)
}
)

Note: I assume that all the field name in your collection are the same as those in your Dataverse Table.

Hope it could help you.

Regards,

Qi

[Anonymous]

Hi Qi,

Thanks for your response.

All those 3 columns are lookup (records from other tables).

What do you mean with table inside my collection?  the error mention exactly about this table.  But I can't figure out where this comes from.   I was assuming these lookup columns are stored as a record. How can I check?
Regarding the changes , when I use .selected.value I get this error:

Also, I tried to use the GUID of the reference table , but also got an error:

The reference below is where I realized that I can patch the collection straight to the datasource. (The only difference is that on this example I didn't see he using lookup fields on it).   And as I mentioned, it is working fine when I remove these columns from my collection.

https://matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/

Thanks again,

Leo

[v-qiaqi-msft · Accepted solution]

Hi@leonunescouto,

Could you please tell me that how you write the formula into the Items property of taccount, GalPurchaseOrders, tvessel ? (Important!)

Lookup field in Dataverse is a complex field type, so if you have this field type in a collection, it is hard for you to patch. 

I have a test on my side, please take a try as below:

Set Items property of the Combo Box as:

Choices([@QiTests].crba2_Testlookup)

Set OnSelect property of the Button:

Patch(QiTests,Defaults(QiTests),{Testlookup:DataCardValue5.Selected})

Note: Testlookup is my Lookup field in my table named 'QiTests'.

Regards,

Qi

[Anonymous]

Hi Qi,

I think you pointed my error,  the item property on those lookup columns.   It was pointing to the same table instead to point to the lookup table.

But now, when I point to the proper lookup table, its not allowing me to use that same formula.   Should I point to the guid column of the lookup table, right?

Thanks again, mate.

Leo

[LauraMay]

Hi Qi @v-qiaqi-msft, Would you be able to help me with a similar issue - Not being able to Patch to Lookup fields

I had great success using UpdateIf and Patch to bulk update a number of records in PowerApps.

I am now attempting to use the same solutions for a Power App embedded into a Power BI report as follows: -

1. Select Records in PowerBI

2. Show Records in PowerBI Gallery using PowerBIIntegration

3. I create a collection "KYCxCLCollection" from the gallery

   ClearCollect(KYCxCLCollection, GalleryPowerBIKYC.AllItems)

4. I use a checkbox within the NewForm to indicate where a user can update individual fields with a different value  before patching if they need to. (They are all Lookup fields apart from 'Applies To')

 If(CheckboxKycPackage.Value = true, UpdateIf(KYCxCLCollection, true, {'KYC Package Name': LookupKYCPackage_1.Selected.kycPackage}));
If(CheckboxAppliesTo.Value = true, UpdateIf(KYCxCLCollection, true, {'Applies To': ChoiceAppliesTo_1.Selected.Value}));
If(CheckboxRelatedParty.Value = true, UpdateIf(KYCxCLCollection, true, {'Related Party Name': LookupRelatedParty_1.Selected.kycPackage}));
If(CheckboxClause.Value = true, UpdateIf(KYCxCLCollection, true, {'Clause Name': LookupClause_1.Selected.clause}));
If(CheckboxListOfValues.Value = true, UpdateIf(KYCxCLCollection, true, {crb8f_kycpackagexclause_listofvaluesname: LookupListofValues_1.Selected.listOfValues}));

5. I create another collection from the initial collection but drop the IDs to ensure power Apps creates new records rather than updating existing records

ClearCollect(CopyMultipleKYCPackagesCollection, DropColumns(KYCxCLCollection, "crb8f_kycpackagexclauseid", "crb8f_kycpackagexclauseuid"));

6. But the final Patch statement, recognises the source being my inital CDS table "kycPackageXclause" does not seem to like this new Collection I have just created "CopyMultipleKYCPackagesCollection".

Patch(kycPackageXclause, CopyMultipleKYCPackagesCollection)

I can't understand why this works in a straightforward PowerApp, but doesnt work for a PowerApp reading from PowerBI. Any insight MOST HELPFUL, if you can understand everything I have written above!