Updating a Gallery - Detecting When Gallery Data Has Been Refreshed?

[Anonymous]

I have a canvas app with two galleries.  When a user name column is clicked in gallery 1, it sets the visibility of gallery 2 to true and updates the items in the gallery per a complex formula.  Since the formula is lengthy, it can take a few seconds to update gallery 2.  Therefore I need to add a preloader to gallery 2 but I'm not sure how I would determine when the new data is loaded (old data remains visible while formula executes. There are 2 variables (varDealTableSelUser & varDealTableRole) used in gallery 2 that contribute to the delays in updating gallery 2.

// Gallery 1 - OnSelect (user name)
Set( varDealTableVis, true ); 
Set( varDealTableSelUser, ThisItem.Assigned_x002d_Full_x002d_Name ); 
Set( varDealTableRole, "Bid Manager");

// Gallery 2 - Items
Sort(
With(
   { 
      wManagers:
      With(
         {
            wActiveDI:
            AddColumns(
               ShowColumns(
                  'Deal-Intake-App', 
                  "Title", 
                  "Close_x002d_Date", 
                  "SFDC_x002d_Opp_x002d_Name", 
                  "SFDC_x002d_Opp_x002d_ID" 
               ), 
               "Active",  
               If(
                  Close_x002d_Date > Today(), 
                  "Yes", 
                  "No"
               )
            ),
            wBidMgrs: 
            ShowColumns(
               Filter(
                  'Assigned-Users', 
                  Role_x002d_Assigned = varDealTableRole
               ), 
              "Deal_x002d_Request_x002d_ID", 
              "Assigned_x002d_Full_x002d_Name", 
              "Role_x002d_Assigned"
            )
         },
         AddColumns( 
            ShowColumns(
               wBidMgrs, 
               "Deal_x002d_Request_x002d_ID", 
               "Assigned_x002d_Full_x002d_Name"
            ), 
            "Active", 
            If(
               LookUp(
                  wActiveDI, 
                  Title = Deal_x002d_Request_x002d_ID, 
                  Close_x002d_Date
               ) > Today(), 
               "Yes", 
               "No"
            )
         )
      )
   },
   // NEW SECTION [Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser]
   AddColumns(
        Filter(
            wManagers,
            If( !toggleView, Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser, Assigned_x002d_Full_x002d_Name = varDealTableSelUser)
            //Assigned_x002d_Full_x002d_Name = varDealTableSelUser
        ),
        "daCloseDate", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, Close_x002d_Date),
        "dealName", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, SFDC_x002d_Opp_x002d_Name),
        "oppID", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, SFDC_x002d_Opp_x002d_ID)
    )
), daCloseDate, Ascending)

 Gallery 1 (right) displays users and their number of active deals.  When a name is clicked in gallery 1, gallery 2 (left) displays the active deals for the selected user (see screen shots below).  How can I detect when gallery 2 has been updated/reloaded after the user clicks a name in gallery 1? 

[RandyHayes]

@Anonymous 

Your Gallery2 Items formula is over-complex and needs not be so much data activity.  For example, you are using the ShowColumns function in your formula that will just slow down your formula evaluation.  Unless you have a very specific need to only show certain columns (this is not one), then you should avoid it.  If you have thought that using ShowColumns would make you app faster because it is only "getting"  those columns, this is a misunderstanding.  PowerApps will still gather ALL of the columns of a table to then feed to a ShowColumns function to shape the data table to only have those columns.  So, you're already getting all the columns and then taking time to remove/show only certain columns.  It's not needed.

Also, in your formula, you are performing three lookups on the same 'Deal-Intake-App' data source.  This is another area of slow down.  What should be done is to gather the record once in the formula and then reference the columns you want.  This IS a good place for a ShowColumns (potentially).

To combine the above concepts, your formula can become the following:

Sort(
  With(
   {wManagers: 
       With({wActiveDI: AddColumns('Deal-Intake-App', "Active",  If(Close_x002d_Date > Today(), "Yes", "No")),
            wBidMgrs: Filter('Assigned-Users', Role_x002d_Assigned = varDealTableRole)
         },
         AddColumns( wBidMgrs, "Active", If(LookUp(wActiveDI, Title = Deal_x002d_Request_x002d_ID, Close_x002d_Date) > Today(), "Yes", "No"))
      )
   },
   // NEW SECTION [Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser]
   ForAll(
        Filter(
            wManagers,
            (!toggleView && Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser) ||
            (toggleView && Assigned_x002d_Full_x002d_Name = varDealTableSelUser)
            //Assigned_x002d_Full_x002d_Name = varDealTableSelUser
        ) As _item,
        Patch(_item, LookUp(ShowColumns('Deal-Intake-App', "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID", "DI-ID"), 'DI-ID' = _item.Deal_x002d_Request_x002d_ID)
    )
  ), 
  Close_x002d_Date
)

Note, the three added columns will now be the original column names from the 'Deal-Intake-App' table rather than the names you had.  Unless there is a particular reason to rename the columns with the AddColumns you had, I would avoid it as it is only adding more time to your formula and more work for you.

Beyond the performance on the Items filter itself, the only way to make sure the second gallery has all of its information is to provide it all to it.

This would involve looking at your first gallery items property and incorporating the second into the first.  This way your second gallery would only be referring to the records associated with the first gallery selection based on the formula above that would be in it.

I hope this is helpful for you.

[Anonymous]

@RandyHayes - Thanks for the streamlined formula.  Unfortunately, it has some errors and I have working code and I need to tackle the gallery preloader issue.  

Regarding this suggestion: "Beyond the performance on the Items filter itself, the only way to make sure the second gallery has all of its information is to provide it all to it.  This would involve looking at your first gallery items property and incorporating the second into the first.  This way your second gallery would only be referring to the records associated with the first gallery selection based on the formula above that would be in it."

I think I need to clarify the issue. 

• Gallery 1 displays two columns: User and # of Active Deals. 
• Gallery 2 uses that complex formula and displays ALL the active deals for the user selected in gallery 1 and displays 6 columns:  Bid Mgr, Opp Name, Opp ID, Close Date, Deal ID, and Active (Y/N) . 
• The second gallery already refers to the records associated with the first gallery selection.

As a result, the data is different between the two galleries.  When a user name column is clicked in gallery 1, it sets the visibility of gallery 2 to true and updates the items in the gallery per a complex formula that relies on 2 vars that change each time you click a new name in gallery 1.  How would I determine when the new data is loaded (old data remains visible while formula executes)?  Perhaps a collection could be updated after a name is selected in gallery 1 but how would I detect that new data has been added to the collection?

[RandyHayes]

@Anonymous 

There is no gallery pre-loader, so you need to work out the issues with the formula in it.  I suggested how to make it more streamlined for performance, so I would try to figure out the issues with it and correct.  Even though you have a working formula, it is causing you issues.

A collection is not going to do anything more than slow down your app even more as you will just be duplicating information you already have.

The solution to resolve this is to gather the information IN the first Gallery.  You are doing more "formula" than you need to and it is causing what you are seeing.  I would incorporate the entire formula together.  You will take a little more of a performance hit in the initial screen load, but your performance with the second gallery will be instantaneous. 

You cannot detect the type of things you are thinking about detecting, so the best answer is to have the data already at hand, then there absolutely no delays.

If you can give me more information about the first gallery Items property, perhaps I can suggest some ways to combine this successfully and get you what you need.

[Anonymous]

Thanks @RandyHayes for the detailed response.  I was planning on using a preloader component but as you said there is no way to detect a gallery reload.  Here is the code for both galleries.  It took a long time with some help from a forum member to get working code so here it is. I'm very interested in your alternate approach to improve performance and loading times.  

// Gallery 1 Items
Sort(
With(
   { 
      wManagers:
      With(
         {
            wActiveDI:
            AddColumns(
               ShowColumns(
                  'Deal-Intake-App', 
                  "Title", 
                  "Close_x002d_Date", 
                  "SFDC_x002d_Opp_x002d_Name", 
                  "SFDC_x002d_Opp_x002d_ID" 
               ), 
               "Active",  
               If(
                  Close_x002d_Date > Today(), 
                  "Yes", 
                  "No"
               )
            ),
            wBidMgrs: 
            ShowColumns(
               Filter(
                  'Assigned-Users', 
                  Role_x002d_Assigned = "Bid Manager"
               ), 
              "Deal_x002d_Request_x002d_ID", 
              "Assigned_x002d_Full_x002d_Name", 
              "Role_x002d_Assigned"
            )
         },
         AddColumns( 
            ShowColumns(
               wBidMgrs, 
               "Deal_x002d_Request_x002d_ID", 
               "Assigned_x002d_Full_x002d_Name"
            ), 
            "Active", 
            If(
               LookUp(
                  wActiveDI, 
                  Title = Deal_x002d_Request_x002d_ID, 
                  Close_x002d_Date
               ) > Today(), 
               "Yes", 
               "No"
            )
         )
      )
   },
   AddColumns(
      GroupBy(
         Filter(
            wManagers,
            Active="Yes"
         ),
         "Assigned_x002d_Full_x002d_Name",
         "OtherData"
      ),
      "BidsDone",
      CountRows(OtherData)
   )
), Assigned_x002d_Full_x002d_Name, Ascending)


// Gallery 2 Items
Sort(
With(
   { 
      wManagers:
      With(
         {
            wActiveDI:
            AddColumns(
               ShowColumns(
                  'Deal-Intake-App', 
                  "Title", 
                  "Close_x002d_Date", 
                  "SFDC_x002d_Opp_x002d_Name", 
                  "SFDC_x002d_Opp_x002d_ID" 
               ), 
               "Active",  
               If(
                  Close_x002d_Date > Today(), 
                  "Yes", 
                  "No"
               )
            ),
            wBidMgrs: 
            ShowColumns(
               Filter(
                  'Assigned-Users', 
                  Role_x002d_Assigned = varDealTableRole
               ), 
              "Deal_x002d_Request_x002d_ID", 
              "Assigned_x002d_Full_x002d_Name", 
              "Role_x002d_Assigned"
            )
         },
         AddColumns( 
            ShowColumns(
               wBidMgrs, 
               "Deal_x002d_Request_x002d_ID", 
               "Assigned_x002d_Full_x002d_Name"
            ), 
            "Active", 
            If(
               LookUp(
                  wActiveDI, 
                  Title = Deal_x002d_Request_x002d_ID, 
                  Close_x002d_Date
               ) > Today(), 
               "Yes", 
               "No"
            )
         )
      )
   },
   // NEW SECTION [Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser]
   AddColumns(
        Filter(
            wManagers,
            If( !toggleView, Active="Yes" && Assigned_x002d_Full_x002d_Name = varDealTableSelUser, Assigned_x002d_Full_x002d_Name = varDealTableSelUser)
            //Assigned_x002d_Full_x002d_Name = varDealTableSelUser
        ),
        "daCloseDate", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, Close_x002d_Date),
        "dealName", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, SFDC_x002d_Opp_x002d_Name),
        "oppID", LookUp('Deal-Intake-App', 'DI-ID' = Deal_x002d_Request_x002d_ID, SFDC_x002d_Opp_x002d_ID)
    )
), daCloseDate, Ascending)

[RandyHayes]

@Anonymous 

As I had kind of imagined from your prior posts.  You are Grouping by the same data for Gallery1 and then completely discarding the value of the "OtherData" records. 

Essentially what you are doing in these formulas is to create a wActiveDI variable with a shaped record table from 'Deal-Intake-App' and an added column called Active (that is never used anywhere else in your formula).

Then you are shaping the Assigned-Users table records based on a filter by Role assigned into a vBidMgrs variable.

Finally, you are creating a table for vManagers that is the based on a data shape of the vBidMgrs table and adding a column based on the lookup into the wActiveDI table based on the Title to determine if it is active (which you already did once in the wActiveDI and never used).

Finally, you are grouping that data on a filter of the vManagers table where active is yes and are grouping by the Assigned Full Name.  All of the Matching records are in the column OtherData - which you never use again except to count the rows for an added column.

THEN - in your Gallery2, you are doing the exact same process over again except refining by the grouped information...you already have it in the OtherData column!! 

So, yes, you are taking some very big performance hits doing it this way.

Here is my suggestion:

Gallery1 Items property:

Sort(
    AddColumns(
       GroupBy(
          ForAll(
              Filter('Assigned-Users', Role_x002d_Assigned = "Bid Manager") As _item,
              Patch(_item, 
                  With(LookUp('Deal-Intake-App', Title = Deal_x002d_Request_x002d_ID),
                      {daCloseDate: Close_x002d_Date,
                       dealName: Deal_x002d_Request_x002d_ID, SFDC_x002d_Opp_x002d_Name,
                       oopID: SFDC_x002d_Opp_x002d_ID,
                       Active: Close_x002d_Date > Today()
                      }
                  )
              )
          ),
          "Assigned_x002d_Full_x002d_Name",
          "OtherData"
      ),
      "BidsDone", CountRows(OtherData)
    ),
    Assigned_x002d_Full_x002d_Name
)

Gallery1 OnSelect action formula:

Set( varDealTableVis, true); 

Really don't need this unless there is a reason to make the gallery visible or not.

Gallery2 Items property:

Filter(Gallery1.Selected.OtherData,
   (!toggleView && Active) || toggleView 
)

Keep in mind that I have typed all of the above formulas free-hand, so there might be some syntax issues that I am not noticing.  But the concept of the above should give you what you want and you will have NO delay in the second gallery.

[Anonymous]

@RandyHayes - Thanks for the detailed code and explanation.  I know I reused a lot of code but I was up against a deadline and needed an immediate solution.  Your solution omits one important thing:  the bottom table relies on a specific user being present in the query (done by setting a var 'varDealTableSelUser' when a user is selected in the top table) since the bottom table is user-specific. Role is also passed to the bottom table vis the via 'varDealTableRole'.  This is because the bottom table has to work for all roles and I am displaying the role above the gallery as a header.  Here is what the Resource Dashboard page looks like. 

[RandyHayes]

@Anonymous 

So the bottom gallery (I assume that is the one we've been calling Gallery 2) is accounted for in the Items of Gallery1.

You are grouping by the Assigned Full Name.  So, all the records in the OtherData column will be only pertaining to that user.

Before you were doing another filter of that name in the Gallery2 items.  There is no need for that with this formula because the GroupBy has already done that for us in the OtherData column records.

So, the Gallery 2 items just references that selected record and the OtherData column...it is only for that user selected.

As for the Role, you were setting that to "Bid Manager" in your original.  This is accounted for in the Gallery1 Items already.

What am I missing?

[Anonymous]

There are many other roles in the other utilization galleries.  I need to pass the role info because the bottom table has to work for all roles and I am displaying the role above the gallery as a header.  Does that answer your question?

[RandyHayes]

@Anonymous 

Yes, but I guess what I am saying is that, based on the formulas you sent me, the only role filtering you did was for the Bid Manager.  So, since I don't know the intricacies of your app, I can only go by what I saw in the formulas.

So, I'm not sure exactly what you're trying to do with the roles.  Perhaps we need to explore what you are doing in the other galleries?