Joining multiple cells with empty cells inbetween

Ask for Help
1

Hi, I´m new to Grist but already having lots of fun. Referential databases are relatively new for me but I see a lot of benefits for our small company. Grist offers some really smart solutions that I really like.

I´m in the process of creating a database for our event management needs. We have the event itself, a lot of different participants with different goods and services, invoices etc. Participants will be linked to almost everything.

To make it easier to identify them in a reference lists, I joined multiple cells together, like this for example:

$Company + ", " + $Department+ ", " + $LastName+ ", " + $FirstName

It works, but it looks somewhat wrong if a participant is not associated with a company, for example: “, , LastName, FirstName”

I´m looking for a way to avoid the unnecessary “, “ when a cell is empty and already experimented with Concat and .format but they both created the same result so far. Is there a simple way of doing this? I´d like to avoid more complex formulas since I´m still in the process of learning and have to explain them later to my colleaques.

2

Look into the join() function for strings. I’m away from my computer but I think “, “.join($Company,$Department,$LastName,$FirstName) should do what you want.

3

Thanks for the suggestion. I tried it but got this error:
TypeError : str.join() takes exactly one argument (4 given)

It tested it again with only one string, with this result: C, o, m, p, a, n, y

I read the documentation for .join but the example given there only joins the contents of one column with text. As soon as more than one column is involved it doesn´t work anymore.

4 
$Company && $Department && $LastName && $FirstName 
  ? $Company + ", " + $Department + ", " + $LastName + ", " + $FirstName
  : ($Company && $Department && $LastName ? $Company + ", " + $Department + ", " + $LastName 
  : ($Company && $Department ? $Company + ", " + $Department 
  : ($Company ? $Company : "") + ", " + $LastName + ", " + $FirstName)))

This formula does the following:

  • It checks if all fields ($Company, $Department, $LastName, $FirstName) are non-empty.
  • If they are, it concatenates them with commas.
  • If some of the fields are empty, it adjusts the format accordingly, skipping over the missing data to avoid those extra commas.
5

That looks like something that could work, even though I can´t wrap my head around it yet. But there seems to a missing parenthesis somewhere in there. I get this error:

SyntaxError : unmatched ')' A `SyntaxError` occurs when Python cannot understand your code. The closing parenthesis `)` on line 5 does not match anything. (usercode, line 5)

6

Do this:

", ".join(filter(None, [$Company, $Department, $LastName, $FirstName]))

It should work if all your fields are string. If they are references, you should call the correct record field instead, as in $Person.LastName. If they are something else, you can convert them to strings using str($Company).

7

Yes, that worked right out of the box. Thank you.

This variation of .join() should be part of the official documentation.

Also thank you to Farhan for his take on the problem.

1 Like