Example: Multiple IDO filters

Note: This example was created specifically for SyteLine users, but customers using other Mongoose-based applications might benefit from the concepts manifested here.

Suppose each of your salespeople has an company login. They are allowed to see their own sales opportunities, but you also want them to see any opportunities that do not currently have a salesperson assigned, so that they can assign the opportunity to themselves.

Instead of creating a new mapping table, we can take advantage of the existing mapping in the SyteLine slsman_mst table, which has both a username column and a slsman column.

The filter on SLOpportunities requires two clauses.

First Clause


Field	Setting	Notes
IDO	SLOpportunities	The filter applies only to the SLOpportunities IDO. (You will be ORing another filter clause after this one.)
User		Leave blank so the filter applies to all users. You will use the table join to determine who the filter applies to.
Group		Leave blank so the filter applies to users in all groups.
Property	slsman	The Salesperson field on the Opportunities form is bound to the SLOpportunities.slsman property.
[Operator]	=
This Value	Current User Joined To	Use this setting to specify a table join.
This Table	slsman_mst	This is the existing slsma_mstn table that includes the username (login ID) and slsman (salesperson) mapping.
Where This Column is the Username	username	This drop-down list will populate after you specify the table.
And This Column has the Value	slsman	This is the value that is compared to the slsman property on the SLOpportunities IDO.

When you click Add This Clause, the pseuo-SQL query that displays looks like this:


Slsman = ANY (SELECT [slsman] FROM [slsman_mst] WHERE [slsman_mst].[username] = dbo.UserNameSp()))

Second Clause


Field	Setting	Notes
IDO	SLOpportunities	The filter applies only to the SLOpportunities IDO. (You will be "OR"ing this filter clause to the previous one.)
User		Leave blank so the filter applies to all users.
Group		Leave blank so the filter applies to users in all groups.
Property	slsman	The Salesperson field on the Opportunities form is bound to the SLOpportunities.slsman property.
[Operator]	=
This Value	Literal	Compare the property to the literal value in the adjoining field.
[Literal field value]		Leave this field blank, or specify null to indicate a null value. The null will match any opportunities where a salesperson is not defined.
OR Instead Of AND With Previous Clause	Selected	Select this box to indicate an OR

When you click Add This Clause, the pseudo-SQL query that displays looks like this:


Slsman = ANY (SELECT [slsman] FROM [slsman_mst] WHERE [slsman_mst].[username] = dbo.UserNameSp())) OR (Slsman = null)

After you activate and save the filter, when a salesperson logs into the application and tries to access the Opportunities IDO (either through a form or through a web-based call to the IDO), the filter looks up the login ID in the slsman_mst table and limits the information they can view to records with that customer number. However, the salesperson can also see any Opportunities records where no salesperson is assigned, because of the OR and the second clause above.