validate/format data as it is entered

[Paul_G]

Hi - part of my database is dealing with input of clinical measurements of vision, specifically lens powers (I'm an optometrist).

The units of lens strength are by convention in 0.25 steps, so lens power goes +0.25, +0.50, +0.75, etc (or the same in minus values). General practise in clinical work is to shorthand these when entering, as (for example) +050, +100, -250, etc. Actual powers are very rarely above +/-20.00, so we would know to read them as +0.50, +1.00, -2.50, etc (rather than +50.00, +100.00, -250.00, etc).

My problem - I need to set up the data entry so that users entering any shorthand versions (such as "050", "+050", "0.50", "0.5", +0.5", "+.5", ".5" or "+0.50") would have it automatically and immediately reformat itself correctly as "+0.50". (The plus or minus sign is important).

I think I could create a 'case' script to go through and process it, but I'm not sure how would be best to implement the script. Would it be best to use a script trigger? (onObjectExit?). Or is there another way that would be better? I expect I'd have to make it a Text field to be able to force the Plus symbol to be shown?

It would be great if I could somehow make it reformat itself wherever it's entered, without having to remember to put a script trigger on wherever it appears, but if I have to then that's okay.

Validation popup windows forcing people to 'just enter it properly' won't be accepted - it needs to be something that works in the background reformatting that shorthand entry into proper format.

Thanks in advance.

[comment]

Try it this way?

diopter.fp7.zip

[Paul_G]

You're a genius!

Thanks so much. I *knew* there must be a more elegant way of doing it. :laugh: