Add numeric data type to database #misc #suggestion


Paul Lever
 

There is currently no numeric data type for database columns. For numeric data I use Single Line Text.  This is troublesome when sorting. If the column data includes 1,2,3,10 it will sort as 1,10,2,3.
 
Paul
 


 

On Thu, Jul 28, 2022 at 4:41 PM Paul Lever <svjeorgia@...> wrote:
There is currently no numeric data type for database columns. For numeric data I use Single Line Text.  This is troublesome when sorting. If the column data includes 1,2,3,10 it will sort as 1,10,2,3.
 
For a numerical type, should there also be a precision field? That is, you would be able to specify how many digits after the decimal (including 0)?

Thanks,
Mark 


 

I strongly support this request, and believe that the ‘precision’ degree shouldn’t need to be more than three (0.123) places. That would accommodate all conventional monetary transactions and most typical ’machining’ measurements. 
Thanks, Paul, for the suggestion, and Mark for the response. 

On Fri, Jul 29, 2022 at 07:34 Mark Fletcher <markf@corp.groups.io> wrote:
On Thu, Jul 28, 2022 at 4:41 PM Paul Lever <svjeorgia@...> wrote:
There is currently no numeric data type for database columns. For numeric data I use Single Line Text.  This is troublesome when sorting. If the column data includes 1,2,3,10 it will sort as 1,10,2,3.
 
For a numerical type, should there also be a precision field? That is, you would be able to specify how many digits after the decimal (including 0)?

Thanks,
Mark 


 

On 2022-07-29 11:34, Mark Fletcher via groups.io wrote:
For a numerical type, should there also be a precision field? That is, you would be able to specify how many digits after the decimal (including 0)?
I think it should (with a default of 0) so it can accommodate fractional numerical values and sort them correctly, otherwise the same problem could happen, I guess depending on how that db software works in that regard.  Plus there's the question of what would happen if it doesn't and someone tries to enter a fractional number in a numeric field type.

As to how many decimal digits to allow, some graphics apps (like Coreldraw for example) allow up to 6 digits for drawing precision, so for some user support groups (like the Coreldraw User group here on GIO or maybe other science/engineering-type groups) having only 3 digits allowed may not be as useful.

Cheers,
Christos


 

Hi All,

When I add this, I'm thinking of also including a prefix character that you could select, like dollar sign, pound sign, euro sign. What other characters should be an option?

Thanks,
Mark


Andy Wedge
 

On Fri, Jul 29, 2022 at 06:42 PM, Christos Psarras wrote:
As to how many decimal digits to allow,
Would a floating point definition be useful?

Regards
Andy


Andy Wedge
 

On Fri, Jul 29, 2022 at 06:48 PM, Mark Fletcher wrote:
I'm thinking of also including a prefix character that you could select, like dollar sign, pound sign, euro sign. What other characters should be an option?
In addition to currency prefix symbols, is there any desire for weights and measures suffixes such as Kg, g, L, ml, cc, cm, m, in, ft, yds, hrs, min, sec etc?

Regards
Andy


Paul Lever
 

I'm most interested in getting the sorting to work. So a single type of numeric would be fine. Then read using any API that converts  text to numeric, and then sort using the result. 
This would handle integers, signed values and floating point. 
I don't think you need the level of definition/formatting that a typical spreadsheet offers,

Paul


On Fri, Jul 29, 2022, 2:35 PM Andy Wedge <andy_wedge@...> wrote:
On Fri, Jul 29, 2022 at 06:42 PM, Christos Psarras wrote:
As to how many decimal digits to allow,
Would a floating point definition be useful?

Regards
Andy


 

I would likely not use these. However, since the Groups.io world is at least a bit bigger than me…SURE, WHY NOT! I do know some folks who would benefit from this.

If the time and effort for development is going to be taken in the first place, it will likely be a small effort to add this. However, if this is not done ‘with this round of effort’ and is deemed something ‘we wish we had’ later on, it will take much more time and effort.

 

I’m for one-and-done.

 

Dan Tucker, Groups.io  AFDRetiree’s Group Founder/Owner/Moderator

 

From: "main@beta.groups.io" <main@beta.groups.io> on behalf of Andy Wedge <andy_wedge@...>
Reply-To: "main@beta.groups.io" <main@beta.groups.io>
Date: Friday, July 29, 2022 at 10:51 AM
To: "main@beta.groups.io" <main@beta.groups.io>
Subject: Re: [beta] Add numeric data type to database #misc #suggestion

 

On Fri, Jul 29, 2022 at 06:48 PM, Mark Fletcher wrote:

I'm thinking of also including a prefix character that you could select, like dollar sign, pound sign, euro sign. What other characters should be an option?

