Exporting data from table (via "export view" function) or simple copy-pasting - need to finetune the bad result

Baserow Help
, ,
1

Hi,
I need to export filtered part of my data from a table and have some things to sort out.
First: I need to combine text from three text data fields to get just one text string. These three should have a space between. So I created this formula:

concat(field(‘Title 2’),’ ‘,field(‘Title 1’),’ ',field(‘Name’))

The problem is that If I isply copy the combined result from this new formula field and paste it f.e. to my text editor, there is a quotation mark plus space at the beginning of every record where there were no text data in “Title 2” field. And also quotation mark at the end. Records (rows) that contained any text data in “Title 2” field are OK, without quotation marks.

I tried to test with the formula concat(field(‘Title 1’),’ ',field(‘Name’)) and result is OK. But ofcourse I miss then all text data from “Title 2” field :frowning:

I tried to export the view to csv file, open in Excel and split data to columns. By this I am able to remove quotation marks during the process, but there is still that additional space at the beginning in all rows where there was no text in “Title 2” field.

How should I change my formula field to get the correct result?

Btw. I found few more things when played with the “Export view” function. If I include (unhide) and export the multiple-select field, them if the row has more than one items, they are separated by comma, but there is no space after comma. :frowning:
And if there is and item in the multiple-select list, which contains more words and commas between them, then this item after export has quotation marks at its beginning and end.
So f.e. the item which looks like this:

word1, word2 word3, word4

Loks like this after export:
“word1, word2 word3, word4”

So if I have f.e. 4 multiple-select items included in a row and one of them is that one with more words and commas, the result looks like:

item1,item2,“word1, word2 word3, word4”,item4

Which means that third item is that one multi-word one.

To summarize my questions and requests for solution:

  1. how should I correctly finetune my formula field to remove unwanted quotation marks and starting space (for those specific rows without Field2 data)
  2. is there any solution how to avoid unwanted quotation marks when “multi-word with commas” item from multiple-select field is used during export or copy-pasting
  3. is it possible to put a space after comma between multiple-select items when exporting or copy-pasting

Thanks.

EDIT: maybe I should involve “join” or “isblank” or both somehow to get my formula to work as I want. But not very clear to me, how… Any help please?

2

Hi,

I dot not have the double quote problem on a mac when copy/pasting the data into TextEdit. I remember that it was already discussed somewhere in the forum, but I can’t find the post. I think that it had something to do with keyboard settings.

The good news is that you can solve the problem of the double spaces with the isblank() function. Assume the following table

image
image799×338 45.6 KB

The formula is the field test is:

concat(
field('Zip Code'),
if(isblank(field('City')),'',' '),
field('City'),' ',field('County')
)

So, you only add a space if the middle field has a value. This will maybe also solve the issue of the double quotes.

Just a quick thought: isn’t it easier to remove all the double quotes by using Find & Replace in your text editor?

Regards
Frederik

3

This could help, but I need to change the order: I want to concat in this order:

  1. City
  2. Zip Code
  3. County
    … and separated by a space, as you have now in your formula.

I tried to change the formula by “trial and error” method, but wasn’t able to get the correct result due to my very poor GREP or formula-creating skill and knowledge.

4

hi @frederikdc , please would you help me finetune the formula? See/read my last post, I mean, I would need to have those three values in a different order.
Thanks.

5

Hi,

You just need to change the order of the items in the concat function

concat(
if(isblank(field('City')),'',' '),
field('City'),' ',
field('Zip Code'),
field('County')
)
6

Thanks. Yes, this works, but there are always quotation marks at the beginning and at the end of every row, which is not such a problem because after importing into excel they are gone.
But there is also one redundant space at the beginning (right after the quotation mark) in every row. So the result right now is:

" City, Zip Code, County"
or
" Zip Code, County"

Would you know , how to get rid of that space?

7

The quotation marks are probably added by the operating system and are not related to baserow.

If the city is you first field, you need to check if a space must be added after the city

concat(
field('City'),
if(isblank(field('City')),'',' '),
field('Zip Code'),
' ',
field('County')
)
8

BINGO!
Perfect, it works! No space at the beginning, not even quotation marks. :+1:

Thank you very much for your help, @frederikdc

9

Btw. I have one more question:
I have one field which is a multiple select type. Sometimes my rows contain more items in this field, not just one.
If I do the view export (f.e. in csv format), then between all items a comma is added as a separator in the csv exported file.
I would need to add a space after every comma separator. I think a properly created formula could do it (concat these multiple items and put a comma with a space between them). How should it look like?

EDIT: Hmm, it looks like it may not be possible - I am browsing the formula list of all algorithms/parameters/fields/operators, but I don’t see my multiple select field there.

10

Indeed, a field of the type multiple_select cannot be used in a formula
image

A workaround is to replace to store the values of the multiple_select field in a separate table and use the a link to table field instead. A linked to table field has much more possibilities

11

Yes, I know, but I would like to have the data colorized, which is not possible with Link to table items. Maybe it will be implemented in the future? @olgatrykush ?

But more important - working with (choosing from) multiple select field is so much faster than with a linked table. When I click the + icon or just when I just start typing, it’s almost instant (I have two multiple select field - one contains 76 items, the other one has 85 items). While if I would do that with linked table, then 1) inserting by simple starting typing does not work and 2) after clicking the + button I have to wait few seconds and after the table is displayed, then I can select a record from it

But I understand that working with (linked) table has some advantages if compared to multiple select field.

12

Hey @marcus, the development of the feature to support multiple select in the formula field is currently in progress. It is expected to be released in Baserow 1.22: Support multiple select in the formula field (#1363) · Issues · Baserow / baserow · GitLab. :slightly_smiling_face:

1 Like