Jump to content
  • 14
JMR

Worksheet criteria - ability to remove one from middle of the stack

Question

20 answers to this question

Recommended Posts

  • 0

We also encounter this problem regularly. The only soultion I found so far is to change the criteria manually in the formula bar. It's also the only way to change an "&" criteria into a "|" citeria, or vice versa. Very annoying.

With more and more focus on the "I" in BIM, we need to have more flexable and userfriendly ways of sorting and searching our necessary information. My colleagues always look with befuddlement at me when I am explaining theses things.

 

  • Like 1

Share this post


Link to post
  • 0

Not the best solution, but probably workable for what you want:

 

Edit Criteria for the row in question and then click Cancel. The formula for the criteria should be displayed in the Formula Bar of the worksheet. It will look something like this:

=DATABASE((INSYMBOL & (INVIEWPORT & ALL & ((R IN ['__PDF']) | (R IN ['__PDF']))) & (L='Design Layer-2') & (T=SOLIDCSG)))

In this case I have gotten the PDF Record criteria added twice.

 

Click into the Forumula Bar and delete the criteria you don't want. In this case will delete from the vertical bar (which indicated a logical OR function) to the first close parenthesis after the '__PDF to get:

 

=DATABASE((ALL & (INVIEWPORT & INSYMBOL & ((R IN ['__PDF']))) & (L='Design Layer-2') & (T=SOLIDCSG)))

Then right click in the row header and choose Edit Criteria again to make sure you have edited it correctly.

 

The ampersand (&) characters above are a logical AND symbol. You would delete one of them and everything before or everything after to the next & in most cases.

 

Easier criteria editing are certainly a good wish.

Share this post


Link to post
  • 0

Hello,
It is indeed embarrassing to have to go through the table formula to delete a selection criterion in the tables.
On my side, I found the following trick:
I change the filter value to the same as the one at the end of the criteria list. And deletes the last redundant line by asking for fewer criteria.
I hope I've made myself clear. Attached are some screenshots in French.
In the same vein, since 2018 or 2019, it is no longer possible to simply change the sort order of the columns by dragging the sort number of the column headers... do you have any tracks for that?
And finally, it would be really nice to be able to use criteria filter operators on classes and layers with a syntax like "starts with..." or "contains" or "does not contain".
Without entering the formula bar and use the asterisk symbol it is not possible....
And this principle could be applied to all windows and dialogues where there are sorting or selections to be made.
Thanks to the readers (users or developers) for telling me if they have solutions for this.
Sincerely.
Edgar RAMEL
Landscop design


Translated with www.DeepL.com/Translator

Tableau 1.JPG

Tableau 2.JPG

Tableau 3.JPG

  • Like 2

Share this post


Link to post
  • 0

@Edgar RAMEL That is a great work around.  Move the last criteria up to replace the one you want to "delete" and then delete the last one!! I love out of the box thinking!.

 

I don't know of a way to change sort order without effectively deleting all of the sorts and restarting them.

 

But a quick look makes it appear to be a scriptable problem.  How many different sorts do you need at one time?

Share this post


Link to post
  • 0

Hello, Pat,
More than three in general.
On the script side, I'm not really good at it. 😓

Tableau 4.JPG

Share this post


Link to post
  • 0
On 6/3/2019 at 8:09 AM, Pat Stanford said:

Not the best solution, but probably workable for what you want:

 

Edit Criteria for the row in question and then click Cancel. The formula for the criteria should be displayed in the Formula Bar of the worksheet. It will look something like this:


=DATABASE((INSYMBOL & (INVIEWPORT & ALL & ((R IN ['__PDF']) | (R IN ['__PDF']))) & (L='Design Layer-2') & (T=SOLIDCSG)))

In this case I have gotten the PDF Record criteria added twice.

 

Click into the Forumula Bar and delete the criteria you don't want. In this case will delete from the vertical bar (which indicated a logical OR function) to the first close parenthesis after the '__PDF to get:

 


