Background

Problem: Different dealers use different fields to store the same information in their CDK DMS. Some dealers use custom fields to store information we need (such as Sale Type or Gross values). Previously (on the CDK API 1.0), we had no way to ingest their data through the CDK API and these dealers were forced to not use the API (and provide a manual file instead). We need the API to be more flexible for a variety of dealers.

Solution:  we will offer Custom Field Mappings, Custom Exceptions, and Universal Exceptions

Benefit: More dealers will be able to use the CDK API, resulting in Clarivoy receiving their sales in a timely and accurate manner (vs. collecting a file).

Types of Custom Mappings

1. Universal Mappings
   objective: for mapping CDK field → cdk_raw field for all properties
   example: FIDealType [original CDK data] → new_or_used [in cdk_raw]
   
   
   1. Custom Mappings
      objective: for mapping CDK field → cdk_raw field for specific property IDs
      example: For Property 99997, CustomFrontGrossFieldName [original CDK data] → front_gross [in cdk_raw]
      
       
   2. Universal Value Exceptions
      objective: for inserting hardcoded values → cdk_raw field based on a condition met in the original CDK data
      example: IF FIDealType = Wholesale [original CDK data], THEN set [in cdk_raw] sale_type=Wholesale
      1. CDK_API_VALUE field should accept wildcard character of * (asterisk) anywhere in the string. *=any number of characters.
         Examples:
         1. WHOLESALE* would mean “starts with WHOLESALE”
         2. *WHOLESALE would mean “ends with WHOLESALE”
         3. *WHOLESALE* would mean “contains WHOLESALE”
         4. M*SALE would match values such as “MISC SALE” “MISCELLANEOUS SALE” “M SALE”
      2. NULL values - CDK_API_VALUE field should accept values of <NULL>|<null>|null|NULL all equating to null
         
          
   3. Custom Value Exceptions
      objective: for inserting hardcoded values → cdk_raw field based on a condition met in the original CDK data - for specific property IDs
      example: For Property 99998, IF DealType = 3 [original CDK data], THEN set [in cdk_raw] sale_type=Wholesale
      1. CDK_API_VALUE field should accept wildcard character of * (asterisk) anywhere in the string. *=any number of characters.
         Examples:
         1. WHOLESALE* would mean “starts with WHOLESALE”
         2. *WHOLESALE would mean “ends with WHOLESALE”
         3. *WHOLESALE* would mean “contains WHOLESALE”
         4. M*SALE would match values such as “MISC SALE” “MISCELLANEOUS SALE” “M SALE”
      2. NULL values - CDK_API_VALUE field should accept values of <NULL>|<null>|null|NULL all equating to null
         
          
   4. Universal Field Exceptions
      objective: for mapping specific original CDK field values → cdk_raw fields based on a condition met in the original CDK data
      example: IF FrontEndGrossProfit = <null> [original CDK data], THEN map FrontGross [original cdk field] → front_gross [cdk_raw]
      1. CONDITION_VALUE field should accept wildcard character of * (asterisk) anywhere in the string. *=any number of characters.
         Examples:
         1. WHOLESALE* would mean “starts with WHOLESALE”
         2. *WHOLESALE would mean “ends with WHOLESALE”
         3. *WHOLESALE* would mean “contains WHOLESALE”
         4. M*SALE would match values such as “MISC SALE” “MISCELLANEOUS SALE” “M SALE”
      2. CUSTOM_VALUE field will accept <NULL>|<null>|null|NULL
         
          
   5. Custom Field Exceptions
      objective: for mapping specific original CDK field values → cdk_raw fields based on a condition met in the original CDK data - for specific property IDs
      example: For Property 99998, IF AccountingDate = <null> [original CDK data], THEN map ContractDate [original cdk field] → sales_date [cdk_raw]
      1. CONDITION_VALUE field should accept wildcard character of * (asterisk) anywhere in the string. *=any number of characters.
         Examples:
         1. WHOLESALE* would mean “starts with WHOLESALE”
         2. *WHOLESALE would mean “ends with WHOLESALE”
         3. *WHOLESALE* would mean “contains WHOLESALE”
         4. M*SALE would match values such as “MISC SALE” “MISCELLANEOUS SALE” “M SALE”

CUSTOM_VALUE field will accept <NULL>|<null>|null|NULL

Custom Mapping Process

This explains how to add custom mapping for specific fields or custom value exceptions - for a specific property.

1. Determine the fields you want to map.  Fields are case sensitive and must be in this format: object.attribute 
   1. Check Sales Dashboard.  
      1. Query by Property ID.
      2. In the CDK Raw tile, export the data to Excel.
      3. All fields sent by the API are in the Original JSON column.  Some of the common fields have been added to this table as columns of their own for easy reference.
   2. If needed, see list of available fields from the API here: Field Mapping: CDK Drive Get FI Sales API
2. Open the Google Sheet CDK Mappings.
3. Add row(s) to one of the following sheets:
   1. Custom Mappings
   2. Custom Value Exceptions
   3. Custom Field Exceptions

Do not modify the Universal Mappings or Exceptions sheets without first consulting Ops Director as this will affect ALL dealers on the CDK API.  The downstream impact of any changes needs to be considered/studied before modifying mappings or exceptions for all dealers.