Example: Table join IDO filter

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

This example applies to the Row Authorizations form.

Suppose you have created some application 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 application's 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.


Field	Setting	Notes
IDO	SLCos	The filter applies only to the SLCos IDO. (You would also create separate filters for the SLCoitems IDO and any other IDOs needed to view the customer information you want to include.)
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	custnum	The Customer field on the Customers form is bound to the SLCos.custnum property.
[Operator]	=
This Value	Current User Joined To	Use this setting to specify a table join.
This Table	CustLogin	This is your custom table with the mappings between the user ID and the customer number. The table must be in the application database.
Where This Column is the Username	custuser	This drop-down list will populate after you specify the table.
And This Column has the Value	custnum	This is the value that is compared to the SLCos.custnum property.

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 the application 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.