Unanswered

PowerApps collection patch issue into SP List

(0) Share

Report

Posted on by mohitosh12

Hello Friends,

I would request support here on this Canvas app issue where I am trying to Add/Update records in Grid view to backend SharePoint List.

Data is stored temporarily on a collection called colGridData but here I see unnecessary records getting displayed as we can see in the below Grid view on the app (Screenshot below). We can see 7 records with valid ID coming from the backend list, but the rest are getting patched unwanted and if I click on Edit in Grid View button and then Exit Grid View then it is all those patched to the List. Also, they are displayed each time the colGridData collection is loaded through a button OnSelect function, code is displayed below.

ClearCollect(

colGridData,

Filter(

'WPP Decommission',

(IsBlank(filterPhaseCombo.Selected.Value) || IsEmpty(filterPhaseCombo.Selected.Value) || 'CI Phase'.Value = "Select CI Phase" || 'CI Phase'.Value = filterPhaseCombo.Selected.Value)

&& (IsBlank(filterstatusCombo.Selected.Value) || IsEmpty(filterstatusCombo.Selected.Value) || 'CI Status'.Value = "Select CI Status" || 'CI Status'.Value= filterstatusCombo.Selected.Value)

&& (IsBlank(filterPillarCombo.Selected.Value) || IsEmpty(filterPillarCombo.Selected.Value) || 'CI Pillar'.Value = "Select CI Pillar" || 'CI Pillar'.Value = filterPillarCombo.Selected.Value)

&& (IsBlank(filtergeoCombo.Selected.Value) || IsEmpty(filtergeoCombo.Selected.Value) || 'CI GEO'.Value = "Select CI GEO" || 'CI GEO'.Value= filtergeoCombo.Selected.Value)

&& (IsBlank(filterPMCombo.Selected.Value) || IsEmpty(filterPMCombo.Selected.Value) || 'Project Manager'.DisplayName = "Select Project Manager" || 'Project Manager'.DisplayName= filterPMCombo.Selected.Value)

)

I have shared galleryGrid OnSelect function and Edit in Grid View button OnSelect Code. This code is called via Select(Parent) function from OnChange function of each Input control in the galleryGrid.

So, for a workaround I am trying to delete these unwanted records before patching function via the following code executed from one the combobox control OnChange function.

RemoveIf(

colGridData,

ID = Blank()

)

galleryGrid OnSelect Function code –

Patch(

colGridData,

ThisItem,

{

'Project Name': projectName.Text,

'Project Manager': {

Claims: ProjectManager.Selected.Email,

DisplayName: ProjectManager.Selected.DisplayName,

Department: "",

Email: ProjectManager.Selected.Email,

JobTitle: "",

Picture: ""

'SNOW Ticket': snowTicket.Text,

'Asset ID': AssetID.Text,

'Source of Decom': {Value: sourceOfDecom.Selected.Value},

Product: {Value: ProductName.Selected.Value},

'Request Date': RequestDate.SelectedDate,

'CI Name': CIName.Text,

'CI Pillar': {Value: CIPillar.Selected.Value},

'CI OpCo': CIOpCo.Text,

'CI GEO': {Value: CIGEO.Selected.Value},

'CI IP': CIIP.Text,

'CI Description': CIDescription.Text,

'CI Phase': {Value: CIPhase.Selected.Value},

'CI Status': {Value: CIStatus.Selected.Value},

'Pre Decommission Start': CIPreDecomStart.SelectedDate,

'Cooling Start': CICoolingStart.SelectedDate,

'Diskwipe Start': CIDWStart.SelectedDate,

'Post Decommission Start': CIPostDecomStart.SelectedDate,

'CI End': CIEndDate.SelectedDate,

'Cancelled Date': CICancelledDate.SelectedDate,

'Cooling Change #': 'CoolingChg#'.Text,

'Cooling Start Date': CoolingChgStDate.SelectedDate,

'Server Shutdown Date': ShutdownDate.SelectedDate,

'Diskwipe Change #': 'DWChg#'.Text,

'Diskwipe Start Date': DWChgStDate.SelectedDate,

'Server Disk Wipe Date': DWDate.SelectedDate,

'Diskwipe End Date': DWChgEdDate.SelectedDate

}

)

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Sort by

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @mohitosh12 ,

You have Select(Parent) in OnChange of each Control within the Gallery, and OnSelect of Gallery to Patch the collection, which is incorrect. Please use the Patch function in OnChange of the Controls directly. I know it will make the formula seems everywhere on the Gallery Controls, but unless you use an icon or button for each line item, this is necessary.

You can refer to below video and get more info:

Power Apps Editable Table/Gallery like Excel (Tutorial) - YouTube

Best regards,

Was this reply helpful? Yes No
mohitosh12 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your suggestion.
I attempted to make a change to the patch function on the OnSelect function of galleryGrid (by following your posted YouTube video) where I am checking the collection has the item based on ID, if yes then I am updating the collection else patch, but it seems this is continuously patching existing records instead of adding them into the gallery which is getting displayed as well and app goes non responsive.
Now I will try your suggestion to use the patch function on the OnChange function of each input control instead of Select(Parent) function.
But I am wondering why is this happening with the patch, ideally this patch function should get triggered only when OnChange event is triggered against each input control in the gallery isn't it? This idea is taken from another YouTube video by Reza Dorrani.
(519) Editable Grid in Power Apps | SharePoint List - YouTube

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @mohitosh12 ,

In short, you have different scenario compared with the App in the video. The app in the video has a button to collect and add empty rows into the collection and more buttons with other functionalities.

Best regards,

Was this reply helpful? Yes No
mohitosh12 6 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-jefferni ,
I did add those buttons described in the video as shown below.
App OnStart Function
Set(
varGridEdit,
false
);
Set(
varNewRecord,
Defaults('WPP Decommission')
);
Set(
varNumber,
100000
)
btnLoadData OnSelect Function
ClearCollect(
colGridData,
Filter(
'WPP Decommission',
(IsBlank(filterPhaseCombo.Selected.Value) || IsEmpty(filterPhaseCombo.Selected.Value) || 'CI Phase'.Value = "Select CI Phase" || 'CI Phase'.Value = filterPhaseCombo.Selected.Value)
&& (IsBlank(filterstatusCombo.Selected.Value) || IsEmpty(filterstatusCombo.Selected.Value) || 'CI Status'.Value = "CI Status" || 'CI Status'.Value= filterstatusCombo.Selected.Value)
&& (IsBlank(filterPillarCombo.Selected.Value) || IsEmpty(filterPillarCombo.Selected.Value) || 'CI Pillar'.Value = "Pillar" || 'CI Pillar'.Value = filterPillarCombo.Selected.Value)
&& (IsBlank(filtergeoCombo.Selected.Value) || IsEmpty(filtergeoCombo.Selected.Value) || 'CI GEO'.Value = "GEO" || 'CI GEO'.Value= filtergeoCombo.Selected.Value)
&& (IsBlank(filterPMCombo.Selected.Value) || IsEmpty(filterPMCombo.Selected.Value) || 'Project Manager'.DisplayName = "Project Manager" || 'Project Manager'.DisplayName= filterPMCombo.Selected.Value)
&& (IsBlank(filterCINameCombo.Selected.'CI Name') || IsEmpty(filterCINameCombo.Selected.'CI Name') || 'CI Name' = "Select CI Name" || 'CI Name'= filterCINameCombo.Selected.'CI Name')
)
)

newItemButton OnSelect function
Set(
varNumber,
varNumber + 1
);
ForAll(
Sequence(Value(txtNumber.Text)),
Collect(
colGridData,
Patch(
varNewRecord,
{ID: varNumber}
)
)
)
editButton OnSelect function
If(
varGridEdit,
If(
!IsBlank(
LookUp(
colGridData,
IsEmpty('Project Manager') || IsBlank('Project Manager') || IsEmpty(Product) || IsBlank(Product) || 'CI Name' = Blank() || 'CI Name' = ""
)
),
Notify(
"Please fill mandatory fields (highlighed in Red border) to proceed with save",
NotificationType.Error,
2000
),
Patch(
'WPP Decommission',
UpdateIf(
colGridData,
Created = Blank(),
{ID: Blank()}
)
);
Notify(
"Grid data is updated",
NotificationType.Success,
2000
);
Set(
varGridEdit,
!varGridEdit
);
Reset(headerCheckbox);
Select(btnLoadData);
),
Set(
varGridEdit,
!varGridEdit
)
)

Few of the button OnSelect function code has been shared above where actions are happening. I would like to share with you that I had made a few code changes on the OnChange of each control where I am checking whether the input data change has happened like below. This has stopped patching those additional unwanted records except when I click on copyButton in the Edit mode (code is shared below).
If(
ThisItem.Product.Value <> ProductName.Selected.Value,
Select(Parent)
);
copyButton OnSelect Function
Set(
varNumber,
varNumber + 1
);
ForAll(
colSelected,
Collect(
colGridData,
Patch(
varNewRecord,
{
ID: varNumber,
'Project Name': ThisRecord.'Project Name',
'Project Manager': ThisRecord.'Project Manager',
'SNOW Ticket': ThisRecord.'SNOW Ticket',
'Source of Decom': ThisRecord.'Source of Decom',
Product: ThisRecord.Product,
'CI Pillar': ThisRecord.'CI Pillar',
'CI OpCo': ThisRecord.'CI OpCo',
'CI GEO': ThisRecord.'CI GEO',
'CI IP': ThisRecord.'CI IP',
'CI Description': ThisRecord.'CI Description'
}
)
)
);
Clear(colSelected);
Reset(headerCheckbox)

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @mohitosh12 ,

I cannot understand the formulas of newitembutton and editbuton. For example,

newItemButton OnSelect function

Set( varNumber, varNumber + 1 ); ForAll( Sequence(Value(txtNumber.Text)), Collect( colGridData, Patch( varNewRecord, {ID: varNumber} ) ) )

this seems no sense. Every time tap this button, it will add as many entries that ID = varNumber as the number in txtNumber input box.

In the video, the formula is quite simple compared with yours:

Set( varNumber, varNumber + 1 ); Collect( colGridData, Patch( varNewRecord, {ID: varNumber} ) )

Best regards,

Was this reply helpful? Yes No
mohitosh12 6 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-jefferni,
Good day.

Thanks for your further questions. This logic of input text for a number of records to patch to the collection is coming from another YouTube video of Reza @ (580) Power Apps Bulk Data Operations | Create, Update, Delete, Copy & Import | SharePoint List - YouTube
So, the use case of providing the variable number of records to patch is the requirement in my, so I implemented here. Every business logic applied in the app is inspired from Reza's YouTube video, may be somewhere I missed something causing the issue I reported.

Although now the issue only exists in the copyButton click and all the other clicks are not causing the issue which I reported in my initial post as I have added an IF condition to check if the value is changed from default by the user before Select(Parent) function in each input control in the galleryGrid. I am not sure why the OnChange event was getting triggered mostly on the combobox control even if the user wasn't changing the value.

Was this reply helpful? Yes No
mohitosh1202 6 on at

Like (2)

Report
Copy link

Link copied!

Hello @v-jefferni,
Good day.

It looks like the solution is found, with the following piece of code on the Gallery OnSelect function, the unnecessary record patching has been stopped. This is from one of the latest YouTube videos from Reza. This ensures that when the any data is edited then the current record is collected into the colGridData else Update the record based on ID.
If(
IsBlank(
LookUp(
colGridData,
ID = ThisItem.ID
)
),
Collect(
colGridData,
ThisItem
)
);
UpdateIf(
colGridData,
ID = ThisItem.ID,
{
Record patch code goes here
}
)

Was this reply helpful? Yes No

jamesmdc1

110 on at

Like (0)

Report

Copy link

Link copied!

I Thought I had this sorted myself, but now I'm having a heck of a time with this.

My edit in grid button "OnSelect"

If(
 varGridEdit,
 If(
 !IsBlank(
 LookUp(
 colGridData,
 IsBlank(ID)
 )
 ),
 Notify(
 "Error: Missing ID",
 NotificationType.Error,
 2000
 ),
 Patch(
 QUOTES2test,
 UpdateIf(
 colGridData,
 Created = Blank(),
 {ID: Blank()}
 )
 );
 Remove(
 QUOTES2test,
 colDelete
 );
 Clear(colDelete);
 ForAll(
 colDataImport,
 Collect(
 colGridData,
 Patch(
 varNewRecord,
 {
 ID: varNumber,
 TITLE: Blank(),
 PURPART: PartItem.Text,
 QUOTENAME: QuoteNameItem.Text,
 EC: ECitem.Text,
 SELLQTY: Value(SellQtyItem.Text),
 ANNUSAGE: Value(AnnualUsageItem.Text),
 PURQTY: Value(PurchaseQTYItem.Text),
 PURROUGH: PurchaseRoughItem.Text,
 LEADTIME: LeadtimeItem.Text,
 CONTQTY: Value(ContQTYItem.Text),
 FOB: FOBItem.Text,
 QUOTEDATE: DateValue(QuoteDateItem.Text),
 ROUGHWEIGH: Value(RoughWeighItem.Text),
 ROUGHPRICE: Value(RoughPriceItem.Text),
 LINESC: Value(LineSCItem.Text),
 HEATTREAT: Value(HeatTreatItem.Text),
 QUOTENUMBE: QuoteNumbeItem.Text,
 SUPPLERNUM: SupplerNumItem.Text,
 ACTMACH: Value(ACTmachItem.Text),
 TOTALSELL: Value(TotalSellItem.Text),
 QUOTESALE: Value(QuoteSaleItem.Text),
 ANNUALMAT: Value(AnnualMATerialItem.Text),
 ANNUALMACH: Value(AnnualMachItem.Text),
 TOOLING: Value(ToolingItem.Text),
 MACHMATPER: Value(MachMatperItem.Text),
 ALUMMETAL: Value(AlumMetalItem.Text),
 ADJUST: Value(AdjustItem.Text),
 FREIGHT: Value(FreightItem.Text),
 SUHOUR: Value(SuHourItem.Text),
 SETUP: Value(SetupItem.Text),
 CONTAINER: ContainerItem.Text,
 FOLLOWUP: FollowUpItem.Text,
 SCRAPCOST: Value(ScrapCostItem.Text),
 PROFIT: Value(ProfitItem.Text),
 DESC1: MachDesc1Item.Text,
 DESC2: MachDesc2Item.Text,
 PATT: Value(PATTitem.Text),
 ENGNOTES: EngNotesItem.Text,
 PAINT: Value(PaintItem.Text)
 }
 )
 )
 );
 Clear(colDelete);
 Notify(
 "Grid Data Updated",
 NotificationType.Success,
 2000
 );
 Select(btnLoadData);
 Set(
 varGridEdit,
 !varGridEdit
 )
 ),
 Set(
 varGridEdit,
 !varGridEdit
 )
);

And the "onSelect of "btnLoadData"

// Clear and collect filtered data into colGridData
ClearCollect(
 colGridData,
 Filter(
 QUOTES2test,
 StartsWith(
 PURPART,
 txtTitle.Text
 )
 )
);
// Increment varNumber and import data if colDataImport has rows
If(
 CountRows(colDataImport) > 0,
 Set(
 varNumber,
 varNumber + 1
 );
 ForAll(
 colDataImport,
 Collect(
 colGridData,
 Patch(
 Defaults(QUOTES2test),// Create a new row with default values.
 {
 PURPART: First(ThisRecord.Value.Result).Result,
 SOLDPART: Index(
 ThisRecord.Value.Result,
 2
 ).Result,
 QUOTENAME: Index(
 ThisRecord.Value.Result,
 3
 ).Result,
 EC: Index(
 ThisRecord.Value.Result,
 4
 ).Result,
 ANNUSAGE: Value(
 Index(
 ThisRecord.Value.Result,
 5
 ).Result
 ),
 PURQTY: Value(
 Index(
 ThisRecord.Value.Result,
 6
 ).Result
 ),
 SELLQTY: Value(
 Index(
 ThisRecord.Value.Result,
 7
 ).Result
 ),
 CONTQTY: Value(
 Index(
 ThisRecord.Value.Result,
 8
 ).Result
 ),
 PURROUGH: Index(
 ThisRecord.Value.Result,
 9
 ).Result,
 LEADTIME: Index(
 ThisRecord.Value.Result,
 10
 ).Result,
 FOB: Index(
 ThisRecord.Value.Result,
 11
 ).Result,
 CONTAINER: Index(
 ThisRecord.Value.Result,
 12
 ).Result,
 ROUGHWEIGH: Value(
 Index(
 ThisRecord.Value.Result,
 13
 ).Result
 ),
 ROUGHPRICE: Value(
 Index(
 ThisRecord.Value.Result,
 14
 ).Result
 ),
 LINESC: Value(
 Index(
 ThisRecord.Value.Result,
 15
 ).Result
 ),
 PAINT: Value(
 Index(
 ThisRecord.Value.Result,
 16
 ).Result
 ),
 HEATTREAT: Value(
 Index(
 ThisRecord.Value.Result,
 17
 ).Result
 ),
 QUOTENUMBE: Index(
 ThisRecord.Value.Result,
 18
 ).Result,
 QUOTEDATE: DateValue(
 Index(
 ThisRecord.Value.Result,
 19
 ).Result
 ),
 SUPPLERNUM: Index(
 ThisRecord.Value.Result,
 20
 ).Result,
 ACTMACH: Value(
 Index(
 ThisRecord.Value.Result,
 21
 ).Result
 ),
 SUHOUR: Value(
 Index(
 ThisRecord.Value.Result,
 22
 ).Result
 ),
 SETUP: Value(
 Index(
 ThisRecord.Value.Result,
 23
 ).Result
 ),
 SCRAPCOST: Value(
 Index(
 ThisRecord.Value.Result,
 24
 ).Result
 ),
 PROFIT: Value(
 Index(
 ThisRecord.Value.Result,
 25
 ).Result
 ),
 FREIGHT: Value(
 Index(
 ThisRecord.Value.Result,
 26
 ).Result
 ),
 MATMARKUP: Value(
 Index(
 ThisRecord.Value.Result,
 27
 ).Result
 ),
 TOTMACHINE: Value(
 Index(
 ThisRecord.Value.Result,
 28
 ).Result
 ),
 TOTALSELL: Value(
 Index(
 ThisRecord.Value.Result,
 29
 ).Result
 ),
 QUOTESALE: Value(
 Index(
 ThisRecord.Value.Result,
 30
 ).Result
 ),
 ANNUALMACH: Value(
 Index(
 ThisRecord.Value.Result,
 31
 ).Result
 ),
 ANNUALMAT: Value(
 Index(
 ThisRecord.Value.Result,
 32
 ).Result
 ),
 MACHMATPER: Value(
 Index(
 ThisRecord.Value.Result,
 33
 ).Result
 ),
 ALUMMETAL: Value(
 Index(
 ThisRecord.Value.Result,
 34
 ).Result
 ),
 ADJUST: Value(
 Index(
 ThisRecord.Value.Result,
 35
 ).Result
 ),
 TOOLING: Value(
 Index(
 ThisRecord.Value.Result,
 36
 ).Result
 ),
 MACHDESC1: Index(
 ThisRecord.Value.Result,
 37
 ).Result,
 MACHDESC2: Index(
 ThisRecord.Value.Result,
 38
 ).Result,
 PATT: Value(
 Index(
 ThisRecord.Value.Result,
 39
 ).Result
 ),
 DESC1: Index(
 ThisRecord.Value.Result,
 40
 ).Result,
 DESC2: Index(
 ThisRecord.Value.Result,
 41
 ).Result,
 ENGNOTES: Index(
 ThisRecord.Value.Result,
 42
 ).Result,
 FOLLOWUP: Index(
 ThisRecord.Value.Result,
 43
 ).Result
 }
 )
 )
 )
);
// Clear the colDataImport collection
Clear(colDataImport);

I also see an error when trying to enter text into a text field