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.
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.
In this example, we are just going to use the controls that are already on the Form and rename them.
="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.
These query statements are stored in Specification Properties, which are then passed to the CPQ DriveApp to be run.
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.
Edit the rule and replace it with the following:
@"INSERT INTO [driveApp-CPQ-Contacts-CustomFields] (ContactId,Value1,Value2) VALUES ('@(DWConstantRecordId)','@(SocialMediaProfileReturn)','@(NoOfLicensesReturn)')"
Edit the rule and replace it with the following:
@"UPDATE [driveApp-CPQ-Contacts-CustomFields] SET Value1 = '@(SocialMediaProfileReturn)', Value2 = '@(NoOfLicensesReturn)' WHERE ContactId = '@(DWConstantRecordId)'"
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.
The Specification Macro SetValues should now look like the following image:
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.
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:
For the connection string, you can use the following rule:
DriveAppGetConnectionStringByAlias(DWConstantDriveAppAlias)