Editing column properties for an entire environment dataverse

(0) Share

Report

Posted on by CtrlCloud-Jelle

Hi,

For one of my customers I'm synchronizing an on-prem SQL server to the dataverse.

The database consists of all types of data, including floating point numbers.

When I set up the dataflow, the floats in the SQL database got a 'floating point number' label in the dataverse, which seemed fine.

Until the sync started sync'ing and I got loads of errors because the default column minimum value is set to 0.

All negative numbers got an error.

Of course, I could change this manually, but that's a lot of columns and not really future proof (a lot of new clients will be on-boarded to this client of mine). So how do I go about changing all float columns of all tables in an environment (plus future ones if possible) to have float values ranging from -10000000 to 1000000 (the min/max)?

Thanks for your help!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

AhmedSalih

6,678 Moderator on at

Like (1)

Report

Hello, @CtrlCloud-Jelle, You will have to create the Dataverse tables first and use Decimal data type columns for those float in SQL. When you configure the Dataflows, you will load the data into existing tables.

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

CtrlCloud-Jelle 3 on at

Like (0)

Report

Hi @AhmedSalih

Thank you for your reply! This seems to be the only workaround available I'm afraid.
Unfortunately that means a lot of manual work, since all tables and columns have to be predefined (and cannot be created using SQL)?

Do you have a suggestion on how to automate the creation of the tables? We have a lot of different types of tables and this would take a long time to create manually.

Again, thanks for your help!

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @CtrlCloud-Jelle,

The effort required to setup an automated way would take longer than manually doing it. You can reduce the manual effort required via the Attributes Factory for the XrmToolBox where you can bulk create or update attributes which is faster than using the Portal to go 1 by 1. The tables do need to be created first however.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Shaun_WM 2 on at

Like (0)

Report

I've been pulling my hair out trying to figure out why my dataflows are failing. So i have to manually go in and edit each of the hundreds of columns in dataverse tables to -100000000 even though i have the data type set correctly in power query. That can be right.

This does somewhat make power query useless when importing into a new table as any negative numbers have to be set up manually. Surely there is a way for negative numbers to be require imported into Dataverse new tables?

Was this reply helpful? Yes No
latelounge123 4 on at

Like (1)

Report

Same issue here, this is unbelievable!
How can you put such default limitations on a number field? It comes totally unexpected and is extremely annoying. Never saw this behaviour with any standard database i have been working with - and in addition there seems to be now way to change this sh*tty behaviour, how can you come up with such an idea in the first place anyway? I will now try to use text only as datatype and do another transformation in PowerBi.

Was this reply helpful? Yes No
DC-20101201-0 2 on at

Like (0)

Report

When is this going to be fixed? I have numerous tables I want to bring via numerous methods notibly legacy ODBC connections, but everything that's a decimal and displays fine in power query is loaded as a float 0-999999999999. This is just stupid. Why does it not have the min set at -9999999999. Of course users need negative values, you can't justs exclude them if they are in the data! accounts in credit balances and credit transactions etc.... are negative in our database.

Was this reply helpful? Yes No