number must be whole

[rob]

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 September 23, 2015 by rob

[Josh Ormond]

Make it an auto-enter calc: Use either Int () or Round () or Floor () or Ceiling ()...depending on what you actually want to happen with that number they enter.

 

[comment]

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 September 23, 2015 by comment

[rob]

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. 

[jbante]

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 September 23, 2015 by jbante

[comment]

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 

 

 

[rob]

we went with option 3 by comment.  I studied all suggestions and each taught me a lot so thanks guys.