IDO Data Rules and IDO Cached Settings sample scenarios

Note: The exercises in this guide assume that the environment uses a Null value for the AccessAs setting.

In your environment, a different value can be used for the AccessAs setting.

Open the AccessAs form and see what the AccessAs setting is for your environment. For example, if in a cloud environment it likely will be ue_ as the AccessAs value.

Throughout the exercises, you will see a Caution note mentioning where you are required to add your AccessAs prefix as a part of an object name - form, ido, constraint, data elements, etc., while working through the exercises.

Setup

For all of the scenarios involved in this chapter we are going to use two IDOs and two forms that are required to be created.

Step 1: Creating the SQL Tables

In this process, you are required to create these four (4) tables:
  • DRExampleDepts_mst
  • DRExampleUserTasks_mst

Creating the DRExampleDepts_mst SQL table

  1. Open the SQL Tables form and add these details and columns:
    • Table Name: DRExampleDepts_mst
    • Schema: dbo
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • MultiSite: checked
    • Save the table.
  2. Paste these in the metadata for the columns: 
    dept	dbo	DRExampleDepts_mst	nvarchar 	nvarchar 	40			NO	0			0	9
description	dbo	DRExampleDepts_mst	nvarchar 	nvarchar 	60			YES	0			0	10

    The pasted code should look be similar to the image below.

  3. Click OK.
  4. Click Save.

Creating the DRExampleDepts_mst SQL table

  1. Open the SQL Tables form and specify these details and columns:
    • Table Name: DRExampleUserTasks_mst
    • Schema: dbo
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • MultiSite: checked
    • Save the table.
  2. Click the Columns button and add the two required columns by selecting Edit > Paste Rows Append.
  3. Paste these in the metadata for the columns: 
    Username	dbo	DRExampleUserTasks_mst	UsernameType 	nvarchar 	128			YES	0			0	2
TaskType 	dbo	DRExampleUserTasks_mst	nvarchar 	nvarchar 	40			NO	0			0	3
StartDate 	dbo	DRExampleUserTasks_mst	DateTimeType 	datetime 				YES	0			0	4
EndDate 	dbo	DRExampleUserTasks_mst	DateTimeType 	datetime 				YES	0			0	5
Status 	dbo	DRExampleUserTasks_mst	nvarchar 	nvarchar 	20			NO	0			0	6
TaskDesc 	dbo	DRExampleUserTasks_mst	DescriptionType 	nvarchar 	40			YES	0			0	14
Seq 	dbo	DRExampleUserTasks_mst	int	int				NO	1	1		0	15
dept 	dbo	DRExampleUserTasks_mst	nchar 	nchar 	40			NO	0			0	16

    The pasted code should be similar to the image below.

  4. Click New Constraint to add a Primary Key for the Seq.
  5. Specify these information:
    • Constraint Type: select Primary Key.
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • Constraint: specify PK_DRExampleUserTask_mst.
    • Clustered Index: select this option.
  6. Select the Seq column and click Add.
  7. Click Finish.

Creating the DRExampleUserReqs_mst SQL table

  1. Open the SQL Tables form and specify these details and columns:
    • Table Name: DRExampleUserReqs_mst
    • Schema: dbo
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • MultiSite: checked
    • Save the table.
  2. Click the Columns button and add the two required columns by selecting Edit > Paste Rows Append.
  3. Paste these in the metadata for the columns: 
    ReqNum 	dbo	DRExampleUserReqs_mst	int	int				NO	1	1		0	9
ReqDate 	dbo	DRExampleUserReqs_mst	DateType 	datetime 				NO	0			0	10
Item 	dbo	DRExampleUserReqs_mst	nvarchar 	nvarchar 				YES	0			0	11
UnitCost 	dbo	DRExampleUserReqs_mst	decimal 	decimal 				NO	0			0	12
Qty 	dbo	DRExampleUserReqs_mst	decimal 	decimal 				NO	0			0	13
ExtCost 	dbo	DRExampleUserReqs_mst	decimal 	decimal 				NO	0			0	14
Dept 	dbo	DRExampleUserReqs_mst	nvarchar 	nvarchar 				NO	0			0	15
Description 	dbo	DRExampleUserReqs_mst	nvarchar 	nvarchar 				YES	0			0	16
Approved 	dbo	DRExampleUserReqs_mst	tinyint 	tinyint 				NO	0			0	17
AppDate 	dbo	DRExampleUserReqs_mst	DateType 	datetime 				YES	0			0	18
AppUsername 	dbo	DRExampleUserReqs_mst	UsernameType 	nvarchar 				YES	0			0	19

    The pasted code should be similar to the image below.

  4. Click New Constraint to add a Primary Key for the Seq.
  5. Specify these information:
    • Constraint Type: select Primary Key.
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • Constraint: specify PK_DRExampleUserReqs_mst.
    • Clustered Index: select this option.
  6. Select the ReqNum column and click Add.
  7. Click Finish.

