Trouble Patching Collection with values in a Nested Gallery

[gregpio85]

Hello!  

I'm working on a project in PowerApps to help a team map out trade promotions.

For the datastructure, I have two SQL Server tables, trade_plan_detail_promo_group, and trade_plan_detail_promo_group_item with there being many promo_group_items to a single promo_group.

In PowerApps, to allow users to edit their promo groups and promo group items, I have a gallery that displays all promo groups, then a nested gallery that shows all promo_group_items related to that promo group. (See below).

When the user enters the screen, they can make any edits to the gallery that they like, then click save to write those values back to the database. I'm trying to only update the records that have been updated, so I have a change detector switch that compares what the record contains to what is in the text boxes.

When the user clicks save, I have the following code that I was hoping would push all the user input values into a collection, then patch the collection to the SQL table. However, when I click the button, I am unable to populate the collection 'trade_plan_detail_promo_group_item_line' with anything from the gallery. 

Clear(trade_plan_detail_promo_group_item_line);

ForAll(Filter(gal_trade_plan_detail_group_item_1.AllItems, switch_change_detector_4.Value) As ChangedRows,
Patch(trade_plan_detail_promo_group_item_line,  
    Defaults(trade_plan_detail_promo_group_item_line), {
        id: ChangedRows.id,
        trade_plan_detail_id: ChangedRows.trade_plan_detail_id,
        tactic_id: ChangedRows.dd_tactic_1.Selected.id,
        group_item_start_date: ChangedRows.dt_start_date_1.SelectedDate,
        group_item_end_date: ChangedRows.dt_end_date_1.SelectedDate,
        cost: Value(ChangedRows.txt_cost_1.Text),
        retail: Value(ChangedRows.txt_retail_1.Text),
        weeks: Value(ChangedRows.txt_weeks_1.Text),
        units: Value(ChangedRows.txt_units_wk_1.Text),
        fixed_type_id: ChangedRows.dd_fixed_type_2.Selected.id,
        fixed_amount: Value(ChangedRows.txt_fixed_amt_1.Text),
        modified_time: Now(),
        modified_user: User().Email
            }));

I have tried everything at this point. I redid the nested gallery as just a regular gallery (non nested) to confirm that it worked without being nested and it did. I tried a handful of other suggestions elsewhere, but was unable to get this particular piece working. 

Any help or advice on how to achieve this would be great! Thanks!

[Rusk · Accepted solution]

Hi @gregpio85 

I've also run into issues with nested galleries in my apps.  I am in the middle of reworking one now to try this suggestion instead:

https://www.matthewdevaney.com/group-the-items-in-a-power-apps-gallery/

With this method, instead of a nested gallery, create a "level" column and for your "headings" use level 1, for "nested" use level 2.   Then you will be able to achieve your successful non-nested functionality that you described above!

[Rusk]

I forgot to mention, I have been able to successfully patch a nested gallery by using "Lookup".  In one of those apps, which is similar to what you have (user edits a grid, then updates are saved), I used something like this:

Patch(colChanges,
LookUp(colChanges, ID = ThisItem.ID),
{Item: (GridNewItem.Text)}

[gregpio85]

Hi @Rusk ,

Thanks for the response and sorry for the late reply! Very creative approach!

I'm working on implementing on my end and am working through all of the unique problems specific to my implementation. 

Drop Downs, conditionally showing items, spacing, using lookups to patch, etc. 

Once I have a working app I'll mark as an accepted solution, but boy I wish Microsoft would allow us to reference AllItems for a nested gallery. That would make my life way easier. I'm sure it's not that simple, but would be a major time saver. 

Cheers!

[gregpio85]

Marking Rusk's first response as solution. I've made my way through this project enough to understand how the rest of it will work. 

For anyone following along with a similar infrastructure/problem, I created a view in database that combined the two tables I was looking to nest into one, marking which table each record was from, then I created a single gallery, using the view as the data source, and layered the different components in the gallery on top of each other and conditionally hid/showed them based on whether or not the record in the gallery was from the header table or the item table.

When the user is entering data, I use the column that differentiates between which table the record came from and an identity record to look up the record I want to update and use the UI components to patch the update. 

It's kind of a pain to conditionally hide items, but it's not bad once you get the hang of it.