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

[Anonymous]

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

[myke_deabreu]

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?

[Anonymous]

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

[Anonymous]

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?

[PaulD1 · Accepted solution]

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).

[Anonymous · Accepted solution]

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!!