Creating the DRExampleParms_mst SQL table

  1. Open the SQL Tables form and specify these details and columns:
    • Table Name: DRExampleReqParms_mst
    • Schema: dbo
      Caution: If you are in cloud, your table name should have the ue_ as prefix.
    • MultiSite: checked
    • Save the table.
  2. Click the Columns button and add the two required columns by selecting Edit > Paste Rows Append.
  3. Paste these in the metadata for the columns: 
    MgtReqLimit 	dbo	DRExampleReqParms_mst	decimal 	decimal 	19	2	(0)	NO	0			0	11
ExecReqLimit 	dbo	DRExampleReqParms_mst	decimal 	decimal 	19	2	(0)	NO	0			0	12
VendorControlledMaintMatls 	dbo	DRExampleReqParms_mst	tinyint 	tinyint 			(0)	NO	0			0	13

    The pasted code should be similar to the image below.

  4. Click OK.
  5. Click Save.

Step 2: Creating Triggers

Caution: If your are in Cloud, your table and view names should have the ue_ as prefix on them when working through the Create Triggers section.
  1. Open the Trigger Management form.
  2. Select the Regenerate Specific Triggers option.
  3. Specify DRExampleDepts_mst in the Starting Table field.
  4. Specify DRExampleUserTasks_mst in the Ending Table field.
  5. Click Generate.
    Note: These should show that four (4) tables have been processed.

Step 3: Creating Views for the tables

Caution: If your are in Cloud, your table and view names should have the ue_ as prefix on them when working through the Create Views section.
  1. Open the Application Schema Tables Metadata form.
  2. Filter on DREx* and press F4.
    • For the DRExampleDepts_mst table, specify DRExampleDepts for the View Name and click Save.
    • For the DRExampleReqParms_mst table, specify DRExampleReqParms for the View Name and click Save.
    • For the DRExampleUserTasks_mst table, specify DRExampleUserTasks for the View Name and click Save.
  3. Open the View Management form.
  4. Select the Regenerate Specific Views option.
  5. Select DRExampleDepts_mst in the Start table field.
  6. Select DRExampleUserTasks_mst in the Ending table field.
  7. Click Generate.
    Note: A message prompt is shown that four (4) views were processed.
  8. Click OK.

Step 4: Creating the IDO project

  1. Open the IDO Projects form.
  2. Create a new IDO.
  3. Specify these information:
    Project Name
    Specify DRExample
    Description
    Specify IDO project for data rules examples.
    Access
    Use the default or use the value NULL.
  4. Save the IDO project.

Step 5: Creating the IDOs

From the IDO Projects form on the DRExample project, click the New IDO button.

Creating the DRExampleDepts IDO

  1. In the New IDO form, specify these information:
    Project Name
    Select DRExample.
    Primary Base Table
    Use the ViewName DRExamplesDepts.
    IDO Name
    Specify DRExampleDepts.
    Table Alias
    Specify dredep
  2. Click Next.
  3. Click Finish.
  4. Click the Properties button.
  5. Locate the Dept and Description properties
  6. Specify sDept for the Label String ID on the dept property.
  7. Click Save.
  8. Specify sDescription for the Label String ID on the Description property.
  9. Click Save.

