Bulk Record Creation with Collections - Patching Multiple Records in Collections From ComboBox inside Gallery

[delluser]

I am attempting to create bulk records with collections and have them display in an editable grid in my PowerApp.

Summary:
To create new bulk records, I create 2 new collections, colBulk and colBulkCopy, to which I patch any specified number of new blank records, varNewRecord, to those collections. colBulk and colBulkCopy are duplicates at this stage. 

Bulk Add Button OnSelect:

Set(
    varNumber,
    varNumber + 1
);
ForAll(
    Sequence(Value(NumberOfRecordsToAdd.Text)),
    Collect(
        colBulk,
        Patch(
            varNewRecord,
            {
                ID: varNumber,
                'Field 1': Blank(),
                'Field 2': Blank()
})));
ForAll(
    Sequence(Value(NumberOfRecordsToAdd.Text)),
    Collect(
        colBulkCopy,
        Patch(
            varNewRecord,
            {
                ID: varNumber,
                'Field 1': Blank(),
                'Field 2': Blank()
})));

I am displaying colBulk in my gallery (colBulk is in the gallery Items property), which displays fields as editable ComboBoxes filled with selections pulled from a SharePoint List. To extract data from the ComboBoxes, I need to use the format ComboBox1.Selected.Result, etc. For OnChange of the ComboBoxes, I select my gallery, Gallery1. When my gallery is selected, it needs to patch all ComboBox selections that were just inputted in the editable grid across ALL the new bulk records that were just added to my collection colBulkCopy. In other words, I need to patch multiple records from one collection (colBulk, which is displayed in my gallery) to another collection (colBulkCopy). I attempted to use a Patch and ForAll formula in my gallery's OnSelect property to accomplish this. I tried a couple different iterations of the Gallery OnSelect.

For all attempts, I am bulk adding 2 new blank records and filling out both records in my editable grid gallery display by making ComboBox selections.

Gallery1 OnSelect Attempt 1: 

Patch(colBulkCopy, ForAll(colBulk,
{
    ID: ID,
    'Field 1': Field1Combo.Selected.Result,
    'Field 2': Field2Combo.Selected.Result
}));

Results: 2 new records are created in colBulkCopy with the ID field populated, but Field 1 and Field 2 do not get populated with the inputted ComboBox selections.

Gallery1 OnSelect Attempt 2: Using Gallery1.AllItems instead of colBulk

Patch(colBulkCopy, ForAll(Gallery1.AllItems as new,
{
    ID: new.ID,
    'Field 1': new.Field1Combo.Selected.Result,
    'Field 2': new.Field2Combo.Selected.Result
}));

 Results: Same as above - 2 new records are created in colBulkCopy with the ID field populated, but Field 1 and Field 2 do not get populated with the inputted ComboBox selections.

Note: In both the 2 newly added records in both attempts, the ID (created by ID: varNumber in my bulk add button) is the same value for both. It does not generate a unique ID. So both new records in each iteration, for example, could have their ID as 2000. I have attempted to generate a new unique ID by using the following code to generate a temporary table from colBulk and creating a field rowNumber:

With({records: colBulk}, ForAll(Sequence(CountRows(records)),Patch(Last(FirstN(records,Value)),{rowNumber: Value})))

Result: Temp table created of colBulk with rowNumber column populated with a unique ID number for each record in colBulk.

However, I don't know how to incorporate any of that into either of my actual collections.

Also, for both of my Gallery OnSelect attempts, it does not matter if I instead store the value of Field1Combo.Selected.Result in a text label as Value(Field1ComboSelectedResultLabel).Text and use that for the patch to Field 1 rather than using Field1.Combo.Selected.Result. I get the same results.

Originally, I was only adding 1 new blank record and my patch formula worked successfully to patch the inputted ComboBox selections in the new blank record in the editable grid gallery display into colBulk. 

Gallery1 OnSelect Single Record Add

Patch(colBulkCopy, LookUp(colBulkCopy, ID=ThisItem.ID),
{
    'Field 1': Field1Combo.Selected.Result,
    'Field 2': Field2Combo.Selected.Result
});

 However, when I attempt to use the above code when adding 2 records, the patch creates 2 new records in colBulk and only writes field values to the first record. It overwrites the first record's values with newly inputted values when the second record is inputted in the gallery, and it always leaves the second record in colBulk blank. I believe this is because "LookUp(colBulkCopy, ID=ThisItem.ID)" specifically points to only one record at a time, so > 1 record bulk patching would not work.

