You are viewing limited content. For full access, please sign in.

Question

Question

Find() Returns What When It Does Not Find a Match

Forms Version 10 How To
Updated April 19, 2024
asked on March 1, 2021

Hello,

I'm attempting to use the FIND() formula in Forms, but I need to know what it returns when it does not find a match.  I assumed it was 0 or some value less than 0, but I'm getting a "calculation error" instead.  If you use this exact formula from LF help

=FIND("type", "prototype", 1)

it returns 6.

But, if you enter something like this

=FIND("type", "prototy", 1)

You get a "calculation error".  Huh???

What's going on here?

 

1 0

Answer

SELECTED ANSWER
replied on March 1, 2021 • Show version history

Yeah, it doesn't really recover from that kind of error very well.

I'm on version 10.2 - unless it was added later, there is no formula like IFERROR like what is available in Excel, so I build my own: 

=IF(FIND("type", CONCATENATE("prototy", "type"), 1) > LEN("prototy"), -1, FIND("type", "prototy", 1))

It adds the search value to the end of the string, before searching, in order to avoid the error - if the search only finds it at the end of the string, it returns -1, otherwise it will then search the unmodified version of the string and return the location of the searched value.

There are 3 places you have to enter your searched value (or the variable it comes from) and 3 places you have to enter the string being searched (or the variable it comes from).

1 0
View 8 previous replies
replied on March 2, 2021

Yeah, works great, thanks.  Confused though as to why Laserfiche would implement a formula without a proper return value for a failed case. 

1 0
replied on March 2, 2021

Or have the calculation error as an option in the custom error messages - but at least in 10.2, that doesn't appear possible either.

0 0
replied on March 2, 2021

Yeah, agreed. Hopefully, they'll get to it soon.

1 0
replied on March 3, 2021

You want to be able to customize the error when the field get error "This field contains a calculation error." as you usually get this error when the formula function use another field as reference, and you want to let the customer know which field value they need to update?

0 0
replied on March 3, 2021

My assumption is that a "calculation error" should only happen if the developer has forgotten to account for some scenario related to the data, not necessarily as a data validation tool, but my knowledge about formulas and their usage in forms is pretty lacking.  :-)

 

0 0
replied on March 3, 2021

This is just an example - where there wasn't a simple way to deal with the formula throwing an error for not finding the value - being able to override the default error message for that one particular field would be handy - particularly it is was searching using values entered by the user.

However, if I had to choose between having an IFERROR formula or having the ability to pick alternate language for the error, I'd choice the IFERROR formula.

0 0
replied on March 3, 2021

Yeah, that makes sense.

0 0
replied on March 3, 2021

I have added supporting IFERROR formula in Forms to our internal ideas portal. 

2 0
replied on March 4, 2021

Fantastic!

0 0
replied on March 4, 2021

Thank you.  

0 0
replied on April 19, 2024

Looks like there haven't been any changes to this yet. I'm getting the error:

Calculation contains an invalid function name.

when trying to use IFERROR; was that ever added?

1 0

Replies

replied on March 1, 2021
0 1
replied on March 1, 2021

You could try wrapping that in an iferror() formula.

=IFERROR (value, value_if_error) so something like 

=IFERROR (FIND("type", "prototy", 1), -1) 

https://exceljet.net/excel-functions/excel-iferror-function

0 1
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...