7 Replies Latest reply on Dec 27, 2016 6:47 AM by lindseym

# Let and Case Function - Calculation Question

Hello and Happy Holidays

I am trying to combine the contents of two columns into one.

Column 1 Title: Location Calculation

Column 1 Contents: 201

Column 2 Title: Building From Spaces

Column 2 Contents: A

I have this calculation, and it is working for what I typically do - which is usually get the location calculation only.

Let ( [

text =  LeftWords ( Location Calculation ; 1 ) ;

n = text + 0 ;

text = Case ( n = 0 ; text ; n )

] ;

Case (

\${Copy (Side A)} = "BLDGLOC #" ;

Substitute ( text ;

"TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) );

Substitute (

\${Copy (Side A)} ;

["TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow )];

["LOC #" ; text ]

)

)

)

It properly returns the left words of the column titled: Location Calculation

Now I want to modify the above to return the contents from a column called "Building From Spaces"

I tried this:

Let ( [

text =  GetField ( Building from Space ) & LeftWords ( Location Calculation ; 1 ) ;

n = text + 0 ;

text = Case ( n = 0 ; text ; n )

] ;

Case (

\${Copy (Side A)} = "LOC #" ;

Substitute ( text ;

"TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) );

Substitute (

\${Copy (Side A)} ;

["TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow )];

["LOC #" ; text ]

)

)

)

and it returns: BLDG201 When the location calculation is 201 and the building is A

What I want is A201

(also, im still working on the n = text + 0 part of the function --- just know right now it does what I need it to do

Thanks!

-L

• ###### 1. Re: Let and Case Function - Calculation Question

What is this? \${Copy (Side A)}

Do you have a field named Copy (Side A)?

• ###### 2. Re: Let and Case Function - Calculation Question

Yes BruceRobertson . I have failed named Copy (Side A)

This is where the original content of "BLDGLOC#" resides, which I want to replace with "A201" no spaces.

• ###### 3. Re: Let and Case Function - Calculation Question

Hi,

Also, to make it easier to discuss this with others, note the difference between fields and \$variables.

1 of 1 people found this helpful
• ###### 4. Re: Let and Case Function - Calculation Question

I've attached the FMP to the original comment as well.

I'm actually not using a script to do this calculation.

Sorry about the pseudo-code. I'm learning.. and it's one of those situations that now I know "better" for some things - like parentheses in the field names and since I am doing this internally for my company - -I don't really have the time and resources to go back and make it pretty and clean up the "code"

Maybe one day!

• ###### 5. Re: Let and Case Function - Calculation Question

You don't need GetField ( Building from Space ) for getting the content ("A" in example) of the field. The function is used when a field have field name as its content.

Your example have too few data to answer, but first try is

Building from Space & <<existing calculation here>>

since you want to concatenate "A" and "201".

1 of 1 people found this helpful
• ###### 6. Re: Let and Case Function - Calculation Question

Case(

Copy Side A = "BLDGLOC #" ;

But none of your data contains a space before the #.

• ###### 7. Re: Let and Case Function - Calculation Question

user19752

The concept to concatenate the two fields proved to be the quickest way for me to achieve success.

I ended up making a new field to combine them, and using the "hide when." to "filter" when I need this special formula.

I'm sure it isn't the prettiest way to make it work... but it does work.

The other suggestions were very helpful -- I just need more time to sit with them and then learning how they work.

Thank you Kindly.

-L