LO Base - concatenate existing fields and create a field that displays it

[adamearl]

Been struggling with this for quite a while. I need to write emails regarding specific projects. For the subject for each email, I would like to take 5 fields from my existing table and have those in the subject line. It takes a long time to manually enter those so I thought I could create a new field and concatenate those 5 existing fields and then simply copy/paste that concatenated field into the email subject. So for example, the textbox field would grab existing data from the record and create “Field1” | “Field2” | “Field3” | “Field4” | “Field5”. Then i just copy/paste into subject line and compose my email.

Ive been trying to do it using a query and then create a subform on to my MainForm but unsuccessful.

Is there a way to do this?

Also, is there a way to have a button in my MainForm that when clicked fires up my mail program and populates the emailSubject with those 5 fields formatted as shown? That would be ideal but probably even more complicated than my first question.

Any help would be appreciated.

Thanks.

[Wanderer]

One question is where you had problems…

@adamearl

trying to do it using a query

The query will not see your field, but the related fields in the current record, but if field1, field2 is connected to col1 col2 a first (failing) approach would be a

SELECT col1 || col2 FROM table

One problem is: Concatenating an empty field gives NULL. Another the need of separators. So you need IFNUUL();or COALESCE() to catch the NULL- values.
A typical example, wich assumes Name is never NULL:

SELECT IFNULL( FirstName || ' ', '') || Name

.
But for your second part you need a macro anyway, so you could do your concatenation also in BASIC/Python etc.

@adamearl

have a button in my MainForm that when clicked fires up my mail program

You could try with a mailto: link but I have not tried. In an older discussion I found my mailer (Thunderbird) restricted attachments via mailto: so my macro actually uses the SHELL() function to call Thunderbird with necessary parameters.
So my button needs to be connected to a macro, wich could also use fields in the form. The guide for Base has several examples in the macro section, and I could also post my macro when back from work…

[RobertG]

Concatening of fields:
"Field1"||"Field2"||"Field3"
You could also add text content like
"Field1"||', '||"Field2"||', '||"Field3"
To avoid concatening results to NULL, as @Wanderer mentioned:
COALESCE("Field1",'')||', '||COALESCE("Field2",'')||', '||COALESCE("Field3",'')
Instead of IFNULL COALESCE also works with internal Firebird database and could contain more than only 2 options.
Have a look at Base Guide.
Description is a little bit older, but the German Base Handbuch V2408 won’t be translated again into English.