=DATABASE((ALL & (INVIEWPORT & INSYMBOL & ((R IN ['__PDF']))) & (L='Design Layer-2') & (T=SOLIDCSG)))

Then right click in the row header and choose Edit Criteria again to make sure you have edited it correctly.

 

The ampersand (&) characters above are a logical AND symbol. You would delete one of them and everything before or everything after to the next & in most cases.

 

Easier criteria editing are certainly a good wish.

 

Hi @Pat Stanford,

This just caught my eye, because I never knew we could control the logical connective between database criteria. The default is always '&', correct? Is the only way to create an "or" connection between criteria to overwrite the formula in the formula bar? Is there any sort of white paper or forum post listing ALL the ways to manipulate the logical connections in the formula bar? I would like make use of this hidden functionality!

 

Also, YES, PLEASE!...we should add the ability to delete a criteria row without the need for workaround!! It is unbelievable that this is not a rudimentary part of the edit criteria interface...

 

Thanks,
Matt

Share this post


Link to post
  • 0

My understanding is that different criteria (Layer or class or record.field, etc.) are all ANDed together.

 

Multiple instances of the same criteria (all the Layers for instance) are ORed together.

 

There is not way to specify any different setup other than to manually edit the criteria in the formula bar.

 

Yes, there should be a better criteria editing system in general.

Share this post


Link to post
  • 0
7 hours ago, Pat Stanford said:

My understanding is that different criteria (Layer or class or record.field, etc.) are all ANDed together.

 

Multiple instances of the same criteria (all the Layers for instance) are ORed together.

 

There is not way to specify any different setup other than to manually edit the criteria in the formula bar.

 

Yes, there should be a better criteria editing system in general.

 

Thanks @Pat Stanford,

Do you know where I can find a list and description of all the notations applicable to controlling the boolean logic in the formula bar? @Edgar RAMEL mentioned use of "*" in this thread, for example.

 

On 6/13/2019 at 8:26 AM, Edgar RAMEL said:

And finally, it would be really nice to be able to use criteria filter operators on classes and layers with a syntax like "starts with..." or "contains" or "does not contain".
Without entering the formula bar and use the asterisk symbol it is not possible....

 

Seems promising...I would love to try out some more advanced formulas. I'm sure I could have benefitted from this info. in the past!

 

Thanks,
matt

 

Share this post


Link to post
  • 0

I think there are only two logical functions that can be used.

 

&  AND  (Ampersand)

|  OR     (Vertical Bar, not capital I or lowercase L)

 

* (Star) is used to represent one or more wildcard characters.  I have not tried, but ? (question mark) may work for a single wildcard character.

Share this post


Link to post
  • 0

Hello everyone,

I confirm that the asterisk * works well to replace a string of characters.

I use it in the example table above.

Indeed a summary of the syntaxes posdible in the formulas would be good.

Share this post


Link to post
  • 0

Circling back to this thread, I don't understand how you're supposed to control the case by case use of '&' and '|' when setting criteria. In one worksheet, it insists the first 4 terms are "OR" connectives, and then the last 5 are "AND" connectives. I can overwrite this in the long-form entry bar, but the next time I edit criteria it reverts back to the original logic. What makes it chose one logical connective over the other when initially selecting criteria?

I must be missing something...

 

Matt

 

On 6/26/2019 at 12:41 PM, Pat Stanford said:

I think there are only two logical functions that can be used.

 

&  AND  (Ampersand)

|  OR     (Vertical Bar, not capital I or lowercase L)

 

* (Star) is used to represent one or more wildcard characters.  I have not tried, but ? (question mark) may work for a single wildcard character.

 

Share this post


Link to post
  • 0

If you manually edit the criteria it SHOULD tell you the criteria can't be edited by the criteria editor the next time you try to edit it.

 

By default, multiple instances of the SAME criteria (Object type is circle, Object Type is Arc, Object Type is polygon) will be OR'd together. DIFFERENT criteria (Object Type, Line Color, Class, etc.) will be AND'd together.

Share this post


Link to post
  • 0
4 hours ago, Pat Stanford said:

If you manually edit the criteria it SHOULD tell you the criteria can't be edited by the criteria editor the next time you try to edit it.

Nope...that's not what's happening in my file. The criteria seem to be on some kind of "automatically reorganize" setting. Not only does it reset the boolean logic, it also automatically stacks multiple instances of the same criteria on the list regardless of the order in which they were entered (which isn't a problem in and of itself, but it may help explain what's going on)

Share this post


Link to post
  • 0

my best suggestion in that case is to copy the criteria from the formula bar (without the initial equals sign) and paste it into a cell in the worksheet. Set it to a white font if you really don't want to see it. Then you can copy and paste it in the next time you need to edit the criteria. Remember to copy and paste out the revision when you are done.

 

The reorganizing to put all the similar criteria together is required to all the OR logic to work.

Share this post


Link to post
  • 0
Posted (edited)

@Pat Stanford, what would really be helpful (maybe this is a wishlist item), is the addition of ONE more option in the list of boolean pull downs that get selected during criteria editing. Currently, there are 7 filter options: {=, <>, <, <=, >=, none}

 

We could really use an additional 8th filter: ><  ("ONLY equals")

Which would be a way to override the automatic "and/or" determination issue I'm always wrestling with.

 

Currently, if I have a long list of options for a specific criteria, and I want to narrow it down to a single option, I'm often forced to make an equally long list of criteria excluding each of the other options in the set I do not want to be included. This is because I'm stuck with an OR connective between criteria, when I really need an AND connective. If there were an "only equals" filter (><), this would serve the same purpose. 

 

Matt

 

 

Screen Shot 2019-08-22 at 10.43.25 AM.png

Edited by mgries

Share this post


Link to post
  • 0

If VW does not do what you want it is always a Feature Request. That is the only way to get it on the radar.

 

But I don't think I understand your problem.  If you only want a single category, just enter it once.

 

If you use for Type >< Rectangle Type >< Circle you will get no returns because the item can not be only a rectangle and only a circle at the same time.

Share this post


Link to post
  • 0
22 hours ago, Pat Stanford said:

But I don't think I understand your problem.  If you only want a single category, just enter it once.

Ok, here's what I mean...

 

  • I've got a record that tracks all things related to Lot Coverage. One of the parameters in this record is "Type of Area". And one "Type of Area" is "Driveway or Parking"

 606861569_ScreenShot2019-08-23at10_18_29AM.thumb.png.18f0ec1d854165db0068d4b601031cd6.png

 

 

  • I've got a worksheet that breakdown all the Lot Coverage areas by category (aka: separate data base header row). One of the data base headers is "Impervious Vehicular Areas". To tabulate this category, I need to call up ONLY the "Driveway or Paving" value. Looking at the screenshot below, when I use the equal sign (=), it calls up ALL 53 "Type of Area" values. The correct answer should be 0, because there are no Driveway or Parking surfaces on this particular site.

949515246_ScreenShot2019-08-23at10_16_16AM.thumb.png.b961486add591a06da24597224a2f0b6.png

 

 

  • I can correct this by making 2 criteria entries relating to "Driveway or Parking". I add a "<=" row and then a ">=" row, essentially eliminating all values other than "Driveway or Parking". So this is why I suggested we needed an 8th boolean connector: "><", which is just a combination of the 2 separate connectors I'm currently being forced to use. 

1745739576_ScreenShot2019-08-23at10_23_47AM.thumb.png.87359b3d868f596ada224914d4e1b169.png

 

So, maybe I'm doing something wrong, but based on my experience, the criteria process needs to be told what values NOT to include in order to isolate individual values from a list of possibilities.

 

If you look further up the list of criteria in the screenshot above, you can see this same issue is apparent with my True/False (0/1) values. Instead of stating "=0" to call up only False statements, I actually end up being forced to use "<>1" to instead eliminate all the True statements from the list.

To quote Tone Loc, Something's not stirring the cool-aid Ace.

 

Thanks,

Matt

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×