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

Question

Question

laserfiche forms "find" Function

Classic Form Designer Forms
Updated one day ago
asked two days ago

I am attempting to use an IF statement to populate a field. The date for the field I am reading has a few variations of the data that I need to use to determine the path of a process flow. The first two items are able to be identified with no problem but the third phrase is not working. 

 

The formula is:

=IF(FIND("Mo Incr Benefit", Plan_Description), "MIB", IF(FIND("Monthly Increasing Benefit", Plan_Description), "MIB", IF(FIND("MIB", Plan_Description), "MIB", "0")))

 

When the plan_description field has: Mo Incr Benefit / Limited 10-Pay - Imnt Dth or Monthly Increasing Benefit / Limited 10-Pay - Imnt Dth it works fine but when i enter MIB  HQ   5PAY FEMALE or (MIB) GRADED BENEFIT - NO HEALTH   7 PAY - FEMALE.

Any ideas on what I can do to get past this error? The message I receive is: 

 

0 0

Answer

SELECTED ANSWER
replied two days ago

Ideally, LFForms would be coded to return an error value like 0 or -1, but since it doesn't do that, we need to ensure that the value we are searching always has the values we need to find, so that we never see the error.

Here's one way to do that.

Add an additional field (you probably want to use Field Rules to hide it).  I named this one Plan_Description2 as it's going to modify the value from Plan_Description.  The formula for this field takes the value from Plan_Description and adds all of the possible value we are searching for on to the end of it.  This looks messy, which is why we are hiding it with Field rules.

=CONCATENATE(Plan_Description,"Mo Incr Benefit","Monthly Increasing Benefit","MIB")

Now, when we run the search, we are searching against this new Plan_Description2 field instead of Plan_Description.  We know we will always find the values in Plan_Description2, since we forcibly added them to the end, so we need to make sure we find them before what we put at the end.  We can do that by making sure the index number from FIND is low enough that it would be coming from Plan_Description and not what we tacked on the end in Plan_Description2 - using a LEN() function to get the length of the value from Plan_Description.  The new formula looks like this: 

=IF(FIND("Mo Incr Benefit", Plan_Description2)<=LEN(Plan_Description), "MIB", IF(FIND("Monthly Increasing Benefit", Plan_Description2)<=LEN(Plan_Description), "MIB", IF(FIND("MIB", Plan_Description2)<=LEN(Plan_Description), "MIB", "0")))

 

Put that all together, and the examples you showed all appear to work for me.

For what it is worth, I tried to see if the same behvaior carried over to the Modern Designer as well, and at least as of version 11.0.2311.50556 is does appear so.

3 0

Replies

replied two days ago

The IF statement is expecting a TRUE or FALSE evaluation for the first value.  You are using the FIND() formula for the first value.  FIND() does not return true or false, but rather a number indexing the position of the searched value within the string.

The reason it is working sometimes, is because it's returning position 1 which is evaluating as true in a 1=true / 0=false sense - but then when it's returning another value, like 2, or 6, or 93, it doesn't know what to do.

Try evaluating if the FIND() formula is >= 1 instead.  Like this: 

=IF(FIND("Mo Incr Benefit", Plan_Description)>=1, "MIB", IF(FIND("Monthly Increasing Benefit", Plan_Description)>=1, "MIB", IF(FIND("MIB", Plan_Description)>=1, "MIB", "0")))

 

1 0
View 2 previous replies
replied two days ago

I did try that but I am still getting the same results.

0 0
replied two days ago

I forgot that FIND() doesn't return a 0 or -1 or anything like that when it doesn't find the value it is searching for, it generates an error like this.

I'm trying to remember how I have handled this in the past.

0 0
SELECTED ANSWER
replied two days ago

Ideally, LFForms would be coded to return an error value like 0 or -1, but since it doesn't do that, we need to ensure that the value we are searching always has the values we need to find, so that we never see the error.

Here's one way to do that.

Add an additional field (you probably want to use Field Rules to hide it).  I named this one Plan_Description2 as it's going to modify the value from Plan_Description.  The formula for this field takes the value from Plan_Description and adds all of the possible value we are searching for on to the end of it.  This looks messy, which is why we are hiding it with Field rules.

=CONCATENATE(Plan_Description,"Mo Incr Benefit","Monthly Increasing Benefit","MIB")

Now, when we run the search, we are searching against this new Plan_Description2 field instead of Plan_Description.  We know we will always find the values in Plan_Description2, since we forcibly added them to the end, so we need to make sure we find them before what we put at the end.  We can do that by making sure the index number from FIND is low enough that it would be coming from Plan_Description and not what we tacked on the end in Plan_Description2 - using a LEN() function to get the length of the value from Plan_Description.  The new formula looks like this: 

=IF(FIND("Mo Incr Benefit", Plan_Description2)<=LEN(Plan_Description), "MIB", IF(FIND("Monthly Increasing Benefit", Plan_Description2)<=LEN(Plan_Description), "MIB", IF(FIND("MIB", Plan_Description2)<=LEN(Plan_Description), "MIB", "0")))

 

Put that all together, and the examples you showed all appear to work for me.

For what it is worth, I tried to see if the same behvaior carried over to the Modern Designer as well, and at least as of version 11.0.2311.50556 is does appear so.

3 0
replied one day ago

That did the trick! Thanks for the help!

1 0
replied one day ago

Happy to help!  Glad it worked for you.

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

Sign in to reply to this post.

Details
Related Posts
Loading ...