Skip to main content
Solved

Help!

P
Forum|alt.badge.img
Hello, I want to know if there is a formula to identify a text within a link cell, in this cell conditions that a home meets appear. Example: unreleased, released, foundation, walls, roof, finishes, finished. I want the formula to identify if the cell evaluated says without releasing the result is UNSTARTED, if it contains released the result is IN PROCESS or if it contains finished, the result is TERMINATED. I have tried with the IF formula, but I have not succeeded. Thank you
 

Best answer by Alexey_Gusev

Hi,
It depends on what does it mean “not succeeded”? You couldn’t create formula at all? Or it works, but not quite correct in some cases?
Actually, you can use
IF(FIND(‘something’, {FieldName}) as condition
or IF({FieldName},   - to check for ‘non-emptiness’. It’s good to start from it. 
You can use REGEX functions as well, in your case it’s almost the same, in many cases REGEX have more advantages, but FIND is more clear for beginners.

Try to think in terms of ‘guard clauses’, like ‘if it contains “finished”, it should be TERMINATED, no matter what about released. That’s why in formula above this condition goes second.
The problem why your formula might not work 100% as expected, same as formula above -  because ‘unreleased’ contains ‘released’. 
There are several ways to solve it  - by substituting value in formula or maybe by using ARRAYJOIN with a given divider. Link is array, in formula it turns to string in the form of ‘messofvalues’. With AJ, you can set “, “ as divider and do the proper search.


 

View original
Did this topic help you find an answer to your question?

2 replies

Ben_Young1
Forum|alt.badge.img+9
  • Ben_Young1
  • Brainy
  • 520 replies
  • March 17, 2025

Hello ​@p.gomez.adm!

It’s certainly possible to evaluate whether a linked record field’s displayed string value(s) contain one or more values.

There are multiple ways to achieve this, but here’s how I would go about it:

IF(
  {Relationship},
  IF(
    REGEX_MATCH({Relationship}, "(?i)finished"),
    "TERMINATED",
    IF(
      REGEX_MATCH({Relationship}, "(?i)released"),
      "IN PROCESS",
      "UNSTARTED"
    )
  )
)

This formula will check if the linked record field (“Relationship”) returns a value. 
If the field contains “finished,” it returns “TERMINATED.” If the field contains “released,” it returns “IN PROCESS.” Otherwise, the formula returns “UNSTARTED.”

If the linked record field does not return a value, then nothing is returned.

P
1 person likes this
  • P
    p.gomez.adm
Alexey_Gusev
Forum|alt.badge.img+12
  • Alexey_Gusev
  • Brainy
  • 1115 replies
  • Answer
  • March 17, 2025

Hi,
It depends on what does it mean “not succeeded”? You couldn’t create formula at all? Or it works, but not quite correct in some cases?
Actually, you can use
IF(FIND(‘something’, {FieldName}) as condition
or IF({FieldName},   - to check for ‘non-emptiness’. It’s good to start from it. 
You can use REGEX functions as well, in your case it’s almost the same, in many cases REGEX have more advantages, but FIND is more clear for beginners.

Try to think in terms of ‘guard clauses’, like ‘if it contains “finished”, it should be TERMINATED, no matter what about released. That’s why in formula above this condition goes second.
The problem why your formula might not work 100% as expected, same as formula above -  because ‘unreleased’ contains ‘released’. 
There are several ways to solve it  - by substituting value in formula or maybe by using ARRAYJOIN with a given divider. Link is array, in formula it turns to string in the form of ‘messofvalues’. With AJ, you can set “, “ as divider and do the proper search.


 

P
1 person likes this
  • P
    p.gomez.adm

Reply


Most helpful members this week

Terms & Conditions