cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leonunescouto
Advocate I
Advocate I

Patching a collection back to datasource

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:

leonunescouto_0-1611206547892.png

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.

leonunescouto_1-1611206808256.png

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

Best Regards,
Qi

View solution in original post

5 REPLIES 5
v-qiaqi-msft
Community Support
Community Support

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

Best Regards,
Qi

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:

leonunescouto_1-1611242045595.png

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

leonunescouto_0-1611241803170.png

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

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

 

Best Regards,
Qi

View solution in original post

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.

 

leonunescouto_0-1611331036337.png

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?

leonunescouto_1-1611332876487.png

 

Thanks again, mate.

 

Leo

 

LauraMay
Advocate I
Advocate I

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!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,315)