Creating the DRExampleUserTasks IDO

  1. Click the New ID button.
  2. In the New IDO form, specify these information:
    Project Name
    Select DRExample.
    Primary Base Table
    Use the ViewName DRExampleUserTasks.
    IDO Name
    Specify DRExampleUserTasks.
    Table Alias
    Specify drexut
  3. Click Next.
  4. Click Finish.
  5. Click the Properties button.
  6. Locate the Dept and ensure that it is NOT Read Only in the bottom center.
  7. Click the New Property button.
  8. In the IDO Property Wizard, select the Unbound option.
  9. Click Next.
  10. Specify the Body in the Property Name.
  11. Click Finish.
  12. In the Data pane, specify these information:
    Data Type
    Specify String as a data type.
    Length
    Specify the length of 4000.
  13. In the Formatting pane, specify these information:
    Label String ID
    Specify sBody as the label string for the Body property.
    Read Only
    Select this option.
  14. Click Save.
  15. Repeat the steps to add the Subject property and specify these information:
    Property Name
    Specify Subject as the property name.
    Data Type
    Specify String as a data type.
    Length
    Specify the length of 400.
    Label String ID
    Specify sSubject as the label string for the Subject property.
    Read Only
    Select this option.
  16. Repeat the steps to add the ToEmailAddress property and specify these information:
    Property Name
    Specify ToEmailAddress as the property name.
    Data Type
    Specify String as a data type.
    Length
    Specify the length of 128.
    Label String ID
    Specify sDRExampleToEmailAddress as the label string for the ToEmailAddress property.
    Read Only
    Select this option.
  17. Select and update the Label String ID for these properties:
    Property Label String ID
    Username

    Specify sUsername.
    TaskType

    Specify sDRExampleTaskType.

    Select both Required and Read Only option.
    StartDate

    Specify sStartDate.

    Select the Read Only option.
    EndDate

    Specify sEndDate.

    Select the Read Only option.
    Status

    Specify sDRExampleStatus.

    Select both Required and Read Only option.
    TaskDesc

    Specify sDRExampleTaskDesc.

    Select the Read Only option.
    Seq

    Specify sSeq.
    dept

    Specify sDRExampleDept.

    Clear the Read Only option, if selected.
  18. Save all of the IDO Properties.

Creating the DRExampleUserReqs IDO

  1. In the New IDO form, specify these information:
    Project Name
    Select DRExample.
    Primary Base Table
    Use the ViewName DRExampleUserReqs.
    IDO Name
    Specify DRExampleUserReqs.
    Table Alias
    Specify drexur
  2. Click Next.
  3. Click Finish.
  4. Click the Properties button.
  5. Locate the Dept and ensure that it is NOT Read Only in the bottom center.
  6. Update these properties:
    Property Fields and Values
    ReqNum Label String ID: sDRExampleReqNum
    ReqDate Label String ID: sDRExampleReqDate

    Default Value: CURDATE()
    Item Label String ID: sDRExampleItem

    Select the Read Only option.
    UnitCost Label String ID: sDRExampleUnitCost.

    Justify: Right

    Display Decimal Position: 2

    Select the Read Only option.
    Qty Label String ID: sDRExampleOty

    Justify: Right

    Display Decimal Position: 2

    Select the Read Only option.
    ExtCost Label String ID: sDRExampleExtCost.

    Data Type: Decimal

    Length: 22

    Position: 8

    Justify: Right

    Display Decimal Position: 2

    Select the Read Only option.
    Dept Label String Property: sDRExampleDept
    Approved Label String ID: sDRExampleApproved

    Select the Read Only option.

    Ensure that the Required option is not selected.
    AppDate Label String ID: sDRExampleAppDate

    Select the Read Only option.

    Ensure that the Required option is not selected.
    AppUsername Label String ID: sDRExampleAppUsername

    Select the Read Only option.

    Ensure that the Required option is not selected.
  7. Save all of the IDO properties.

Creating the DRExampleReqParms IDO

  1. In the New IDO form, specify these information:
    Project Name
    Select DRExample.
    Primary Base Table
    Use the ViewName DRExampleReqParms.
    IDO Name
    Specify DRExampleReqParms.
    Table Alias
    Specify drerp
  2. Click Next.
  3. Click Finish.
  4. Click the Properties button.
  5. Locate the MgtReqLimit and ExecReqLimit properties
  6. Update these properties:
    Property Field and Value
    MgtReqLimit Label String ID: sDRExampleMgtReqLimit

    Justify: Right
    ExecReqLimit Label String ID: sDRExampleExecReqLimit

    Justify: Right
    VendorControlledMainMatls Label String ID: sDREaxmpleVendCntrlMaintMtls
  7. Save all of the IDO Properties.

Step 6: Check in the IDOs

On the IDOs form for the new IDOs, click the Check In button and click OK to get past the message about the Source Integration not being enabled.

Step 7: Creating the Extension Class Assembly for IDO: DRExampleUserTasks

See Appendix A: Data Rules Pre and Post-Save IDO Extension Class Source Code to do the Scenario Example 4 - Data Rules Pre and Post Save Methods example.
Note: You can skip this step if you are not doing the Scenario Example 8 - Data Rules Pre and Post Save Methods.

Step 8: Creating the Extension Class Assembly for IDO: DRExampleUserReqs

See Appendix B: Data Rules Method Based Cached Setting Extension Class to do the Scenario Example 8 - Create a Site-wide cache setting for third-party application (Vendor Controlled Maintenance Materials) to limit approval of requisitions using a C# method instead of a property based Cached Setting example.
Note: You can skip this step if you are not doing the Scenario Example 8 - Create a Site-wide cache setting for third-party application (Vendor Controlled Maintenance Materials) to limit approval of requisitions using a C# method instead of a property based Cached Setting.

