1 2 Previous Next 19 Replies Latest reply on Nov 13, 2012 8:57 PM by flusheddata

# Most efficient way to write this calculation

I have four fields (FIELD1, FIELD2, FIELD3, FIELD4). The fields may be empty or have content.

I want to have a formula that does FIELD1 & "¶" & FIELD2 & "¶" & FIELD3 & "¶" & FIELD4, so I would end up with:

FIELD1

FIELD2

FIELD3

FIELD4

as the result.

However, if one of the fields is empty, I don't want it to have the extra carriage return.

For example, if FIELD2 is blank, I do not want:

FIELD1

FIELD3

FIELD4

FIELD1

FIELD3

FIELD4

I am trying to create this calculation as effiiently as possible. Right now I have it working extremely inneficiently, as it has multiple nested IF's with IsEmpty. I've done this before with only two fields and it is pretty straight forward. But the number of combinations with 4 fields is a real bear.

Any suggestions of better approaches?

• ###### 1. Re: Most efficient way to write this calculation

Alas --

The best version I have come up with so far is like this:

If ( IsEmpty ( Field1 ) ; "" ; Field1 &¶ )

& If ( IsEmpty ( Field2 ) ; "" ; Field2 &¶ )

& If ( IsEmpty ( Field3 ) ; "" ; Field3 &¶ )

& If ( IsEmpty ( Field4 ) ; "" ; Field4 &¶ )

where '¶' is the paragraph mark.  This is equivalent to the sort of thing in word processing where you specify 'xx points after each line' with all of the problems that comes with it.

Somewhat tedious, but easily expandable, and still gives the right result if you decide later to comment out one of the lines.

-- Drew Tenenholz

• ###### 2. Re: Most efficient way to write this calculation

List ( ) appears to be an easy function to use. Blanks do not show.

List ( field1 ; field2 ; field3...)

• ###### 3. Re: Most efficient way to write this calculation

Thanks.  Issue I have with this one is it adds extra paragraph marks at the end that need to be trimmed.  That was the reason I had so much complexity in my formula.

• ###### 4. Re: Most efficient way to write this calculation

Hmmm.  Never thought of using that.  It worked.  I used List() a lot to create a list of portal records, but did not think of using it this way.

Interestingly, I have a list of a list.  Fields1-4 are actually calculations, using List() in a related table based on which entries met a certain criteria.  So Field1 is a List() of records where rating =1 , Field2 is where rating=2, etc.  And then this List solved my "recombining" them together, in the new order.

I wish List allowed you to create a list based on a criteria (like a SumIFS in Excel), that would eliminate a lot of intermediate calculation fields.

• ###### 5. Re: Most efficient way to write this calculation

Yes! List() is the correct method, as it doesn't add a value (if blank).

You can use fields, variables (that evaluate to values) and literal (quoted text) values.

```let (
\$abc = table::text2
; list ( table::text1, \$abc, "what" )
)
```

Beverly

• ###### 6. Re: Most efficient way to write this calculation

I wish List allowed you to create a list based on a criteria (like a SumIFS in Excel), that would eliminate a lot of intermediate calculation fields.

Have you explored FilterValues( ) ?

Malcolm

• ###### 7. Re: Most efficient way to write this calculation

Issue I have there is that I am trying to find records based on a couple of different fields/attributes.  For example, If I want to find all of the Ratings=1, where the item is a Fruit and the Color Red, and list the products names for each of them.  I don't believe I can accomplish that with FilterValues, right?

• ###### 8. Re: Most efficient way to write this calculation

Issue I have there is that I am trying to find records based on a couple of different fields/attributes.  For example, If I want to find all of the Ratings=1, where the item is a Fruit and the Color Red, and list the products names for each of them.  I don't believe I can accomplish that with FilterValues, right?

It may not be the most obvious use of FilterValues. I'm sure if the challenge was right we'd see a filterValues solution quickly enough.

You may want to use a relationship based on multiple attributes to achieve that result

Malcolm

• ###### 9. Re: Most efficient way to write this calculation

When I try your "let" I am getting ? as the result.

So right now, I have "parent" table called Table1, and I have "child" related table called Table2.

I have a field on Table2 called Data, which calculates three fields combined with Pipes ("|"), so long as they meet a criteria (rating = 2).

I then have a field on the parent table, "Test", which is the output of LIst(Table2::Data).

I have tried to put the Let (\$data = ... with the variable equal to the formula from Table2::Data, and then list(\$data).

Thoughts?

• ###### 10. Re: Most efficient way to write this calculation

alas wrote:

I have four fields (FIELD1, FIELD2, FIELD3, FIELD4).  The fields may be empty or have content.

I want to have a formula that does FIELD1 & "¶" & FIELD2 & "¶" & FIELD3 & "¶" & FIELD4, so I would end up with:

FIELD1

FIELD2

FIELD3

FIELD4

as the result.

My guess is that Fields 1 to 4 need normalisation. Putting them into related table will probably simplify many issues you're currently having.

• ###### 11. Re: Most efficient way to write this calculation

This thread started with a fairly simple question which was answered quickly but the thread is still running. Why don't you tell us what you are trying to achieve.

• ###### 12. Re: Most efficient way to write this calculation

I agree with Vaughan ... you are attempting to solve a problem that shouldn't exist to begin with.  You have multiple 'like' fields which should be related records and you have (probably) several fields which hold multi-lines which are best treated as records as well.

The good news is that you have caught it early.  :-)

• ###### 13. Re: Most efficient way to write this calculation

Easy.

Let ( List =

Case ( Not IsEmpty ( Field1 ) ; Field1 & "¶" ) &

Case ( Not IsEmpty ( Field2 ) ; Field2 & "¶" ) &

Case ( Not IsEmpty ( Field3 ) ; Field3 & "¶" ) &

Case ( Not IsEmpty ( Field4 ) ; Field4 & "¶" ) ;

LeftWords ( List ; WordCount ( List ) ) // This removes the extra CR

)

• ###### 14. Re: Most efficient way to write this calculation

flusheddata wrote:

LeftWords ( List ; WordCount ( List ) ) // This removes the extra CR

Actually, it removes more than just the extra CR: if your list contains:

"#Alpha Me¶Bravo You¶Charlie Who?!"

it will be decimated to:

"Alpha Me¶Bravo You¶Charlie Who"

This is, of course, in addition to the obvious why.

1 2 Previous Next