Find formula returns -1 value

1

Hello,

I am using the find formula to get a row number in another table. No issues there. However, when there is no match, I am getting a “-1” value, instead of an error or null.

This then creates other errors as i’m using the find formula with some other math operations, and since -1 is a number I am getting values when I should be getting errors.

Am I doing something wrong?

The formula is:
Find(name,[Employees].name)

3

@Andrea_Pastor

Find() can be used in a Boolean way, like this:

If(Find("keyword",[text column]) > 0, "true", "false")

Implemented like this:

If(Find("keyword",[text column]) > 0, {do some thing}, "")

…where the empty string "" is simply ignored. This might eliminate “-1” cascading through your formulas.

1 Like
4

Yea, I thought of solving it using an if statement, just didn’t seem right I guess.

Thanks for the input! I’ll give it a go.

1 Like
5

@Andrea_Pastor

I also suspect there’s a better way to do what you’re trying to do than using Find(). If you want to share a screenshot, I’ll give you my $.02.

6

Hey Ander,

I actually was able to solve this by having smaller tables + a lookup formula. That does give an error when it does not find a value, so I managed to have a smaller formula.

Thanks for the tip!

1 Like
7

Dear @Andrea_Pastor and @Ander

I felt to jump in: to my opinion the “FormulaMap” should solve your problem.

This is an example I learned from @Daniel_Stieber

Here is a list of objects that are in stock and a list of objects that are ordered. Use formulaMap to see, which objects are NOT in stock.

stock: wrench,hammer,bottle,nails

order: screws,bottle,shovel,beer

order.FormulaMap(if(stock.contains(CurrentValue),"",CurrentValue)).filter(CurrentValue.IsNotBlank())

objects that are NOT in stock screws,shovel,beer

I hope this input will bring you forward in the development of the project :pray:

Kind regards,
//JP