Formula in graphic legend for hardscape components?

[Anders Blomberg]

Is there a formula that returns all hardscape components info, such as dimensions and certain component material info? I'm using it as dynamic text in a graphic legend.

I got some help to construct the current formula as shown in the screen capture below but it's not perfect as I'd be missing out if I'm having more than 6 components. I could obviously extend the rows to include many more component numbers but the resulting text box takes into account lines that returns no values so the text box increases unnecessarily in size with empty lines if I would do that.

@Nikolay Beshevliev, @Tom W., you've been very helpful earlier, any chance you know of a better formula? I believe there was some improvements in recent versions?

[Tom W.]

I don't know any way around this but that's not to say there isn't one. I believe there is a way to put a carriage return in a worksheet formula so there's a slight chance you could create the formula as a single line + incorporate carriage returns that are only activated if a value is returned...? That way the text block would only be as high as the number of components present in each case. Not something I'd feel qualified to embark on exploring so perhaps someone else could say whether this is feasible or not.

 

What I'd likely do is just display the Total first + the constituent parts underneath but I realise that's a bit of a cop-out 🙂

[Anders Blomberg]

Thanks as always for taking the time Tom! I'll adjust the arrangement for now but the it'd be cool if someone knows of a more elegant formula for this.

Edited February 4 by Anders Blomberg

[bob cleaver]

@anders great looking and informative legend !

[Pat Stanford]

We have fought this a couple of times in Data Tags. I know the formulas for Graphical Legends are different, but maybe something similar would work.

 

I have not used Graphical Legends enough to be good at them. If you want to post a sample I might be able to take a look later this week and make recommendations.

 

If you need more than a few components, the formula is going to be long and detailed. But more repetitive than complex. But I worry it might end up having to be longer than the maximum formula length.

 

 

[Anders Blomberg]

Here's a heavily reduced model if you'd like to have a look at it @Pat Stanford. I was really hoping this could all be formulated in a short text.

Formula model.vwx

[Jonk]

20 hours ago, bob cleaver said:

@anders great looking and informative legend !

@Anders Blomberg I second that. This is great. 

[Anders Blomberg]

@Jonk, @bob cleaver Feel free to grab the style from the model above if you'd like. These tables are absolutely fundamental to our work and I find it a little strange how much work it is to set up/figure out, but I suppose everyone doesn't have the same needs. It would just make sense to me if something similar was provided in the VW libraries though.

[Jonk]

34 minutes ago, Anders Blomberg said:

@Jonk, @bob cleaver Feel free to grab the style from the model above if you'd like. These tables are absolutely fundamental to our work and I find it a little strange how much work it is to set up/figure out, but I suppose everyone doesn't have the same needs. It would just make sense to me if something similar was provided in the VW libraries though.

@Anders Blomberg Amazing! I was already playing around with it. A lot of concise information in one spot. 

[bob cleaver]

The legend including the section almost eliminates the need for traditional details where a section through paving, great work ! 

[Pat Stanford]

@Anders Blomberg I think this is close to what you are looking for.  This uses only WS nomenclature to determine what to display made up of a formula for every line that includes a line feed at the end of the returned string. If there is no data, then an empty string and no line feed is returned. This allows you to then use the GL nomenclature for the Total line and have it be just one one below the last of the components.

 

The following does only the first four components. To extend this, copy from the starting # to and including the second #. Paste that at the end of the first line and change the three locations for the component number. The GL editor will automatically wrap the formula, but make sure you don't add any manual returns into the middle. The formula needs to be one long line.

 

#WS_IF(COMPONENTTHICKNESS(1)>0, CONCAT(TXT(COMPONENTTHICKNESS(1), 'Dim', 'Millimeters', 'Dec', '0.0', 'Units'), ' - ', MATPROPERTYBYNAME(COMPONENTMATERIAL(1), 'materialdescription'), CHAR('U+000A')), '')##WS_IF(COMPONENTTHICKNESS(2)>0, CONCAT(TXT(COMPONENTTHICKNESS(2), 'Dim', 'Millimeters', 'Dec', '0.0', 'Units'), ' - ', MATPROPERTYBYNAME(COMPONENTMATERIAL(2), 'materialdescription'), CHAR('U+000A')), '')##WS_IF(COMPONENTTHICKNESS(3)>0, CONCAT(TXT(COMPONENTTHICKNESS(3), 'Dim', 'Millimeters', 'Dec', '0.0', 'Units'), ' - ', MATPROPERTYBYNAME(COMPONENTMATERIAL(3), 'materialdescription'), CHAR('U+000A')), '')##WS_IF(COMPONENTTHICKNESS(4)>0, CONCAT(TXT(COMPONENTTHICKNESS(4), 'Dim', 'Millimeters', 'Dec', '0.0', 'Units'), ' - ', MATPROPERTYBYNAME(COMPONENTMATERIAL(4), 'materialdescription'), CHAR('U+000A')), '')#
TOTALT: #Hardscape#.#Slab Thick##mm_0_1#

 

In the screen shot, the *** and the values below that were your original formulas so I could still see if I was returning the correct values or not.

 

 

And now for how it does it.

 

#WS_IF(  Starts a worksheet IF Function

ComponentThickness(1)>0 is the criteria part of the IF function which determines if it should return the TRUE or FALSE part of the IF

CONCAT(...) is the TRUE part of the IF statement

'')# is the False part of the IF statement and closes the hash tags on the #WS_ function.

 

The Concat concatenates (combines into a single string) the value returned by the TXT function, the separator ' - ', the material description [MATPROPERTYBYNAME(COMPONENTMATERIAL(1), 'materialdescription'),], and the line feed if there is a value [CHAR('U+000A')]

TXT takes the ComponentThickness(1) value and formats is as a string. Specifically as a dimension (Dim), in Millimeters, in decimal format (Dec), using a single decimal point (0.0), and showing the unit mark (Units) This is kind of equivalent to what the #mm_0_1# section of the GL format does.

CHAR('U+000A') is a way to specify a character by a Unicode code point. The code point for a linefeed id 000A. The U+ just tells the interpreter to use it as a unicode value.

 

By putting multiple of the complete #WS_IF(...)# functions together on the "same line" (no manual line breaks, only automatic editor wrapping), you effectively make all of the components that are present into one long string with manual line feeds between components.

 

Since the GL thinks this is just one long line, you can then put the Total line on the next line of the formula and it will automatically adjust up and down depending on the number of components in the object.

 

Hope this helps.

[Tom W.]

49 minutes ago, Pat Stanford said:

CHAR('U+000A') is a way to specify a character by a Unicode code point. The code point for a linefeed id 000A. The U+ just tells the interpreter to use it as a unicode value.

 

Very neat!

[Anders Blomberg]

@Pat Stanford Wow! Will try this out as soon as possible!