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

Edit Form Seems to Change Data Source Column Format

I am running my first canvas app from an Excel workbook on OneDrive. Certain cards in the app create an error notification when I attempt to submit some of my edit forms. I am trying to save dates and numbers typed in my forms to the workbook without changing column formats in my data source, but I’m having no luck even after adjusting them to general and saving from Power Apps as text. Here are some examples of the error messages I'm getting.

 

Account Receivables Error.png

In the first image, I try to save my form but receive this strange error message saying that I attempted to save an invalid value, while a number was expected. The Default property of the field is set to Parent.Default. The column in Excel is in the "general" format.

DatePicker Error.png

The next card is only visible if data is saved upon submission. The card is a Date Picker with a "DefaultDate" set to "Parent.Default" . The error message shows that my form submitted a serial code for the date while expecting a string. My Excel column is "general"  

Slider Error Message.png

This message concerns my slider, which produces a numerical value. The form expected a string. The column format in excel is "general". Also note, I replaced the original text input field on this card with the slider. I'm not sure if that may be part of the issue here.

 

It seems that the data type of each corresponding column in my Excel workbook does not match the data type expected in Power Apps. I attempted to fix the problem by changing the values output by my Datepickers and sliders to text (in the Default property). Sometimes after appropriately formatting my columns in Excel, I am able to successfully submit a form once, but not ever again after I open excel to check on the data type. I find that the column format always reverts back to it's original state "general". Afterward, I can no longer save some forms in my app due to errors such as the ones shown above.

 

I also tried disconnecting my tables from Power Apps, taking my Excel offline, reformatting, reuploading, and reconnecting everything. But as soon as I start saving data in my reconnected Power App, all of my excel columns revert back the original states.

 

I assume that the difference between data type in Power Apps and column format in Excel is the main problem, but please let me know if there are other possibilities.

 

Are "Default" and "DefaultDate" the correct properties for changing values to text in my DatePickers and Sliders?

 

How do I make sure my data type in Excel and data field in the app are compatible?

 

Please let me know if I can provide more detail.

1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @jgcebs ,

 

You will need to convert the data types in Update property of each Data Card to meet the Excel table column type. For example of number type column, set below kind of formula in Update of the corresponding Data Card:

Value(TextInput.Text)

Date column:

DateTimeValue(DataCardValue.Text,"yyyy-MM-dd")

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
v-jefferni
Community Support
Community Support

Hi @jgcebs ,

 

You will need to convert the data types in Update property of each Data Card to meet the Excel table column type. For example of number type column, set below kind of formula in Update of the corresponding Data Card:

Value(TextInput.Text)

Date column:

DateTimeValue(DataCardValue.Text,"yyyy-MM-dd")

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

v-jefferni
Community Support
Community Support

Hi @jgcebs ,

 

Have you resolved the issue? 

If above post helps, then please consider Accept it as the solution to help the other members find it.


Best regards,
Community Support Team _ Jeffer Ni

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni,

 

No, unfortunately, that did not solve the issue. I do not want to make my numerical fields output text from update, because I have other fields in my form that depend on them for calculations.

 

I can't seem to stop Power Apps from changing the format of my columns in Excel from Number to General. 

 

When I attempt to fix this by changing the format of my Excel columns back to Number, Power Apps will allow me to submit successfully. Then I save and close Power Apps, and reopen my Excel to check the column format again. At this point, I find that the column format is changed back to General, and when I retry submitting in Power Apps, I am unsuccessful and receive an error message notifying me that a string was expected rather than a number.

 

This keeps happing whether or not I disconnect and reconnect my data, change column format using the dialog box in excel, delete and re-add my field, change the format property of my text field to number, and so on. These are some of the solutions I've found in other posts, and none seem to work in my case. 

 

Any other advice if my intention is to keep these columns in number format? 

 

Would the patch function help in some way?

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,086)