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....
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?
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:
I've attached a few chronological screenshots to show the issue, with explanatory notes for each:
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.
@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
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:
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]' );
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?
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.