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.