1 2 Previous Next 16 Replies Latest reply on Sep 30, 2016 6:44 PM by philmodjunk

Contanation Calculation

Can anyone offer advice for best practice for simply concatenating several fields, but not including a field and a carriage break if a field is empty?

Field 1

Field 2

Field 3

Field 4

Field 5

Id like to simply concatenate as shown above.

But if any field is EMPTY, then it and its carriage break is not included in the calculation.

So if Field 2 is EMPTY, then

Field 1

Field 3

Field 4

Field 5

Or if Field 2 and Field 3 is EMPTY, then

Field 1

Field 4

Field 5

Thanks so much!

• 1. Re: Contanation Calculation

Try:

List ( Field 1 ; Field 2 ; Field 3 ; Field 4 ; Field 5 )

Edit: from the List function reference:

"Purpose

Returns a concatenated list of non-blank values (separated by carriage returns) for a field or fields."

• 2. Re: Contanation Calculation

Hi,

have you tried the List function?  e.g. List ( table::field1, table::field2, table::field3, etc)

• 3. Re: Contanation Calculation

Thanks so much.  That did the trick.

One more question.

If I then want to create a List field to CONCATENATE  List ( table::field1, table::field2, table::field3, etc), how do I ensure there is a carriage break between the results?

Field 1

Field 2

Field 3

Field 4

Field 5

Field 1

Field 3

Field 4

Field 5

Field 1

Field 4

Field 5

• 4. Re: Contanation Calculation

Substitute ( List ("a", "b") ; "¶"; "¶¶" )

• 5. Re: Contanation Calculation

Use:  List ( Field1; Field2 ; Field3 ; " " ; Field4 ; Field5 )

to get:

Field1

Field2

Field3

Field4

Field5

• 6. Re: Contanation Calculation

I got the email with your question about ensuring a carriage return.  There, it looked like you wanted double-spacing between the values.  Is that what you wanted?  (The double-spacing didn't translate to the post; or the email added an extra carriage return.)

• 7. Re: Contanation Calculation

This might be clearer.

I have a table BIBLIOGRAPHY, and related table PROJECTS, connected through a join table.

In BIBLIOGRAPHY I have a field Citation that is List (Field 1; Field 2; Field 3; Field 4)

In the PROJECTS table I have a field References that is List(BIBLIOGRAPHY:Citation).

I need to make sure that there is a space between each of the Citation clusters in the References result, so:

Field 1 (record 1)

Field 2 (record 1)

Field 3 (record 1)

Field 4 (record 1)

Field 1 (record 2)

Field 2 (record 2)

Field 3 (record 2)

Field 4 (record 2)

• 8. Re: Contanation Calculation

Oh,

what Phil said:

Use:  List ( Field1; Field2 ; Field3 ; " " ; Field4 ; Field5 )

List ( List(field1; field2); " "; List(table::field) )

• 9. Re: Contanation Calculation

Im puzzled by that.  What do the " " marks between Field3 and Field4 do?

• 10. Re: Contanation Calculation

Sorry - I tend to be a bit too brief.

It's an invisible placeholder.  It could be anything, like "------".

• 11. Re: Contanation Calculation

My question is "why is there a placeholder between Field3 and Field4?"

• 12. Re: Contanation Calculation

I believe that Phil was just providing an example of how to create what looks like a blank line within your list.  Requirement one was "no blank lines if the fields are empty."  List does that.  So, you can trick List into providing a blank line by putting a space in as a parameter.  (Similarly, if any of your fields look empty, but actually contain a space, a blank line will show in your list.)

• 13. Re: Contanation Calculation

Off-topic director, but I used to label my field or variable concatenations "cat".  My co-workers thought it had something to do with felines.

• 14. Re: Contanation Calculation

OK... thanks so much.  One more question on this matter.

If Field 1 is always a year, is there a way to have the List(field) in the PROJECTS table automatically sort according to date?  I tried to sort the records in the BIBLIOGRAPHY table by that field, but it does not have any effect on the ultimate List.

1 2 Previous Next