Table Join IDO Filter Example
This example applies to the Row Authorizations form.
You have created some CloudSuite Industrial 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 CloudSuite Industrial 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 , 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 CloudSuite Industrial 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.