Example: Multiple IDO filters
Each of your salespeople has an ERP 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 they can assign the opportunity to themselves.
Instead of creating a new mapping table, we can take advantage of the existing mapping in the 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 , 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) | 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 value | (Selected) | Select this box to indicate an OR |
When you click , 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 ERP and tries to access the Opportunities IDO (either through a form or through a web-based call to the IDO), the filter will look up the login ID in the slsman_mst table and limit 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.