Data Validation By Calculation

[rkass068]

Hello,

 

I am not sure if this is the correct section to post this but its as close as I think it is. I have a field that I would like to have the data validate. There are options to select the field to be strictly numeric and from a value list. I have a field that can be numeric or have data entered from the value list. We do not want any other data (text) unless its numeric to be entered into the dropdown field. Is there a way to validate the Data so it is numeric OR from a value list that is assigned to that field??

 

I am guessing I would have to validate by calculation but I do not know how the calculation would look exactly. Thanks

 

[Wim Decorte]

To test the numeric part:

 

isValid( getasnumber( <your field> ))

 

and for testing if it is part of a value list: check out the FilterValues() function and the ValueListItems() function

[eos]

and for testing if it is part of a value list: check out the FilterValues() function and the ValueListItems() function

 

There is also a dedicated validation setting (“Member of value list“) that allows doing this more conveniently.

[comment]

To test the numeric part:

 

isValid( getasnumber( <your field> ))

 

How does that test that the field value is strictly numeric?

[eos]

There is the validation option “Strict data type:”, …

 

and for testing if it is part of a value list: check out the FilterValues() function and the ValueListItems() function

 

… and also a dedicated validation setting (“Member of value list“) that allows doing this more conveniently.

[comment]

There is the validation option “Strict data type:”, …

 

 

… and also a dedicated validation setting (“Member of value list“) that allows doing this more conveniently.

 

If you select both, then the validation will validate that the entry is strictly numeric AND a member of the value list. Contrary to:

 

 

Is there a way to validate the Data so it is numeric OR from a value list

[eos]

If you select both, then the validation will validate that the entry is strictly numeric AND a member of the value list.

 

Fair enough; it probably pays off to read the entire post.

[rkass068]

Yes, I will test out wim's solution thank you. And yes comment I would like it to be Numeric OR from a value list as oppose to AND. I did see the validation check boxes for a strict numeric and value list value on the data validation options. However, this validation requires the data to be numeric and from the value list. 

[comment]

I have a field that can be numeric or have data entered from the value list.

 

Can you explain in more detail what this is about? If you have a field that needs to have either a number (any number) or one of the text values of a value list, then most likely you have a structural problem.

 

it would also help knowing what type of number is acceptable here. I don't know of a calculation that would reproduce exactly the "strictly numeric type" validation (at least not a reasonably simple one).

[rkass068]

Can you explain in more detail what this is about? 

Hi comment, yes so the field is a serum protein electrophoresis field that can be a number or have values such as NAD, N/A, Oligo, trace or Faint. The text values explains that the band did not end up reproducing a numeric value but something else. A numeric value is the value of protein electrophoresis distance. For the validation we want the data entry clerk or doctors to be able to enter any numeric value but only those text values. For example someone may input "Trace amount" instead of "trace" this would make things complicated when seeing which patients had a trace serum protein electrophoresis. 

[eos]

I don't know of a calculation that would reproduce exactly the "strictly numeric type" validation (at least not a reasonably simple one).

 

If you didn't already think of it, there probably a catch to it – but what about 

IsEmpty ( field ) or not IsEmpty ( GetAsNumber ( field ) )

[comment]

Hi comment, yes so the field is a serum protein electrophoresis field that can be a number or have values such as NAD, N/A, Oligo, trace or Faint.

 

IMHO, you should have two fields: one for the numeric value, and one for the explanation why no numeric value is present. That would also make for less awkward data entry (not having to get the drop-down out of the way in order to enter a number).

 

In any case, you didn't answer my question regarding the type of number that is acceptable here. For example, if the number needs to be an integer, that's easy to validate.

 

 

 

 

what about 

IsEmpty ( field ) or not IsEmpty ( GetAsNumber ( field ) )

 

 

This would pass "a1b" or "1..2" - both of which would fail "strictly numeric" validation.

[rkass068]

 

 

In any case, you didn't answer my question regarding the type of number that is acceptable here. For example, if the number needs to be an integer, that's easy to validate.

Sorry, the number can be any number 

[comment]

Sorry, the number can be any number 

 

Well, you could validate that the entry contains only digits, a decimal point and a minus sign. And that there's no more than one decimal point and/or minus sign. And that the minus sign, if present, is before any other character. Uhm, what else could wrong?

[Wim Decorte]

How does that test that the field value is strictly numeric?

 

True.  A better test would be

 

getasnumber( <the field> ) = <thefield>

[comment]

True.  A better test would be

 

getasnumber( <the field> ) = <thefield>

 

In what domain do you envision this comparison to occur?

 

If the field in question is of type Text, then a perfectly valid, strictly numeric, entry of "0.5" would fail this validation - because "0.5" is not equal to the string ".5" obtained by GetAsNumber ( "0.5" ).

 

OTOH, if the field type is Number, then an entry of "1a2" will pass this validation.