7 Replies Latest reply on Nov 3, 2016 4:06 PM by lindseym

# Calculation Help -- nested functions

I posted this earlier today, but after spending the better half of my day on it (I've learned a lot! but nothing pertaining to this portion of the original question)

The calculation I am looking for help with is:

If (Field Name) contains the word "Level" ; get everything (excluding the first space) after the word "Level" until you reach a "-" , if (Field Name) does not contain the word "Level" return TBD (highlighted)

The (Field Name) contains different values for example, but all instances are separated by a dash [the last item in the list (or the first if there is only 1  item in the list does not have a dash after], the order of the values could be in any random order:

Bldg North-Level 2-Section Beyond the Trees

Level 1-Bldg A4

Section Beyond The Trees

Outcome:

Field Name Contents: Bldg North-Level 2-Section Beyond the Trees

If (Field Name) contains the word "Level" ; get everything (excluding the first space) after the word "Level" until you reach a "-" , if (Field Name) does not contain the word "Level" return TBD (highlighted)        would return 2

If (Field Name) contains the word "Section" ; get everything (excluding the first space) after the word "Section" until you reach a "-" , if (Field Name) does not contain the word "Section" return TBD (highlighted)        would return Beyond The Trees

If (Field Name) contains the word "Bubble Gum" ; get everything (excluding the first space) after the word "Bubble Gum" until you reach a "-" , if (Field Name) does not contain the word "Bubble Gum" return TBD (highlighted)        would return

Thank you kindly,

-L

• ###### 1. Re: Calculation Help -- nested functions

lindseym wrote:

"Section" would return [Beyond the Trees] from (Bldg North-Level 2-Section Beyond the Trees)

I think you've forgotten something in the explanation of the intended algorithm ... like

'If (Field Name) contains the word "Section"'

• ###### 2. Re: Calculation Help -- nested functions

I'll update right away. Thank you

• ###### 3. Re: Calculation Help -- nested functions

Hi Lindseym,

To my understanding, you need a function that takes 3 parameters:

- A string, which is the original text ( Bldg North-Level 2-Section Beyond the Trees for example)

- A substring, the word or expression you are going to select remaining text after (Level or Section)

- A fail text, which will be shown highlighted in case the string does not contain the substring

Here is a function that might accomplish what you want. It can be written in a smaller form without using the Let statement, but I find it simpler to understand this way. Also, this function was written in the data viewer with no DB open, so I have 2 nested let statements. The first Let Statement is not necessary to the function.

// the first let statement is here only to input the variables as I prepared this demo function in the data viewer

Let (

[

~string = "Bldg North-Level 2-Section Beyond the Trees" ;

~substring = "Level" ;

~fail = "TBD"

] ;

// -- core function, this is the actual function

Case (

// In order to start with simpler test, we first test the case where the substring is not contained in the script -> throw a failed test string (TBD or other) if true

patterncount ( ~string ; ~substring ) = 0 ; // This means the string does not contain the substring

textstyleadd ( ~fail ; HighlightYellow ) ;

// Else, we proceed to extrating alla the characters after our Substring

// using FM native function, we determine the substring position in the string, the length of the string and then extract all characters after Position + Length

Let (

[

~lenString = Length ( ~string ) ;

~lenSubstring = Length ( ~substring ) ;

~posSubstring = Position ( ~string ; ~substring ; 1 ; 1 ) ;

~return = Middle ( ~string ; ~posSubstring + ~lenSubstring ; ~lenString )

] ;

// At the end, we trim the result, so we get rid of any unwanted spaces

Trim ( ~return )

)

)

// -- end of core function

) // closing first let statement

• ###### 4. Re: Calculation Help -- nested functions

That was really helpful to see how things work. Thanks!

I got it to kind of work.... Looks like it's having an issue with the length? 'll keep playing with and trying to figure it out oh, the TBD works great!

I think one problem is that the column could read:

Building North-Section Around the Tree-Level 3

or

Section Around the Tree--Building North-Level 3

or

Level 3-Section  Around the Tree-Building North

I could change the import data to put a character around Ex: Level *3*-Section  *Around the Tree*-Building *North*

• ###### 5. Re: Calculation Help -- nested functions

Oh, I think it is getting everything after the "~substring word"..... so could I do one more nested fiction that grabs everything from the "dash" on and deletes it?

• ###### 6. Re: Calculation Help -- nested functions

I think this would work...

GetValue ( Substitute ( textField ; "-" ; ¶ ) ; 1 ) (I've tested this alone and it gets the desired results, but not sure how to incorporate it into the below calculation.

philmfdjunk ( I'm tagging you since I you commented on this post: get all text before character  , and are the first name i recognize that is still active.   )

Let (

[

~string = "Space" ;

~substring = "Level" ;

~fail = "TBD"

] ;

// -- core function, this is the actual function

Case (

// In order to start with simpler test, we first test the case where the substring is not contained in the script -> throw a failed test string (TBD or other) if true

patterncount ( ~string ; ~substring ) = 0 ; // This means the string does not contain the substring

textstyleadd ( ~fail ; HighlightYellow ) ;

// Else, we proceed to extrating alla the characters after our Substring

// using FM native function, we determine the substring position in the string, the length of the string and then extract all characters after Position + Length

Let (

[

~lenString = Length ( ~string ) ;

~lenSubstring = Length ( ~substring ) ;

~posSubstring = Position ( ~string ; ~substring ; 1 ; 1 ) ;

~return = Middle ( ~string ; ~posSubstring + ~lenSubstring ; ~lenString )

] ;

// At the end, we trim the result, so we get rid of any unwanted spaces

Trim ( ~return )

)

)

// -- end of core function

) // closing first let statement

• ###### 7. Re: Calculation Help -- nested functions

Okay! I got it!!!!

I replaced the very last function of Trim (~return)  with      GetValue ( Substitute ( ~return ; "-" ; ¶ ) ; 1 )

Presto!!!

EXACTLY what I was looking for! Thanks for your help!