Jump to content

number must be whole

This topic is 2968 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I do not want a decimal allowed, nor a minus or dollar sign or text.  I want only whole numbers allowed.  I tried GetAsNumber but that did not work.  I tried using filter like Filter(number field ; "1234567890") but that allows leading zero which I do not want either but I need to include the zero for numbers ending in 0.  

I am not sure whether to use auto enter and just remove what I do not want or to provide an error message.  Can I get ideas on how to both correct automatically or fail validation?  This is for version 12 so I know I could use SQL but I do not want to.  I even tried Exact with GetasNumber.  I fail on all tries.  I should know how to do this by now but I don't.  Oh, I tried Int also and Abs.  I have tried everything and yes I searched but nothing fits my request.

I am hoping I do not need a custom function.  I would appreciate help writing the best calc to do this.

Edited by rob
Link to comment
Share on other sites

It would really help if you could explain the reason behind this wish. Do you want to auto-correct user entry, or modify existing records, or ... ? And why do you want to remove the decimal part?

Note also that negative numbers can be whole numbers too. If a user entered -10, why would you want it to become 10 (or any other value)?


Edited by comment
Link to comment
Share on other sites

Thank you both for answering.  This is when person is entering records.  We receive ChangeOrders from external company and sometimes we create one directly in our system and manually assign any number the company wants while on the phone with them but it must be whole number, no decimal, no text, no dashes and no leading zeros.

Sometimes the ChangeOrder will say 4b or 4REV and it must become 4, no negative either.  or the person entering it types it wrong and types r instead of 5.

If 10.2 we should ask them whether it should be 10 or 102.  I thought of just fixing it using 'replace' auto-enter but now that you mention it, they may not notice their mistake if I just fix it and it remove their entry in case of 'r' so validation seems wisest.  I made it a number field but that does not seem to protect it at all from text, dash, leading zero or decimal.  Filter gets me closest but allows leading 0 which upsets our owner.

I hope I answered your questions.  It will eventually be exported to another online system which bombs with leading zero which is why owner gets upset with them.

this is not a primary key or anything.  It is an external suggested tracking for external companies.  They know it should be only numbers but their people add text anyway etc.  And our data-entry guys sometimes forget and just type what they put or they make a mistake themselves.

It is a reference number.  I think that is the best description. 

Link to comment
Share on other sites

Digits only, no leading zero? Try: GetAsNumber ( Filter ( field ; "0123456789" ) ). Filter gets rid of non-numeric characters, then GetAsNumber gets rid of a leading zero. If you're trying to also catch data entry errors, you might considering adding a check digit, like the Damm check digit (implemented with this function).

Edited by jbante
  • Like 1
Link to comment
Share on other sites

Here are some options you could consider:

1. Validate the field by calculation =

Exact ( Self ;  Abs ( Int ( Self ) ) )

2. Set the field to auto-enter a calculated value (replacing existing value) =

Abs ( Int ( Self ) )

3. Trigger a script OnObjectValidate, something like:

If [ not Exact ( YourTable::YourField ; Abs ( Int ( YourTable::YourField ) ) ) ] 
 Show Custom Dialog [ ... ] 
End If 



  • Like 1
Link to comment
Share on other sites

  • 3 months later...

This topic is 2968 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.