cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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?

6 REPLIES 6
Highlighted
Community Support
Community Support

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

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

Highlighted
Frequent Visitor

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

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:

 

Annotation 2019-07-24 120031.png

 

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 fomulasDropdown control fomulasDatacard formulasDatacard 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 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.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.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.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 fieldsSubmitForm 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 appearsNavigating 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.

Highlighted
Frequent Visitor

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

@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

Highlighted
Skilled Sharer
Skilled Sharer

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

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]'
);
Highlighted
Frequent Visitor

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

@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

Highlighted
Skilled Sharer
Skilled Sharer

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

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. 

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,042)