Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

PowerApps collection patch issue into SP List

(0) ShareShare
ReportReport
Posted on by 6

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)

    )

)

mohitosh12_0-1682430483163.png

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

    }

)

 

 

  • jamesmdc1 Profile Picture
    jamesmdc1 110 on at
    Re: PowerApps collection patch issue into SP List

    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 

    jamesmdc1_0-1716388676020.pngjamesmdc1_1-1716388701876.png


    Any help is appreciated 

  • mohitosh1202 Profile Picture
    mohitosh1202 6 on at
    Re: PowerApps collection patch issue into SP List

    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
    }
    )

     

  • mohitosh12 Profile Picture
    mohitosh12 6 on at
    Re: PowerApps collection patch issue into SP List

    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.

  • v-jefferni Profile Picture
    v-jefferni on at
    Re: PowerApps collection patch issue into SP List

    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:

    vjefferni_0-1683187632987.png

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

     

    Best regards,

  • mohitosh12 Profile Picture
    mohitosh12 6 on at
    Re: PowerApps collection patch issue into SP List

    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)

  • v-jefferni Profile Picture
    v-jefferni on at
    Re: PowerApps collection patch issue into SP List

    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,

  • mohitosh12 Profile Picture
    mohitosh12 6 on at
    Re: PowerApps collection patch issue into SP List

    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

  • v-jefferni Profile Picture
    v-jefferni on at
    Re: PowerApps collection patch issue into SP List

    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,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard