SQL View data not refreshing in app

[joshbyra]

Hi everyone,

I'm working on a canvas app that allows users to create a new, or select a previously entered data item from a gallery [glryRecentEntries_1] on the landing page [scrnLandingPage] for editing in a gallery control [glryNewEdit] on a second screen [scrnActivities].  The data is stored on a set of normalized SQL tables, and pulled into a collection [TransactionCollection2] in the application through a consolidated SQL View. 

For the most part, it works as intended, but in testing, we've observed a problem: newly created items and edits to previously created items are not showing when the user submits their changes and goes back to review the item within the app immediately afterward, even though we are refreshing the relevant data connections/views within the navigation button actions and OnVisible events.   The changes do show the 2nd time the user returns to the landing page and tries to view them, but not beforehand, regardless of time elapsed.  For example, I tried making some changes, submitting them, and walking away for 20 minutes before checking to see if the data had updated within the app, and it had not.  Can anyone help me to correct this?

For context, when the application is run or the user navigates back to scrnLandingPage (which can be seen in attached Screenshot1.jpg), the OnVisible action is as follows:

Refresh('[dbo].[Productivity_LandingPage_v]');
Set(
    varusername,
    Office365Users.MyProfile().Surname & ", " & Office365Users.MyProfile().GivenName
);
ClearCollect(
    LandingPageCollection,
    Filter(
        '[dbo].[Productivity_LandingPage_v]',
        emp_name = varusername
    )
)

Please see Screenshot1.jpg.  Let's say the user wants to update a previous entry.  They would click on one of the gallery items, triggering the following Action:

Select(Parent);
Set(varLoading, true);
Refresh('[dbo].[Productivity_Work_v]');
ClearCollect(
    TransactionCollection2,
    ForAll(
        Filter('[dbo].[Productivity_Work_v]', emp_name=varusername),
        If(
            emp_name = ThisItem.emp_name && Work_Transaction_Date = ThisItem.Work_Transaction_Date && productivity_team_name = ThisItem.productivity_team_name,
            {
                TransID: CountRows(TransactionCollection2) + 1,
                AddComment: !IsBlank(Work_Comments),
                Supervisor: Work_Sup_Name,
                WorkID: Work_id,
                TransType: TaskName_Name,
                TransTypeID: TaskList_ID,
                VolMin: Work_TimeSpent_Count,
                Comment: Work_Comments,
                TeamID: productivity_team_name, Deleted: false
            }
        )
    )
);
Set(
    ItemDate,
    Text(Work_Transaction_Date)
);
Navigate(
    scrnActivities,
    ScreenTransition.Cover
);
If(ItemDate = "New",Collect(
    TransactionCollection2,
    {
        TransType: "",
        VolMin: 0,
        Comment: ""
    }
));
Set(
    varLoading,
    false
)

As you can see, this collects the relevant data into TransactionCollection2, which underlies the gallery that can been seen in attached Screenshot2.jpg.    scrnActivities (as shown in Screenshot2.jpg)  has the following as its OnVisible action:

Set(
    varLoading,
    true
);
ClearCollect(
    colFilteredTasklist,
    If(
        IsBlank(ddTeamName.SelectedText.Value),
        '[dbo].[Productivity_TaskList_v]',
        (Filter(
            '[dbo].[Productivity_TaskList_v]',
            /*LOB_Name = ddLOB.Selected.Result,
            SOR_Name = ddSOR.Selected.Result,*/
            productivity_team_name = ddTeamName.SelectedText.productivity_team_name
        ))
    )
);
Set(
    varLoading,
    false
)

The volume on the item in Screenshot2 starts as 33, and the user changes it to 44, as pictured, then clicks the diskette icon on the right to patch TransactionCollection2  with the change, as follows:

Patch(
    TransactionCollection2,
    ThisItem,
    {
        TransType: cboxTransactionType.Selected.TaskName_Name,
        TransTypeID: cboxTransactionType.Selected.TaskList_ID,
        VolMin: Value(txtVolMin.Text),
        Comment: txtComments.Text,
        AddComment: cbComment.Value
    }
)

Once they have done so, they would then click the "Submit Entries" button at the top right of the screen to commit these changes to the SQL table, as follows:

Set(
    varLoading,
    true
);
RemoveIf(
    TransactionCollection2,
    VolMin = 0 || IsBlank(VolMin) || IsBlank(TransTypeID)
);
If(
    ItemDate = "New",
    ForAll(
        TransactionCollection2,
        Patch(
            '[dbo].[PRODUCTIVITY_WORK]',
            Defaults('[dbo].[PRODUCTIVITY_WORK]'),
            {
                Work_Emp_ID: ddEmployeeID.SelectedText.emp_id,
                Work_Transaction_Date: dpTransactionDate.SelectedDate,
                Work_TimeSpent_Count: VolMin,
                TaskList_ID: TransTypeID,
                Work_Comments: Comment,
                Work_Sup_Name: lblSupervisor.Text
            }
        )
    ),
    ForAll(
        TransactionCollection2,
        If(
            TransactionCollection2[@Deleted] = true,
            Remove(
                '[dbo].[PRODUCTIVITY_WORK]',
                LookUp(
                    '[dbo].[PRODUCTIVITY_WORK]',
                    Work_id = TransactionCollection2[@WorkID]
                )
            ),
            Patch(
                '[dbo].[PRODUCTIVITY_WORK]',
                If(
                    IsBlank(TransactionCollection2[@WorkID]),
                    Defaults('[dbo].[PRODUCTIVITY_WORK]'),
                    LookUp(
                        '[dbo].[PRODUCTIVITY_WORK]',
                        Work_id = TransactionCollection2[@WorkID]
                    )
                ),
                {
                    Work_Emp_ID: ddEmployeeID.SelectedText.emp_id,
                    Work_Transaction_Date: dpTransactionDate.SelectedDate,
                    Work_TimeSpent_Count: VolMin,
                    TaskList_ID: TransTypeID,
                    Work_Comments: Comment,
                    Work_Sup_Name: lblSupervisor.Text
                }
            )
        )
    )
);
Refresh('[dbo].[Productivity_LandingPage_v]');
ClearCollect(
    LandingPageCollection,
    '[dbo].[Productivity_LandingPage_v]'
);
Set(
    varusername,
    Office365Users.MyProfile().Surname & ", " & Office365Users.MyProfile().GivenName
);
Navigate(
    scrnLandingPage,
    ScreenTransition.UnCover
);
Set(
    varLoading,
    false
)

As you can see, the user is automatically navigated back to the landing page, as in Screenshot1.jpg.  The problem is that if they were to click on the item they just edited to review, they'll be brought into the edit screen, but the volume will still be showing as 33, even though they updated it to 44 and wrote the change to the table (other changes they make will also not appear to be reflected).   Similarly, if the user creates a whole new item, and save/submits as above, the new item will not show in glryRecentEntries_1 right away. 

Anyway, at this point, if the user clicks the "Back" button, and then clicks on the same item on the landing screen, the changes will show as they should, but the requirement is that the changes are immediately visible within the tool.  As you can see, we have Refresh() commands for the relevant views in the landing page OnVisible action and navigation buttons, and in the "Submit Entries" button, but it's as if they don't work. 

Any idea why this is happening, and how to fix?

Thank you!

-Josh

[v-yutliu-msft]

Hi @joshbyra ,

Firstly , you should use Refresh(View_table) instead of Refresh(table).

Secondly , you could refer this to speed up refresh:

https://powerusers.microsoft.com/t5/General-Discussion/Refresh-SqL-database-taking-ages/m-p/261519

Thirdly, someone has met a similar issue, you could refer this:

https://powerusers.microsoft.com/t5/General-Discussion/Power-Apps-SLOW/m-p/240902#M70666

According to his description, if you make your app smaller, this problem may be improved. 

Best regards,

Community Support Team _ Phoebe Liu

[drewski]

Hello - I'm also working with Josh to try and solve this issue.

Can you elaborate on the View_Refresh vs. Refresh? I don't see any other way to write the Refresh command except for Refresh(table).

The refreshes that we are doing are directly linked to a SQL View already so it should be refreshing the view itself.

The weird thing is when you try to load an item, it first loads with old data, but if you go back to the first screen again and load it - the data is now correct. It's only the FIRST time we try and pull the line item back from SQL that we notice the data loss issue.