Gridview AND/OR

Here's what I am trying to accomplish:

If the pick sequence contains the letters P or Q or T or Z
and the qty on hand is greater than zero
and the qty on PO is zero
and the last receipt date was prior to Apr 1, 2011

Can anyone help?

Assuming you know how to filter on multiple fields using "and(,,)" ...

What you should do is create a Calculated Column called PickSeqMatch like this

FIND("P",{Picking Seq} ,1) +
FIND("Q",{Picking Seq} ,1) +
FIND("T",{Picking Seq} ,1) +
FIND("Z",{Picking Seq} ,1)

The result of this formula will be 0 if there are no matches, and it will be higher than 0 if there is at least one match.

Then include in the filter the expression

PickSeqMatch > 0

Does this help?

Steve

Hi,

We've been using the same formulas as Steve suggests, except we'll put each "Find" condition into a separate calculated column (and then hide them). And we would have the same PickSeqMatch calculated column, as a summation of the various find conditions of each calculated column, which as Steve notes easily indicates when there are matches.

We'll further use the row formats feature based upon the value of the each condition, to then colour the row so that you know what condition was validate for the "or" condition, in this situation a different colour for each picksequence match. So you can tell at a glance on your view what picking sequence is applicable for that item.

We do this in many of our exceptions analysis gridviews in Inventory, order Entry, Purchase Orders, and Job Costing. Works really well, and you get to be Martha Stewart for a day.

Best,
Brian

Hi Susan,

If you create each of your conditions as separately named filters (ie, Pick seq containing P, Pick seq containing Q, Qty on Hand greater than zero, etc), you can use the Filter Toolbox to combine any combination of filters without having to get the formula syntax of AND/OR (or AND NOT / OR NOT). From the View menu, select Filter Toolbax. In your case, select all of the Pick Seq filters with an OR status to group them together, and the other filters with an AND status. This way, you can be responsible for creating just simple filters, and let the toolbox do the combining into more complex combinations.

If this is a common filter configuration for your inquiry, the New button in the Filter Toolbox will create a new filter for you based on the seletions, with the AND/OR syntax of all the sub-filters created for you.

The ShowMeHow video on GridView filters has a brief demonstration towards the end on using the Filter Toolbox. This feature was added in version 9.0D, so the WatchMe video for GridView 9.0D describes the Filter Toolbox in some more detail, at the very beginning of the video.