DriveWorks Pro 20

CPQ Custom Fields

CPQ Custom Fields

This Template allows the addition of Custom Fields to store alongside a record.

Record Types, which allow Custom Fields to be added, are Accounts, Contacts, Items and Orders

Each of the Record Types have a specific SQL Table for Custom Fields.

These Tables are named driveApp-CPQ-<Type>-CustomFields; where <Type> is either Accounts, Contacts, Items or Orders.

Each record will have its own row in the corresponding Custom Fields table.

For example: if you create a new Account and add Custom Fields, a new data row will be created in both the Accounts table, and the Accounts-CustomFields table.

These data rows will then be linked.

In the Template there are three Specification Properties.

These are used by the CPQ DriveApp for interacting with the SQL Table for Custom Fields.

Each Property is used for either Inserting a new Custom Field row, updating an existing Custom Field row or Selecting an existing Custom Field row.

These are the actions the CPQ DriveApp needs to perform on Custom Fields.

This example tutorial shows how to add a new Custom Field Project to store extra values alongside a record.

The process for setting up a Project for either Accounts, Contacts, Items or Orders is identical.

For the purpose of this example, we will use Contacts.

Creating a new Custom Fields Project

1. Create a new Project from the CPQ Custom Fields template.
2. Open the Project in DriveWorks Administrator.

   The first step is to add the necessary controls to the form. Example controls are provided. These can either be modified or deleted to add new ones.

   We recommended the styling properties of the example controls are copied to any new controls.

   In this example, we are just going to use the controls that are already on the Form and rename them.

3. Change the Name and Caption Properties of the Value1 Control to be 'SocialMediaProfile' and 'Social Media Profile' respectively.
4. Change the Name and Caption Properties of the Value2 Control to be 'NoOfLicenses' and 'No. of Licenses' respectively.
5. Change the Text Property of the CustomFieldsHeading Control to be

   ="Extra Information"

Your Form should now look like this:

Now that the Form has been completed, three pieces of information are to be passed to the CPQ DriveApp Order.

• An Insert Statement – to insert a new Custom Field row when a new Contact is added to CPQ
• An Update Statement – to update the correct Custom Field row when the custom field values are updated
• A Select Statement – to select the correct row when the Contact is first edited.

These query statements are stored in Specification Properties, which are then passed to the CPQ DriveApp to be run.

Change the Specification Property rules

Some sample rules are already in place inside the Specification Properties.

We need to edit these rules slightly to get them to work for the driveApp-CPQ-Contacts-CustomFields Table.

1. Navigate to Specification Properties.
2. Edit the CustomFieldsInsert Property.

   Edit the rule and replace it with the following:

   @"INSERT INTO [driveApp-CPQ-Contacts-CustomFields] (ContactId,Value1,Value2) 
   VALUES ('@(DWConstantRecordId)','@(SocialMediaProfileReturn)','@(NoOfLicensesReturn)')" 

3. Edit the CustomFieldsUpdate Property.

   Edit the rule and replace it with the following:

   @"UPDATE [driveApp-CPQ-Contacts-CustomFields]
   SET 
   	Value1 = '@(SocialMediaProfileReturn)',
   	Value2 = '@(NoOfLicensesReturn)'
   WHERE ContactId = '@(DWConstantRecordId)'"

4. Edit the CustomFieldsSelect Property.

   Edit the rule and replace it with the following:

   @"SELECT * FROM [driveApp-CPQ-Contacts-CustomFields] WHERE ContactId = '@(DWConstantRecordId)'"
           

The Specification Properties should now look like the following image:

You may have noticed that the names for the table columns in SQL were Value1 and Value2.

At this point, you may wish to go into your DriveApp SQL Database, locate the driveApp-PQ-Contacts-CustomFields table and edit the column names.

For the purpose of this example, we will leave them as Value1 and Value2.

Change the Specification Macro Properties

1. Navigate to the Specification Macros.
2. Edit the SetValues Specification Macro.
3. Change the Control Names of these two Drive Control Value Tasks to match the names of the controls. In our example, this would be 'SocialMediaProfile' and 'NoOfLicenses'.

The Specification Macro SetValues should now look like the following image:

Instruct the CPQ DriveApp to use the new Project

1. In the CPQ DriveApp, click the Settings icon and select CPQ Settings.
2. Select the Custom Fields tab and check the check box alongside Custom Order Fields Project Required.
3. Select the newly created Project from the Combo Box that appears.
4. Click Save to save the changes.

The Add Custom Fields option will now be available when a Contact is being added or edited.

Click this button and the form edited in the sections above will appear as a dialog.

Filling in the fields and saving the dialog will store the Custom fields in the DriveApp-CPQ-Contacts-CustomFields Database Table.

Remember, the process for creating Custom Field for Accounts, Items and Orders is the same as the one documented above, apart from using ‘Orders’ and ‘Order’ etc. in the three Specifications Properties.

Accessing Custom Field Values

Values that are stored as Custom Fields can be accessed from the other Templates that can be used with the CPQ DriveApp.

There are 4 Stored Procedures that are available to use within the CPQ DriveApp Database:

• driveApp-CPQ-SP-GetAccountCustomFields
• driveApp-CPQ-SP-GetContactCustomFields
• driveApp-CPQ-SP-GetOrderCustomFields
• driveApp-CPQ-SP-GetItemCustomFields

For the connection string, you can use the following rule:

DriveAppGetConnectionStringByAlias(DWConstantDriveAppAlias)