Several database functions in one table cell

[Nico_be]

Hello,
Is it possible to place a formula with several database functions in a single cell?  And even add a fixed text?

For example, for plants, can we group these two cells into one? Instead of having :
A1 : =('Plant Record'.'Botanical name')
A2: ='Plant Record'.'Remark 1'.

Have something like this :

A3: ==('Plant Record'.'Botanical name') ????  ('Plant Record'.'Remark 1')

Thanks a lot!

[Tom W.]

Not sure about combining the functions but what you can do is use concat to combine the data from two (or more) columns into one e.g. put =concat(A2,B2) in C2 then collapse columns A2 + B2 to hide them.

 

Use =concat(A2, ' ', B2) if you want a space in between or =concat(A2, ' , ', B2) if you want a comma, etc.

[Nico_be]

unfortunately it doesn't work, I tried.

[Tom W.]

What doesn't work?

[Nico_be]

nothing appears in the list of objects concerned by the criteria: the cells are empty.

 

[Tom W.]

Maybe post a file or at least some screenshots. It works for me. But maybe I'm totally misunderstanding what you're asking for. You have a file with Plants in it + a database report which is returning data from those Plants right? You have the 'botanical name' field of the plant record being returned in one column + the 'remark 1' field being returned in another column yes? If so you can use concat to combine those two columns into a single third column + add additional text or punctuation in the process then hide the original two columns.

[Nico_be]

And yes, it works!  By making a small file to send here, I redid the test and it works perfectly.
I probably made a syntax error on my first try.
Thanks a lot!

 

While I'm at it: can I insert a "line feed" in a formula?  I'm trying to create a more interesting layout than simple lines...

 

[Tom W.]

1 hour ago, Nico_be said:

While I'm at it: can I insert a "line feed" in a formula?

 

I have no idea! But someone else might e.g. @Pat Stanford, @michaelk

 

Again can you post a screenshot of the kind of thing you mean? Be interested to see

[Pat Stanford]

Ignore everything I wrote below.

 

CHAR(10) is a line break.

 

=CONCAT('Plant Record'.'Latin Name', CHAR(10), 'Plant Record'.'Comment 1')

 

Possible, and not even hard but a little convoluted.

 

In a worksheet spreadsheet cell, edit the row height so you can see at least two rows of text.

 

In the cell type any character you choose.

Hold down the Option key and press Return

Type another character.

 

You should now have a cell with a "line break" character in it.

 

Move the cursor to before the character on the second row.

Hold down the shift key and press the back arrow.  The "line break" should be highlighted.

Copy.

Paste into your formula between two single quotes. The formula will "line break" at that point. Click the disclosure triangle at the right end of the formula bar so you can see both lines.

 

 

[Nico_be]

Thanks for your help already! 🙂

To sum up, here's the objective:
To have several fields in a single table cell, in an attempt to create a more interesting layout than a simple table.  The test file is appended.

 

in the meantime I had found the CHAR(10) trick: https://developer.vectorworks.net/index.php/VS:Chr

 

What almost works:
The CONCAT formula refers to cells in the same table.
 

=CONCAT(D3; (CHAR(13)); 'Flowering: '; E3; (CHAR(13)); (CHAR(13)); 'In a few words: '; F3)

But it seems that the number of characters is limited, as the end of the text in F3 is missing.  I couldn't find any information on character limitation in the reference manual.  Another problem is that I have to refer to columns in the same table, which is inconvenient for printing.  I've tried referencing the data in the other table but as it's a dynamic list this doesn't work as it returns the value of the leading row only, not the list.

I've tried @Pat Stanford's  formula, but it doesn't work (I'm running Windows 10, so I've replaced the commas (,) with semicolons (;) ).

 

 

Any idea?

 

 

Test tableau Copie.vwx

Edited December 1, 2023 by Nico_be

[Nico_be]

there seems to be a bug with the tables, the formula works in one table of the file but not in the other.

It doesn't make searching any easier 🙂
But there's still the problem of character limitation.
second file attached.

Test tableau Copie2.vwx

[Pat Stanford]

There definitely seems to be corruption in the worksheet with the images.  In VW2024 I can not even reliably select a portion of text from the formula bar.

 

I recommend you delete that worksheet and recreate it.

[Nico_be]

Yes, that's what I've done, I've recreated a new table, I'm getting something more interesting in the formatting of the data, but there's the problem of character limitation which doesn't display all the information in the field. I can't find any information about this anywhere.
The new file is attached

Test tableau Copie3.vwx

[Pat Stanford]

It is a limitation of the CONCAT function. It appears to still be using strings that has a length limit of 255 characters. 

 

Database Subrow cells seem to handle up to 336 characters.

 

I don't this you are going to find a work around for this to format this they way you want.

 

You might want to look at Graphical Legends instead as they might be able to do this.

[Nico_be]

Ok Pat, I'll have a look at graphical legend, it's probably a beter idea!

Thanks.