Step 9: Import the forms and strings metadata

See Appendix C: Forms Metadata for instructions on importing the forms and strings metadata necessary for the Data Rules example scenarios.

Step 10: Clearing the IDO and Forms Cache

  1. Navigate to View > User Preferences.
  2. Ensure that the Unload IDO Metadata with Forms option is selected.
  3. Navigate to Form > Definitions.
  4. Click Unload Global Form Objects.
Note: This action clears the IDO and form caches.

Step 11: Creating the data for the sample scenarios

Creating the department data

  1. Open the DRExampleDepts form.
  2. Add five (5) departments based on these data: 
    100 Manufacturing
200 Accounting
300 Maintenance
400 Engineering
500 Executive
600 IT
  3. Click OK.
  4. Click Save.

Creating the group data

  1. Open the Groups form.
  2. Navigate to Edit > Paste Rows Append.
  3. Paste this data: 
    DREX_Acct	Data Rules Accounting Group			0
DREX_Eng	Data Rules Engineering Group			0
DREX_Exec	Data Rules Executive Group			0
DREX_Exec_ReqAppr 	Data Rules Exec Req			0
DREX_Mfg	Data Rules Manufacturing Group 			0
DREX_Mnt	Data Rules Maintenance Group 			0
DREX_IT 	Data Rules Information Technology 			0
DREX_IT_ReqAppr 	Data Rules IT Req Appr 			0
  4. Click OK.
  5. Click Save.

Creating and assigning users to Groups

  1. Use the Users form to add these users.
    User Name Details
    ITUser Super User: No

    User Description: IT Dept. User

    User Password: DataRules1
    Note: You are required to enter your password for a second time after specifying the Editing Permissions field.

    Editing Permissions: Site Developer

    Open the User Modules form and select Transactional in the Module Name field.

    Click new and add a second module and select Developer.
    ITMgr Super User: No

    User Description: IT Dept. Mgr

    User Password: DataRules1
    Note: You are required to enter your password for a second time after specifying the Editing Permissions field.

    Editing Permissions: Site Developer

    Open the User Modules form and select Transactional in the Module Name field.

    Click new and add a second module and select Developer.
    MntMgr Super User: No

    User Description: Maintenance Mgr

    User Password: DataRules1
    Note: You are required to enter your password for a second time after specifying the Editing Permissions field.

    Editing Permissions: Site Developer

    Open the User Modules form and select Transactional in the Module Name field.
    ExecUser Super User: No

    User Description: Executive User

    User Password: DataRules1
    Note: You are required to enter your password for a second time after specifying the Editing Permissions field.

    Editing Permissions: Site Developer

    Open the User Modules form and select Transactional in the Module Name field.
  2. Open the Groups form to assign users to groups.
  3. Use this filter DREX* and press F4 to show all groups with DREX in their name.
  4. Assign the users to these groups:
    Group User Name
    DREX_Exec ExecUser
    DREX_Exec_ReqAppr ExecUser
    DREX_IT ITUser and ITMgr
    DREX_IT_ReqAppr ITMgr
    DREX_Mnt MntMgr
  5. Provide access to the forms, select a group and click the Group Authorizations button.
  6. For the DREX_IT, DREX_IT_ReqqApp, and DREX_Mnt groups, select the Form as Object Type.
  7. Add these groups:
    Group Permission/Privilege
    DRExampleUserReqs Granted for all permissions.
    DRExampleUserTasks Granted for all permissions.
    DRExampleDepts Granted for all permissions.
    DRExampleReqParms Granted for Read and Execute privilege.
  8. For the DREX_Exec group, select the Form as Object Type.
  9. Add these groups:
    Group Permission/Privilege
    DRExampleUserReqs Granted for all permissions.
    DRExampleUserTasks Granted for all permissions.
    DRExampleDepts Granted for all permissions.
    DRExampleReqParms Granted for all permissions.
  10. Populate the sample req limits for managers or executives and a flag that will be used in a later exercise with Cached Settings.
    Note: If in a cloud environment, your form and IDOs may differ having a ue_ prefix.
  11. Open the DRExampleReqParms form.
  12. Create a new record:
    • Specify a $20000 value in the ExecReqLimit.
    • Specify a $1500 value in the MgtReqLimit.
  13. Select the Vendor Controlled Main. Mtls. checkbox.
  14. Save the form.