Worksheet criteria question.

[hollister design Studio]

@Pat Stanford 

 

I have another worksheet criteria question.

 

I made a small Shrub Counter worksheet to have onscreen when I'm populating planting plans.

Pretty simple, just number and name.

 

I set the criteria to:

Field value/Category/=/Shrubs

Field value/Category/=/SHUB

I noticed that my Rhamus wasn't counting.

 

Looked it up and category is 'SHRB, native' -  Comma-Separated.

When I removed the 'native' category, the plant was counted, when I added it back in, it was ignored.

 

Here's a screen shot with 'native' still active and the plant not being counted:

What do I have set wrong ? 

 

 

 

 

Quick second question:

Do I really have to scroll down through all those entries to get to 'category'?

I tried typing to see if VW did first letter search or if I could just type the word in and it didn't work.

 

[Pat Stanford]

1.  = means equals.  If you type SHRUB it will only match SHRUB, not SHRUB1, SHRUBY, SHRUB anything else goes here. I don't have VW open. If there is a Contains instead of = option try that instead.

 

2. Yes, you have to scroll through that impossibly long list of things you are not interested in.

 

 

[Edgar RAMEL]

Hello @hollister design Studio ,

In addition to @Pat Stanford answer, here is the "tactic" I use on my side for database criteria.

The expressions "starts with" "ends with" "contains" etc are not (yet ...
I hope they will be one day, vote for them in the wish list) in the default selection criteria of the spreadsheet.

Also, I would use the asterix * and mathematical operators to return only the occurrences corresponding to "SHRUB+text".

As on this screenshot :

 

Also, in order to speed up the filling of the database fields, I edit the formula directly from the formula editor, it's faster.

 

Hoping to have helped you a bit.

 

Best regards.
Edgar

[Pat Stanford]

@Edgar RAMEL Thank you for reminding me of the "wildcard" options. I wasn't thinking about them last night.

 

So I THINK:

 

* means zero or more characters

? means exactly one character

 

Neither care what that character is:

 

So. =SHRUB?  would match SHRUB1, SHRUBA, SHRUB!, but not SHRUB, SHRUBAA or SHRUB!1

 

=SHRUB* would match all of the above.

 

=SHRUB*A. would match SHRUB12345A, SHRUBAA, SHRUB ZSFDA

 

Still don't have VW open to test.

[Edgar RAMEL]

Hello @Pat Stanford,

Yes, that's the general idea!

I hadn't tried the ?, but indeed it seems that these two wildcards work well. I would occasionally try the other known wildcards ...

 

On the other hand, when we use the "edit criterion" option that opens the appropriate dialog box, we have a problem with the result of the generated query.

 

 

It seems that this function "personally" manages the choices between "and" and "or" in the following criteria of database values ...

 

 

It inserts " | " or " & " as it pleases, without us being able to intervene at this level of the dialog box.

We need to correct this from the data formula editor.

 

Once this is done, it works fine.

 

What is unfortunate (I repeat myself, but the developers who read me may well have the tools to correct this), is that VectorWorks manages all these options natively with the fields from IFC data, as we can see below:

 

 

One day maybe ...

There you go.
Good day to everyone.
Edgar

 

 

Edited February 25, 2021 by Edgar RAMEL

[Pat Stanford]

You are correct that VW manages the settings of AND and OR. But there is some logic to what it does.

 

If you have multiple of the same criteria it applies an OR.

 

So. Layer IS ABC and Layer is XYZ become

Layer='ABC' | Layer='XYZ'

 

Multiple criteria of different types become AND

 

Layer='ABC' & Class='XYZ'

 

And the only way I know to change those is to go in and manually edit the formula string. Or use a script to write the formula string in the first place.

 

I don't think VW accepts any wildcards other than * and ? but I may be wrong.