I have an application with an Azure based SQL database as a data source, My table in database has an ID column with integers that are 18 digits long(bigint). Whenever this column is bought to powerapps, I see only 16 digits and remaining digits shown as zeros.
So if I have value 1234567890123456103 in the database, I see 1234567890123456000 in the powerapp. Is there a way around this? It seems powerapps doesnt support display of longintegers.
Symptom confirmed from my side.
It seems currently PowerApps SQL Server connector can't handle data which is larger than 18 digits.
As the data passed through the connector is number type, the data would be changed automatically.
I will send the feedback from my side to confirm this issue, and if there are any workaround, will post back.
Besides, please consider switch the Bigint format into string, which should be able to have the number displayed correctly.
Please note that, under current situation, the string can't be converted into the proper number value through the Value() function.
Numbers in PowerApps are represented in the IEEE 754 64-bit format, which would give a range of -2^53 to 2^53 for the integers that can be represented without losing precision (that's about 16 digits).
If you have a bigint in SQL and want to display it in PowerApps, you can consider creating a new calculated column that represents that number as a string (varchar or nvarchar).
The table I am querying in SQL is a very large one, and its most effectively queried when utilising the bigint primary key. The approach of maintaining a varchar column and running a query against that column in a very large table is not a good idea and queries never complete.(I tried, it never works)
Is there any way around this issue? Is it possible that the Lookup or filter function in Powerapps allow using strings that eventually query the data source as a Bigint.
Thanks and Looking forward to your support.
You cannot use numbers greater than ~2^53 in PowerApps, as they would lose precision as you noticed. One possible workaround would be to create a stored procedure in SQL Server that would take the number you want to query as a string, convert it into a long integer, and then make the query that would take advantage of your indices. It cannot be done directly in PowerApps, but you can do that using Flow - see this link for some instructions on how you would go about this.
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
Learn how to build the business apps that you need.