Examples: IDO Filters

These examples apply to the Row Authorizations form.

Simple Example: Username Property = Current User

You want to allow all users to see their own user information on the Users form (description, e-mail address, group memberships, etc.) but not any other user's information. To do this, you would set the fields as follows:

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

(Username = dbo.UserNameSp())

After you activate and save the filter, any user who logs in (and who has the proper authorizations for the form) can open the Users form but will see only the record that matches their user ID.

Table Join Example

You have created some SyteLine logins for your customers, and you want to restrict the customers so they can see only their orders and no other customers' orders.

First, you need to create a custom SQL table that connects the SyteLine user logins to customer numbers. Name the table CustLogin, and include two columns: custuser and custnum. Populate the table with your customer user logins and their associated customer numbers.

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

(CustNum = ANY (SELECT [cust_num] FROM [CustLogin] WHERE [CustLogin].[username] = dbo.UserNameSp()))

After you activate and save the filter, when a customer user logs into SyteLine and tries to access the SLCos IDO (either through a form or through a web-based call to the IDO), the filter will look up their user ID in the CustLogin table and, if it finds a matching custnum, it will limit the information they can view to records with that customer number.

Multiple Filters Example

Each of your salespeople has a SyteLine 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:

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:

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 SyteLine 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.

Group Example

Your customer BigCycle (customer number 89765) has 20 employees that are allowed to log into SyteLine through a web portal to check the status of their orders. Instead of setting up a separate IDO filter for each login, you can set up a group called BigCycleLogins and set up all 20 users as members of that group. Then you can create a filter for the group. To do this, you would set the fields as follows:

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

(CustNum = "  89765")
	

After you activate and save the filter, any user in the BigCycleLogins group who logs into SyteLine and requests data from the SLCos IDO will only see records where CustNum = 97765.

If you have 10 different customers who have several users each with SyteLine login access, you could create a group for each customer, and then create 10 "group" IDO filters on the SLCOs IDO (one for each customer group).

How Filters Are Combined

The following information applies to complex situations, for example, when IDOs are inheriting from each other and each level has its own filters defined. An IDO that extends another IDO inherits all its filters, unless the IDO is using extend-and-replace.

Only filters marked Active will be used for all of the following.

The IDO filter clause is built by ANDing together all or any of these categories:

1. The general IDO filter, where both User and Group are blank
2. The user's IDO filter, where User equals the current login
3. The user's group's IDO filters, where Group equals any of the the current user's group memberships. (The group filters are ORed together, since the user should be able to see the rows visible to any of the groups the user belongs to.)

The clauses for categories 1 and 2 consist of all filters in that category ANDed together; the clause for category 3 consists of the clauses for every group the user is a member of ORed together; the group clauses themselves are all the filters for that group ANDed together.

In other words, a user who is a member of several groups can see all of the data these different groups are allowed to see, but it may be restricted by the user's personal filters (and possibly the general filters, but usually the general filters would not be more restrictive than any of the group filters, because that would defeat their purpose). As an example, an IDO has these active filters:

Everyone: E1, E2;

User: U1, U2;

Group1: G1-1, G1-2;

Group2: G2-1, G2-2;

The user who wants to access the IDO is a member of both Group1 and Group2. Then the constructed filter is set up like this:



(E1 AND E2) AND (U1 AND U2) AND ((G1-1 AND G1-2) OR (G2-1 AND G2-2))

Related Topics

Using IDO Filters to Limit User Access