Azure SQL - Edited Dropdown fields via Submit Form not showing in Powerapps for 10mins

[ghostwhowalkz]

Hi all,

I have an issue within my Azure SQL based Powerapp. The database comprises of a variety of tables, with various relationships, eg. accounts, work sites, products, work orders, etc. The app UI is mainly through galleries and detailed views of selected items, which then allows editing via forms, uploading of photos/files, etc.

Out of the many SQL tables whose records are created / edited using forms, my issue lies only with one (Products table), and only when submitting a change to a dropdown field (related Site Name field via ID for the Sites table). The form is submitted in the edit view, and navigates back to the detailed view, however there is no visible change to that dropdown field. Going back to the gallery / list view, and the change is still not shown. A check on the SQL database shows that the record has indeed been successfully edited, and is showing the correct ID / value, but the change is not reflected in powerapps. I have found that despite all navigation, clicking, going back and forth etc, it won't show until some random time close to 10 mins has passed.

I have experimented with writing a forced refresh of that table upon SubmitForm, and also on the OnVisible property of the prior screens just in case. Still no luck.

This makes it really hard to have team members do reliable data entry. As mentioned, there is absolutely nothing different between this table / form editing method and all of my other ones, and they all display changes very quickly. I was looking at the Patch function as a replacement for SubmitForm, but why would the other tables work without any issues??

In the recent past I had to re-code all of my old Combo boxes into DropDowns because Powerapps just wouldn't reliably write the ID / value's to SQL or display the lists correctly while inputting. Hopefully it's a relatively simple fix, rather than another re-write.... 

Any ideas?

[v-yuxima-msft]

Hi @ghostwhowalkz 

Could you please share more details of your controls and their formula expressions in your app?

Could you please share more details of your data source structure and the columns in your table?

Could you please share the scrrenshot of your issue?

Do you mean after you submit data the new data cannot show in the Gallery?

Do you try to add one Refresh Icon, and Icon.OnSelect=Refresh(datasourceName) to test?

Best Regards.

Yumia

[ghostwhowalkz]

Hi Yumia -  @v-yuxima-msft 

Thanks for the reply, I will try to address all of your points. As mentioned in the post, my datasource is comprised of numerous related tables, via an Azure SQL database. Some examples of tables are 'Accounts', 'Sites', 'InstalledProducts', 'WorkOrders', etc. The specific table that is causing me grief is 'InstalledProducts', or refered in the UI as Products.

Installed Products has the following structure:

The controls that are causing issues are the dropdown boxes that are looking to a related table via ID's, displaying the values (in this case, text so that a user can make search and make a choice, ID's aren't helpful at all), and then writing the associated ID back to the table for that field. I have spent considerable time in both practice and research to finetune my formulas to this point so the dropdown lookups work great with all of the other tables in my app (thanks to some of the amazing guys on YouTube & online like Shane Young, Paul O'Flaherty). Below are the screenshots of both the data card and control formulas that are relevant:

Dropdown control fomulasDatacard formulas

I've attached a few chronological screenshots to show the issue, with explanatory notes for each:

The Product List view, w/gallery and form view of basic details for selected record (selection is highlighted)The selected record in detailed view (Form.mode=View). In this case, I wish to edit the record and selected a Site name and region.Edit screen for the selected record. You can see that Site Name's are visible in the dropdown control (via Site ID lookup in related table, dbo.'Sites'). The Region dropdown box is the same.Selections have been made in both dropdown boxes, and ready to SubmitForm, and send changes to SQL table.SubmitForm completed, Refresh of dbo.'InstalledProducts' has been done as part of the save process, and navigated back to detailed view of the record. SQL Server Management Studio shows that the changes are visible in the SQL database (ID's have been correctly written to those columns, so formulas are correct) however screen does not reflect the change for the dropdown / lookup fieldsNavigating back to List view, OnVisible property now forces a Refresh of dbo.'InstalledProducts' table, as well as a Refresh button is available. Still no changes displayed for those two fields. Sometimes it takes 10 - 20 mins for it to pop up, sometimes a logout is required before it appears

So, in summary: Dropdown controls are correctly looking to related tables via ID's and displaying the values (names), so that users can make suitable choices. I believe that the controls are formulated correctly so that the ID's are being written to the table (InstalledProducts) upon the SubmitForm function being called, evidenced by the fact the changes are being shown for that record in the SQL database (observed in SQL Server Management Studio). Despite refreshes being coded into the screen navigation & save functions, and even a button for manual refresh, PowerApps will not show the changes to the dropdown fields for an indeterminate amount of time, sometimes requiring a reboot/logout.

The exact same formulas are being used in other tables, and there are NO issues, changes are reflected almost immediately. Any edits to a record in InstalledProducts that is not a dropdown box control, is displayed almost immediately post save. 

Thanks again for any help. This issue has been bugging me for a while, and it is really creating issues down the line for our users.

[ghostwhowalkz]

@v-yuxima-msft  - Any ideas?? I would love to know if any one else is having similar issues with certain connections to SQL, or Azure SQL specifically. Combo boxes are hopeless with writing ID's back to SQL, I hope Microsoft have some ideas on how to salvage dropdown boxes at least

[mogulman]

I'm doing the exact same thing you are and not having any problems.  I have a lot of lookup tables (20+).  I use numerous dropdowns and comboboxs.  For the galleries and details screen I use views.  I use collections based on a view whenever possible.   For all look up tables I create collections on startup.  The lookup tables rarely  change.   I use collections based on views for comboboxes since they are dynamic.  So far I have had no problem.  I'm currently using an on-premise SQL Server for development.  I plan to use Azure SQL DB.  I used SQL Server Profiler for many days measuring performance and debugging problems.  I set multiselect to false on all comboboxes.  I use this to get id from combobox to write to table:

Last(CounterpartyPrincipalCB.SelectedItems).ContactID

Since I use views for collections I need to refresh view when I add/delete a record then I refresh collection.

Refresh('[dbo].[ContactVGA]');
ClearCollect(
    colContactVGA,
    '[dbo].[ContactVGA]'
);

[ghostwhowalkz]

@mogulman 

Thanks so much for the reply. I'm doing some work to implement your suggestion, and see how it goes. I'm currently using forms to save edits to the SQL tables. Are you using similar, or utilising the Patch function? If so, could you give any guidance or examples for the best way / method of using patch to add / edit a record?

Cheers

[mogulman]

I would avoid using patch to add records.   SQL connector performs numerous checks on db when adding a record.  I used SQL Profiler to study this and was very surprised.  You need to define all non-null fields.  I do use Patch in one isolated case to mark a task completed that is listed in a gallery.