Thanks in advance for the help, it's much appreciated. 

[RandyHayes]

@delluser 

Hate to say it but...you are highly overcomplicating this!!  You don't need to do all of these "development like" activities in PowerApps...it is much simpler.

First, you only need a collection if your gallery rows are expected to expand or lessen (i.e. add rows, remove rows).  Everything else can be done from the Gallery itself.

You are also doing things with the ID...I would highly recommend that you leave that alone.  This is the key that will be needed during the patch to your datasource.  You will not be able to set the ID in the datasource, so doing anything with it is subject to issues.

You do not need to do anything with the OnChange actions of the comboboxes either.  Those controls will have their values that you can just get when you need.

There are a couple of additional bits of information that are important for your base gallery collection (again, only needed if you are adding or removing records), but that involves some more detail on how you are actually populating the Gallery Items property (you mentioned by collection from SharePoint, but more detail is needed on that).

Once you perform your bulk update/patch, it all generates from the Gallery data.  The ID is highly important so that Patch not only knows what record to update, but also if a record needs to be created.

If this is helpful as-is...go for it.  If you have questions, please provide some more details on the Items of your Gallery and the current "concept formula" you have for your bulk updating.

I hope this is helpful for you.

[delluser]

Hey Randy, 

Thanks for the response. I'm not quite sure what you mean by "First, you only need a collection if your gallery rows are expected to expand or lessen (i.e. add rows, remove rows)", as that is exactly what I am trying to do- bulk add rows (and also bulk delete rows, which is why I thought using collections was necessary.

To give you more information about the current state, I use a button "Load Data Button" to collect colBulk from SharePoint ('SharePoint List') in a large cascading If statement with Filters to allow for cascading dropdowns to filter on colBulk and therefore my gallery. So colBulk is a direct pull from my SharePoint List. Here is that button's OnSelect property:

ClearCollect(colBulk,
If(IsBlank(Field2DD.Selected.Result), If(IsBlank(Field1DD.Selected.Result), 'SharePoint List', Filter('SharePoint List', 'Field 1' = Field1DD.Selected.Result)), Filter(If(IsBlank(Field1DD.Selected.Result), 'SharePoint List', Filter('SharePoint List', 'Field 1' = Field1DD.Selected.Result)),'Field 2'= Field2DD.Selected.Result))

I display colBulk in my gallery. In my gallery's Items property, I have the following:

If(!IsBlank(Field1Combo.Selected.Result), Filter(colBulk, 'Field 1'=Field1Combo.Selected.Result),Blank())

 This is because I need my gallery to display items in colBulk with that particular filter criteria based on my Field 1 ComboBox, and if it's blank, I need the gallery to be blank.

In a perfect world, after I successfully patch selected ComboBox values into new records/records I'm updating in my editable grid gallery, I have a "Save" button that Patches colBulk to my SharePoint List using an UpdateIf formula to accomodate for any varNewRecords I may be patching over.

OnSelect of the Save button:

Patch('SharePoint List', UpdateIf(colBulk, Created=Blank(), {ID:Blank()}));
Select(LoadDataButton);

Basically, if I was patching over any of those newly added records, they started out as my blank varNewRecords so they had that "fake ID" . I identify these here because the created field is going to be blank (ie. it hasnt ever been in SP, and created comes from SP), and I patch over the existing fake ID with a blank value so that when its actually patched to SP, SP can generate the SP ID there.

Let me know if you still feel like a collection is not appropriate here. Otherwise, I'm really just looking for a way to successfully Patch more than just one new blank record to colBulk. It works fine when I just add one new record, edit it in my gallery display with the ComboBoxes, and trigger the Patch with the ComboBox's OnChanges.

Thanks so much again for taking the time to read this.

[RandyHayes]

@delluser 

Before I jump into some of the specifics...

You seem to be focused on updating the collection - what is the purpose of that?  There really is no need to do anything with the base collection except to exists with a record ID (and there is one other little trick on that needed).  But beyond that, then the Gallery is your table...it has everything you need for the return to the datasource.

So, I just want to get an idea on what you are trying to then update the collection for, as well as the datasource.  I don't want to "clobber" that need if there is one.