Ask Your Question

Revision history [back]

Field Concatenation with Line Feed / New Line in Base

Is it possible to have line feeds between fields/columns when concatenating them together? I am hoping to concatenate full mailing addresses together like this...

address1 || line-feed || address2 || line-feed || City || ', ' || State || ' ' || Zip

or a similar result with a CONCAT() so that the result will be something like

12345 Any Street Somewhere, WI 54911

(ironically the line feed between the address and the city isn't showing up in the preview on this forum, but I typed one)

The primary reason I want this is to keep from having to position several form fields close together to approximate that appearance. I would like it to be all in one continuous "paragraph." Sometimes there will be an address1 and address2, other times just an address1. Not sure if queries in Base can have conditions like IF to only add address2 if it isn't blank?

I've seen examples on this forum and elsewhere concatenating first and last name with a space or comma between... but what if a person only has a first name in a record? Is there a way to keep there from being a space/comma delimiter using a condition?

In like manner, I'm looking to create a paragraph with line feeds and only have the address2 line feed when it's not blank so there's not an empty space there. All this would be easy in a programming language like PHP, Javascript or whatever. But I was hoping there was a way to do some or all of it in the query itself so a macro wouldn't be necessary.

Possible? I'm kinda guessing no, but can't hurt to ask.

Field Concatenation with Line Feed / New Line in Base

Is it possible to have line feeds between fields/columns when concatenating them together? I am hoping to concatenate full mailing addresses together like this...

address1 || line-feed || address2 || line-feed || City || ', ' || State || ' ' || Zip

or a similar result with a CONCAT() so that the result will be something like

12345 Any Street Somewhere, WI 54911

(ironically the line feed between the address and the city isn't showing up in the preview on this forum, but I typed one)

The primary reason I want this is to keep from having to position several form fields close together to approximate that appearance. I would like it to be all in one continuous "paragraph." Sometimes there will be an address1 and address2, other times just an address1. Not sure if queries in Base can have conditions like IF to only add address2 if it isn't blank?

I've seen examples on this forum and elsewhere concatenating first and last name with a space or comma between... but what if a person only has a first name in a record? Is there a way to keep there from being a space/comma delimiter using a condition?

In like manner, I'm looking to create a paragraph with line feeds and only have the address2 line feed when it's not blank so there's not an empty space there. All this would be easy in a programming language like PHP, Javascript or whatever. But I was hoping there was a way to do some or all of it in the query itself so a macro wouldn't be necessary.

Possible? I'm kinda guessing no, but can't hurt to ask.

In case it matters, I am using MySQL as the backend rather than the internal database supplied by Base. Perhaps in that case I could use MySQL's more advanced querying options with a direct SQL statement?

Field Concatenation with Line Feed / New Line in Base

Is it possible to have line feeds between fields/columns when concatenating them together? I am hoping to concatenate full mailing addresses together like this...

address1 || line-feed || address2 || line-feed || City || ', ' || State || ' ' || Zip

or a similar result with a CONCAT() so that the result will be something like

12345 Any Street Street

Somewhere, WI 54911

(ironically the line feed between the address and the city isn't showing up in the preview on this forum, but I typed one)

The primary reason I want this is to keep from having to position several form fields close together to approximate that appearance. I would like it to be all in one continuous "paragraph." Sometimes there will be an address1 and address2, other times just an address1. Not sure if queries in Base can have conditions like IF to only add address2 if it isn't blank?

I've seen examples on this forum and elsewhere concatenating first and last name with a space or comma between... but what if a person only has a first name in a record? Is there a way to keep there from being a space/comma delimiter using a condition?

In like manner, I'm looking to create a paragraph with line feeds and only have the address2 line feed when it's not blank so there's not an empty space there. All this would be easy in a programming language like PHP, Javascript or whatever. But I was hoping there was a way to do some or all of it in the query itself so a macro wouldn't be necessary.

Possible? I'm kinda guessing no, but can't hurt to ask.

In case it matters, I am using MySQL as the backend rather than the internal database supplied by Base. Perhaps in that case I could use MySQL's more advanced querying options with a direct SQL statement?

Field Concatenation with Line Feed / New Line in Base

Is it possible to have line feeds between fields/columns when concatenating them together? I am hoping to concatenate full mailing addresses together like this...

address1 || line-feed || address2 || line-feed || City || ', ' || State || ' ' || Zip

or a similar result with a CONCAT() so that the result will be something like

12345 Any Street

Somewhere, WI 54911

(ironically the line feed (but without the blank space between the address and the city isn't showing up in the preview on lines... this forum, but I typed one)forum does that to paragraphs)

The primary reason I want this is to keep from having to position several form fields close together to approximate that appearance. I would like it to be all in one continuous "paragraph." Sometimes there will be an address1 and address2, other times just an address1. Not sure if queries in Base can have conditions like IF to only add address2 if it isn't blank?

I've seen examples on this forum and elsewhere concatenating first and last name with a space or comma between... but what if a person only has a first name in a record? Is there a way to keep there from being a space/comma delimiter using a condition?

In like manner, I'm looking to create a paragraph with line feeds and only have the address2 line feed when it's not blank so there's not an empty space there. All this would be easy in a programming language like PHP, Javascript or whatever. But I was hoping there was a way to do some or all of it in the query itself so a macro wouldn't be necessary.

Possible? I'm kinda guessing no, but can't hurt to ask.

In case it matters, I am using MySQL as the backend rather than the internal database supplied by Base. Perhaps in that case I could use MySQL's more advanced querying options with a direct SQL statement?