Is there a way to edit check a field

[al m]

Can a date field be edit checked to fall between 2 different dates?

[Mike Schwartz]

Al,

Tap Forms does not currently provide for data entry validation, which I think is what you’re looking for. The best you can do for now, as far as I can tell, would be to include some additional calculation field(s) that can do some error checking and display “ERROR” or “” depending on the outcome.

Unfortunately, the IF(X,Y,Z) function in Tap Forms is very limited: it only tests for whether the value X is not zero (return Y) or equals zero (return Z). So nothing like in Excel, where you could use a formula such as: IF(OR(DATE<DATE_LIMIT_LOW,DATE>DATE_LIMIT_HIGH),”ERROR”,””).

But there are some ugly kludges you can use. Tap Forms provides a SIGN(X) function, which returns a value of -1, 0, or 1 for X<0, X=0, or X>0.

By way of example, here’s how you could determine if a date field “D” is less than a lower limit “LL” (building up to the answer in two steps for clarity):
• SIGN(D-LL) is 1 if D>LL, is 0 if D=LL, is -1 if D<LL
• SIGN(D-LL)+1 is 2 if D>LL, is 1 if D=LL, is 0 if D<LL

So that last version can be used in an IF statement to test if the date is strictly less than the lower limit: IF(SIGN(D-LL)+1,””,”ERROR”).

But you want to test if the value is between two limits. Extending the thought, you could create a similar expression to test the date D against the upper limit UL, that would yield the value of either 2 or 1 if D <= UL and 0 if D>UL.

You can then test for both conditions simultaneously by multiplying the two functions together. The product will be either 1, 2, or 4 (it doesn’t matter) if and only if the date lies between the two limits, and 0 if outside the two limits. Then stick that in your IF statement, and you’re there.

I told you it was ugly!

— Mike

[Brendan]

Well, you could possibly use the IF(X,Y,Z) statement to check to see if a date is within 2 other dates.

Your formula could look like this:

IF( (Date 1 > Date 2) & (Date 2 < Date 3), "Date 2 IS between Date 1 & Date 3", "Date 2 IS NOT between Date 1 & Date 3")

In my testing, this does work.

Attachments:

You must be logged in to view attached files.

[Mike Schwartz]

Hmmm. So X doesn’t have to be a numerical expression, but can also be a logical expression that can be resolved as TRUE or FALSE? And the symbol “&” functions as the logical AND operator? Are there corresponding OR, XOR, and NOT operators as well?

All this is very good. I think the documentation for IF(X,Y,Z) needs to be updated to reflect all this.

Thanks,
Mike

[Brendan]

I haven’t tried anything other than & in between, but if it does support OR, then it would most likely be the pipe character (|) as the operator. Full disclosure, I didn’t write the original math parser code. I licensed it from here: http://www.mathparsers.com/math-parser-for-objective-c-ios-and-osx/ but I’ve heavily modified it since I purchased the source code license. Ya, the documentation could use some updates.

[scneophyte]

Apologies if this is thread-hijacking but I have a related question: Is there any way to check if a Photo field is empty? I assume no since the Photo fields do not appear in the field list and manually typing “form name::field name” doesn’t work either.

[Mike Schwartz]

A poor man’s workaround would be to add a checkbox field “Photo Included” and check it off as you add photos to your records. Then you can search the checkbox field or even use it in Calculation fields.

— Mike

[scneophyte]

Thanks, Mike. I have a field like that already but I’m trying to catch data entry errors, since humans are not 100% error free! I’ve designed several nested IF statements for other fields that have been very helpful and was hoping to extend the functionality to the photo field…since if a record doesn’t have a photo then there is no receipt.

[Mike Schwartz]

Brendan,

Math Parsers advertises the following arithmetic and boolean operators:

Arithmetic Operators: +, -, /, *, ^(power), %(mod)
Boolean Operators: <, >, =, &, |, ! ,<>, >=, <=

I’ll do some testing on power and modulo operators and see if they work. I’ll also check out the Boolean operators including &, |, and ! and report back.

— Mike

[Author]

Posts