This week will will talk about Dimension Group Filtering for Advanced Analysis.There are two different operators you can use with creating your dimension group filters, the And Operator and the Or Operator. This week we will talk about the And Operator. The And operator is used in cases where you want the records in the group to satisfy two or more conditions at the same time. Some example of ‘and’ statements would be as follows:
- Locations that are Active AND have no parent.
- Employees that are in the New York Location AND in the General Admin Department.
- Employees that are active AND are paid in USD.
- Employees that are part time AND are in the San Francisco Location.
- Employees that are in the New York Location AND have no SSN# specified.
- Vendors who are active AND have ACH Enabled.
- Vendors who have a Type ID of Contractor AND are active AND are located in the USA.
You may have noticed that the last example contained two ANDs which means it has 3 conditions to satisfy. ANDs can be linked together like this to do multiple conditions but remember that all the conditions need to be satisfied in order to get a record returned. Using Item 7 above as an example, if you don’t have any Vendors of Type ID Contractor, your group will be empty. Similarly if all of your Vendors of Type Id Contractor are located in Canada, you will also have an empty group. AND statements are powerful but can also trip you up!
In order to make our Vendor Group we will go to Accounts Payable > Open Setup > Vendor Groups > Add. You will need to complete the header portion of the new group ensuring you have information in the Vendor group ID field and the Name field. Those two fields are mandatory as indicated by the red bar. You will want to ensure that the Group Type is All Members so that we can set our filter.
Scroll down to the filter portion of the screen and set the necessary criteria. For some people it’s helpful to work the statement out on paper before entering it on the screen. As we are doing Item 7 above we will have our first line check if the Vendor Type ID is equal to Contractor, our second line confirm that the Status is Active and our third line will check that the Country Code equals United States. It’s important to confirm that the Condition type is set to ‘All (AND)’ before clicking Save.
In order to verify the contents of your group are correct you can go to the Vendor Group List and click ‘Members’ to the right of your new Vendor Group. This will display a new list view showing all the records that satisfy your filter.