cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power_User_One
Level: Powered On

Switched from Excel to SQL and now only some of the data will submit

I'd consider myself not a beginner but not an expert in the world of Powerapps. I have an app that I built out extensively on excel. After deciding that this app building platform would get what I needed it to do done I decided to build out an Azure SQL Database to replace the excel sheets. I've made the transition and all data shows and is read/write-able (with an interger Primary Key). The funny part now is that I can delete entries, but when I go to edit or submit a new entry only some of the fields actually save to the database. There doesn't seem to be a ryme or reason to which one saves and which don't as they are all coming from outside tables on the database and are an assortment of dropdowns, and text fields.

I've deleted the connection and re-added it. I've tried patch & submitform.

 

I let powerapps build a simple app as well based on the same data and the NEW built app page works exactly as it should. However that's two screens and I have several and it would take close to a week to recreate.

 

What would cause only part of the information to be submitted when it worked perfectly on an excel document and works perfectly from an auto-built app? Am I just stuck re-doing all my hard work?

 

Thanks in Advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Switched from Excel to SQL and now only some of the data will submit

I have seen updates to SQL columns fail if the column already contains a Null value.

Could it be that you are updating existing records where some fields are already Null and you are trying to update to a non-null value?

You might also want to try adding a RowVersion field to the table as well (seems to help with some issues).

View solution in original post

Power_User_One
Level: Powered On

Re: Switched from Excel to SQL and now only some of the data will submit

Unfortunately my solution to the issue was to create a new form using the data source from the sql and then copy/pasting the settings from the old table into the new table. Took a lot of time but it seemed to be the only thing that would work without erasing all of the existing data in my SQL. Seems like a major bug in the system. I have to say though, SQL is the best thing that I've done for the app to date. SO much more functionality!! 

View solution in original post

5 REPLIES 5
myke_deabreu
Level 8

Re: Switched from Excel to SQL and now only some of the data will submit

Hi Power_User_One,

 

Are you using the Form Control (i.e. SubmitForm) or are you Patching? How many SQL Tables are you using?

 

If you are using the Form control my only guess is that you may have modified some of the Datacards and they are not connected to a column any more.

 

Please answer the following questions I would love to help:

 

1) How many tables?

2) Are you using Forms (SubmitForm) or Patch?

3) If you are using Patch can you provide your formula? (dont have to include all coloumns.

4) Are your Datatypes in SQL matching or have enough character limit?

 

Power_User_One
Level: Powered On

Re: Switched from Excel to SQL and now only some of the data will submit

Thank you for your reply.

 

I am using the submitform() function right now but tried the patch too and got the same result.

 

I have 5 tables plus a date picker and a few manual text boxes on this form that feed into one table to display the data.  The tables that feed this form are static and are only used as dropdown table options within the app.

 

Your reccomendation about data type seems to make the most sense. I imported this data from an excel sheet so I wonder if the SQL just assigned the wrong data format to some of the collumns? Can you show me how to check the data type in the collumn and reccomended types for text, numbers, and short date/time? I found this link but its a little scarce on details for the 'how to.'

 

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017

Power_User_One
Level: Powered On

Re: Switched from Excel to SQL and now only some of the data will submit

Using form control

 

5 tables feed into one form mostly to populate dropdowns

 

Is there a way I can see what data type the form is submitting to the SQL?

 

 

Super User
Super User

Re: Switched from Excel to SQL and now only some of the data will submit

I have seen updates to SQL columns fail if the column already contains a Null value.

Could it be that you are updating existing records where some fields are already Null and you are trying to update to a non-null value?

You might also want to try adding a RowVersion field to the table as well (seems to help with some issues).

View solution in original post

Power_User_One
Level: Powered On

Re: Switched from Excel to SQL and now only some of the data will submit

Unfortunately my solution to the issue was to create a new form using the data source from the sql and then copy/pasting the settings from the old table into the new table. Took a lot of time but it seemed to be the only thing that would work without erasing all of the existing data in my SQL. Seems like a major bug in the system. I have to say though, SQL is the best thing that I've done for the app to date. SO much more functionality!! 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,639)