In addition to currency prefix symbols, is there any desire for weights and measures suffixes such as Kg, g, L, ml, cc, cm, m, in, ft, yds, hrs, min, sec etc?

Regards
Andy


 


On 2022-07-29 13:47, Mark Fletcher via groups.io wrote:
When I add this, I'm thinking of also including a prefix character that you could select, like dollar sign, pound sign, euro sign. What other characters should be an option?
 
I'd suggest to also include the tilde ~ (or ≈) so a number can be denoted as being an approximation.  More possible characters could be some of the math symbols/operators, % ≡ > ≥  < ≤ ≠ etc.

Cheers,
Christos


Paul Lever
 

So how would you do the sort if you include the special symbols? Strip all symbols except + and -, then sort?
I think the symbols make this a lot more complicated. How do you sort 1kg, 1.5kg,100mg,.....

Paul




Duane
 

On Fri, Jul 29, 2022 at 01:51 PM, Andy Wedge wrote:
In addition to currency prefix symbols, is there any desire for weights and measures suffixes such as Kg, g, L, ml, cc, cm, m, in, ft, yds, hrs, min, sec etc?
I can see wanting both prefix and suffix, but I doubt that I'd need them.  I would specify the unit as part of the column name being used - Weight(lb), Height(feet), or Cost($).  Of course, there will be those that don't quite see it.  Right now, I have one that says Phone(xxx-xxx-xxx) and people still use other formats like (915) 325-1234.

Duane


Jeremy H
 

This is one of those suggestions where the best can rapidly become the enemy of the good.

My view is that what is important is to be able to say 'it's number, treat it as such': going further you have all sorts of complications over those settings loosely grouped as 'locale' (as to what the decimal point indicator is, currency symbol, etc., etc.) - if Mark can easily find and incorporate routines to handle all these it would be good, otherwise forget them and just have a simple 'it's a number' with any explanation in column headings. 

Jeremy
,


 

Hi All,

For the number field, is it important to have it be blank until it is set to a specific number (and should you be able to clear it)? Or should it default to 0?

Thanks,
Mark


Pete Cook
 

I'd suggest it be blank, since 0 actually may mean something in some use cases. And like any field, I'd like to see it editable and clearable.


Paul Lever
 

I would prefer blank and be able to set as a required field

Paul


Andy Wedge
 

On Tue, Aug 2, 2022 at 11:27 PM, Mark Fletcher wrote:
For the number field, is it important to have it be blank until it is set to a specific number
I would define it as a null value rather than blank which could be interpreted by some to mean a space character.

Regards
Andy


Paul Lever
 

One item to consider un adding the numeric data type is how you will transition databases. I have a database with about a 100 rows in it now. I'd like to be able to modify the field type to numeric when the beta is released and have all the existing text numbers in that field converted to numeric.

Paul


 

Hi All,

I have added the Number column type to databases and extra member data fields.

If you convert an existing Single Line Text column to a Number, it will automatically convert any existing numbers properly.

Please let me know if you have any questions.

Thanks,
Mark


Duane
 

On Wed, Aug 3, 2022 at 03:38 PM, Mark Fletcher wrote:
If you convert an existing Single Line Text column to a Number, it will automatically convert any existing numbers properly.
Oops, didn't work.  Luckily, changing it back to Single Line Text brought the numbers back.

Duane


 

On Wed, Aug 3, 2022 at 2:00 PM Duane <txpigeon@...> wrote:
On Wed, Aug 3, 2022 at 03:38 PM, Mark Fletcher wrote:
If you convert an existing Single Line Text column to a Number, it will automatically convert any existing numbers properly.
Oops, didn't work.  Luckily, changing it back to Single Line Text brought the numbers back.

Hmm, really? What did it do?

I tested it a bunch before launch, and I'm not able to reproduce it now. Off-list can you send me the URL of the table and which column you tried to convert? 

Thanks,
Mark


 

On Wed, Aug 3, 2022 at 2:05 PM Mark Fletcher via groups.io <markf=corp.groups.io@groups.io> wrote:

I tested it a bunch before launch, and I'm not able to reproduce it now. Off-list can you send me the URL of the table and which column you tried to convert? 

Err, nevermind. Found the problem. Last minute change before I pushed to production. Sigh. Sorry about that. Should work now.

Thanks,
Mark 


Paul Lever
 

Tested on my DB and switching from Text to Numeric worked fine. Thanks.

How is the initial sort set on a DB? Looks like it is by ID column. A low priority suggestion would be to enable initial sort on a different column. That would allow the DB to show up in it's most natural way, but still allow users to sort on any column.

Paul