Access Int64 cast in float : change documentation, set a warning message and Access 2016

[simonaubert_bd]

Microsoft Access 2000-2003 does not access big integer

Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.

You can find MS documentation here : https://support.office.com/en-us/article/set-the-field-size-ba65e5a7-2e6f-4737-8e72-36b93f966a33

So when you use Alteryx with big integer (e.g Int64 ), it won't work the way you expected and the field will be converted into Double.

So what I propose :

-a change in documentation about this behaviour
-a warning message on the output box when you use Access and you have Int64

-ability to select Access 2016 in order to use Long integer format

Best regards,

Simon

[NicoleJ]

As of 23.1 release, Alteryx now supports the MS Access 2016 Runtime and the MS Access 365 Runtime for reading ACCDB files! We believe this should address the issues in this idea, but if you have additional comments, please do let us know! 

Thanks for bringing your ideas to the Alteryx Designer Community!!

[simonaubert_bd]

Hello@NicoleJ  Thanks for the update, that's nice to see the Alteryx staff in the idea section :)

Here the ms access documentation
https://support.microsoft.com/en-us/office/set-the-field-size-ba65e5a7-2e6f-4737-8e72-36b93f966a33

• Byte — For integers that range from 0 to 255. Storage requirement is a single byte.

• Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.

• Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is four bytes.

  Tip: Use the Long Integer data type when you create a foreign key to relate a field to another table's AutoNumber primary key field.

• Single — For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits. Storage requirement is four bytes.

• Double — For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes

and here the Alteryx documentation https://help.alteryx.com/20231/designer/data-types

Byte	A unit of data that is 8 binary digits (bits) long. A byte field is a positive whole number that falls within the range 0 thru 255, or 28	0, 1, 2, 3....253, 254, 255
Int16	A numeric value without a decimal equal to 2 bytes, or -(215) to (215)-1	–32,768 to 32,767
Int32	A numeric value without a decimal equal to 4 bytes, or -(231) to (231)-1	–2,147,483,648 to 2,147,483,647
Int64	A numeric value without a decimal equal to 8 bytes, or -(263) to (263)-1	–9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

So, what can we say ?
The biggest integer you can have in Alteryx is Int64, and it will always be converted to a double and there's nothing Alteryx can do about that. However, this idea was mainly about
1/ a documentation change to explain that behaviour (should take approximatly one hour of work)
2/ a warning message when a workflow with int64 is output to an accessdb (maybe two or three hours of work)
So that, no surprise !

The good thing is that now, when we select int32, it creates a long integer, which was the third point of this idea. And thanks a lot for that!

As a conclusion, the hardest part of the idea is implemented and with a few hours of work, it can be totally completed. :)

Best regards,

Simon

[NicoleJ]

@simonaubert_bd Got it - I believe you're saying we should explicitly call out in documentation that Access does not support larger than Int32, and will convert to a double... and then ideally you'd like to see a warning in Designer itself if you are trying to output to Access and you have an Int64 datatype. 

Now, I will say that while it might seem like "2-3 hours of work" to add a warning, that is VERY much not the case when you are making code updates to a big, impressive code base like Designer 😉 So just to level set on expectations when it comes to "little" asks - sometimes even the "littlest" changes can take a very long time to update when you consider the teams potentially affected, the SDLC processes we follow, the tests that need to be run, and the hundreds of other decisions that we are prioritizing every day!

However, you're spot on that documentation is easy, so we'll work on that first and then put the in-product warning on our radar to evaluate feasibility and where it might fit. 

Thanks for the feedback! 

[simonaubert_bd]

@NicoleJYou perfectly sum up my points, thanks for the effort, English is not my mother tongue and I know sometimes I can be unclear.

Best regards,

Simon