Custom IF WBs analysis

analysis.md

Workbooks that were referenced during analysis.(The state of the WBs when referred to, were dated between 20th Aug - 22nd Aug 2019)

1. UPR - Asia Shared Site - UPR - New Client Request Form_v2.0 (1)
2. Unilever - Crown NAM - New Client Request Form - UL Domestic v2.6
3. TWE - New Client Request Form+Custom IF with CP v16
4. Volvo - Crown Global - New Client Request Form_Volvo_v1.4
5. BP - Asia - New Client Request Form_British Petroleum Asia Pacific v1.6
6. HAVI - NAM Client Configuration Workbook v1.8

CDS Mapping

After having a look into the CDS mapping sheet of the above mentioned WBs, I came across three patterns

• [1] Generic pattern: Here we simply need to read the labels(on one row) and the values associated with the labels(present on the row next to the label's row). The values for each label will be associated columnwise repectively.
• [2] Condition with key value pair: This pattern is similar to the Generic Pattern with one exception related to the values associated with the Condition label. Here the values are present in the format as Key = Value. Here we need to break this string to have a distinct Key and Value and use them accordingly. There is one caveat to this case. There might be a situation where we might get Destination = <compound_location> as the value in condition. Then we need to find the sheet in the workbook which might have the components(sub locations) of the compound location mapping, by using which we can generate discrete queries for the same. Another alternative approach to this would be storing the compound location values in a constant and then we can use it whereever it is required.
• [3] Condition with key in the label: This pattern is similar to the Generic Pattern with an exception related to column with the label as Condition. Here the Key is present in the label itself in the format like this,

Condition:
Key =

So here we need to extract the Key from the label, which will be same for all the values in the column with the label as Condition

Custom IF

1. The custom IF tabs sheet names that I came across were:
   • Customized IF Form
   • Customized IF
   • Custom IF
   • Custom IF Form
2. Each custom IF field sheet consisted of the following major sections:
   • Initiation Form
   • Interact
   • Cost Estimate
   • Initiation
3. The Initiation Form major section has columns associated with it, with the following labels(or minor sections or column labels):
   • Section Name
   • Sub Section Name
   • Field Name
   • Tooltip
   • Field Type
   • Field Length
   • Value
   • Display in Pre-Hire (Pre Hire only)
   • Edit in Pre-Hire (Pre Hire only)
   • Mandatory in Pre-Hire (Pre Hire only)
   • Default value in Pre-Hire (Pre Hire only)
   • Display in IF
   • Edit
   • Edit in IF
   • Mandatory
   • Default Value
   • Update Enquiry (allow to edit in Approved IF)
   • Edit in Update Enquiry
   • Copy
   • Display in CE (present in HAVI - NAM Client Configuration Workbook v1.8)
   • Display in Email
   • Display in Email (Note: For Initiation Submission only)
   • Display in File Upload Template
   • Rule
   • Comments
   • Included in Email Notification?
   • backup field(this seems to be a placeholder field, need to ignore this)
4. The Interact major section has columns associated with it, with the following labels (or minor sections or column labels):
   • Tab
   • Section
   • Field Label
   • Field Type
   • Mandatory
5. The Cost Estimate major section has columns associated with it, with the following labels (present in TWE - New Client Request Form+Custom IF with CP v16) (or minor sections or column labels):
   • Display in CE
6. The Initiation major section has columns associated with it, with the following labels (present in TWE - New Client Request Form+Custom IF with CP v16) (or minor sections or column labels):
   • Display in IF
   • Edit
   • Mandatory
   • Default Value
   • Update Enquiry (allow to edit in Approved IF)
   • Copy
   • Rule
   • Tooltip
   • Comments
   • Included in email notification?
7. Clarification required regarding the interpretation of s̶t̶r̶i̶k̶e̶t̶h̶r̶o̶u̶g̶h̶ texts: Case 1: A single column value consists of a single strike through string Case 2: A single column value consists of a strike through string and normal string
8. There were some cases where these major sections were not mentioned (BP - Asia - New Client Request Form_British Petroleum Asia Pacific v1.6)
9. The minor sections(or column labels) will appear in the very next row after the major section's row.
10. Each of the minor section will hold the value for that particular column and an individual row of all the minor section values will define the complete details of a particular field on various forms.
11. There are cases where the all the minor sections(or column) values are empty except for the minor section with the label Section Name. In that case we need to omit that particular row from consideration. This may happen at regular intervals. As per approximate guess, the reason for placing it, is to specify the section name for the rows following it.
12. Clarification Required for the use of the values present in the Rule column.

Custom IF DD

1. The custom IF DD tabs sheet names that I came across were:

   • Customized IF Drop Down List
   • IF Drop Dwon
   • Custom IF Drop Down

2. There were discrete types of formats found in the above mentioned custom workbooks. Here we need to raise it with the client to agree upon a standardized format for mentioning the dropdown related data in the DD sheet of the workbook. The format should be clear enough to draw the boundaries between the Standard options and the client specific options, so that the client specific options could be read easily with high level of positive approximation.

3. After the format standards are met in the DD sheet then we can proceed with reading the client specific dropdown field options.

4. Reading the DD options in the case of custom workbooks, consists of two distinct formats:

   • [1]: Discrete DD options for a client specific field.
   • [2]: Dependent DD options on another client specific field options(this will coexist with the Discrete DD options for a field).

5. Discrete DD options for a client specific field:

   • Here we will have a label for a client specific field in a column and in the rows after in the same column we will have the options for that field.
   • Here the query generated for the options for a field need not be added in the z_main_generic_dynamic_options table. Instead all the options for a field needs to be stored in a serialized format in the field column with the label value in the z_main_webform_component table.
   • Example(taken from TWE - New Client Request Form+Custom IF with CP v16):

   Relocation Type	Is Cost Projection required?	etc..
   Domestic	Yes	etc..
   International	No	etc..

6. Dependent DD options on another client specific field options

   • Here basically, one client specific fields options are dependent on the singular option of another client specific field.
   • Here two types of patterns were observed:
     • Exclusively providing the parent field option value in the child dependent field options space, along with the dependent child field options.

       • Example (taken from TWE - New Client Request Form+Custom IF with CP v16):

         Relocation Type	Assignment Phase	etc..
         Domestic	When relocaion type = "International", show below options:	etc..
         International	Assignment Planning	etc..
         	Pre-Departure	etc..
         	On Assignment	etc..
         	Repatriation	etc..
         	Extension	etc..
         	Localisation	etc..
         	Repatriated	etc..
         	Localised	etc..
         	Terminated/Resigned	etc..
         	Relocated	etc..
         	When relocaion type = Domestic, show below options:	etc..
         	Pre-Departure	etc..
         	Relocated	etc..

       • Here firstly we need to aggregate the options of the dependent field, corresponding to all the parent field options. And then we need to remove the duplicates and then we need to serialize all the options and generate SQL file to store it in the field column with the label value in the z_main_webform_component table.

       • Then we need to generate queries to define the parent child dependency relationship in the table called z_main_webform_parent_children_mapping.

         • [1]: The parent client specific field options will be inserted in the name column along with its parent_id value as 0 and in the type column we need to insert the machine name of the field.
         • [2]: The dependent client specific field options will be inserted in the name column along with its parent_id as the value of the id of the parent client specific field option and in the type column we need to insert the machine name of the field.
         • [3]: Each option of the parent and child will be inserted as separate rows in the table.
     • Providing ancestral relationship between two or more client specific field options
       • Example (taken from TWE - New Client Request Form+Custom IF with CP v16)
       • In the above example as we can see, we have multi-level dependent field options.
       • Here firstly we need to aggregate the options of the dependent field, corresponding to all the parent field options. And then we need to remove the duplicates and then we need to serialize all the options and generate SQL queries to store it in the field column with the label value in the z_main_webform_component table.
       • Then we need to generate queries to define the parent child dependency relationship in the table called z_main_webform_parent_children_mapping.
         • [1]: The first parent client specific field options will be inserted in the name column along with its parent_id value as 0 and in the type column we need to insert the machine name of the field.
         • [2]: The second dependent client specific field options will be inserted in the name column along with its parent_id as the value of the id of the first parent client specific field option and in the type column we need to insert the machine name of the field.
         • [3]: The third dependent client specific field options will be inserted in the name column along with its parent_id as the value of the id of the second dependent client specific field option and in the type column we need to insert the machine name of the field.
         • [4]: This chain will go on till the last options column where the dependency within the adjacent columns exist.
         • [5]: Each option of the parent and child will be inserted as separate rows in the table.
         • [Challenge]:
           • Identifying the options dependency within the adjacent columns.
           • What will be the last column till which the dependency exist, because these dependent options column can exist anywhere in the middle/start/end/etc of the DD field options.

7. There might be cases in which the label of a DD client specific field is a compound label, which corresponds to two fields in the Custom IF sheet.

   • Example (taken from TWE - New Client Request Form+Custom IF with CP v16)
     • Here Home/Host Business Unit correspond to Home Business Unit and Host Business Unit fields in the custom IF sheet.

Custom IF Service Matrix

1. The custom IF service matrix tab sheet names that I came across were:
   • Service Matrix
   • Custom Service Martrix
   • Customized IF Service
   • Custom Service List
   • Custom Service Matrix
2. There might be cases, where a workbook may not have a service matrix sheet at all (like Unilever - Crown NAM - New Client Request Form - UL Domestic v2.6)
3. There are two types of service matrix sheet formats

   • [1] Conditions are present in the rules column

     • [CASE 1]
     • [CASE 2]

   • [2] Conditions are present in discrete columns in the workbook

     • [CASE 1]
     • [CASE 2]
     • [CASE 3]
4. Conditions are present in the rules column[CASE 1]
   • Firstly we need to read all the column labels, and we need to identify which column corresponds to what piece of information.
   • In this case we have
     • Service List (list services): This column specifies the name of the service.
     • Field Type: This column specifies the field type of the service.
     • Drop Down Values: This column specifies the DD values of a services, when the field type of a service is Drop Down
     • Rules (list any rules here): This column specifies that whether a particular service is conditionally related to any other service. In very approximate terms this column defines the conditions for the current service at hand.
     • Tooltip: This column specifies the tooltip values for the services.
     • Service Settings: This column specifies the conditional result for a service.
   • After this we need to read all the pieces of data from the relevant columns and we need to generate the service related SQL queries
   • There are mainly 3 tables relating to which we need to generate the SQL queries
     • z_main_webform_service_list: This table stores the complete information about the services individually.
     • z_main_generic_dynamic_services: This table stores the information of the services, as in how are the services related to a node id and company id etc, along with some other behavioural metrics.
     • z_main_generic_dynamic_services_control: This table stores the information about the service settings along with(if present) the conditional information(exibited by the Rule column) related to the service along with the information that to which node id and company id etc. the current service at hand is related to.
   • Generating SQL queries:
     • Firsly, we need to generate queries to store the service information in the z_main_webform_service_list table. The information majorly consists of the service name, service attributes(DD options values in a serialized format), tooltip and comments. The information to be stored in this table, mentioned previously are the major pieces of data that is available through the WB. All the pieces of information mentioned are subject to availability in the WB. Like in the current example WB we do not have the Comments column, so we need to have that column values as null in the generated SQL queries.
     • Secondly, we need to get the service ID of the individual services for which we have generated insert SQL queries in the previous step. After getting the service IDs of all the individual services from the previous step we need to generate the SQL queries to store the behavioural information of the services in relation to the node id and company id etc at hand.
     • Thirdly: we need to generate SQL queries for the z_main_generic_dynamic_services_control table. In this table we store the values present in the Service Settings column in this case. And in this table we also store the any conditional information(defined in the Rule column) related to a current service at hand. In this case do not have any rules so the current table's conditional field will have empty values. But the condition_result field column of the current table at hand will have the value of the Service Settings column for the current service at hand in its abbreviated form.
5. Conditions are present in the rules column[CASE 2]
   • Firstly we need to read all the column labels, and we need to identify which column corresponds to what piece of information.
   • In this case we have
     • Service Name: This column specifies the name of the service.
     • Field Type: This column specifies the field type of the service.
     • Drop Down Values: This column specifies the DD values of a services, when the field type of a service is Drop Down
     • Rule: This column specifies that whether a particular service is conditionally related to any other service. In very approximate terms this column defines the conditions for the current service at hand.
     • Tooltips: This column specifies the tooltip values for the services.
     • Client Settings: This column specifies the conditional result for a service.
   • After this we need to read all the pieces of data from the relevant columns and we need to generate the service related SQL queries
   • There are mainly 3 tables relating to which we need to generate the SQL queries
     • z_main_webform_service_list: This table stores the complete information about the services individually.
     • z_main_generic_dynamic_services: This table stores the information of the services, as in how are the services related to a node id and company id etc, along with some other behavioural metrics.
     • z_main_generic_dynamic_services_control: This table stores the information about the service settings along with(if present) the conditional information(exibited by the Rule column) related to the service along with the information that to which node id and company id etc. the current service at hand is related to.
   • Generating SQL queries:
     • Firsly, we need to generate queries to store the service information in the z_main_webform_service_list table. The information majorly consists of the service name, service attributes(DD options values in a serialized format), tooltip and comments. The information to be stored in this table, mentioned previously are the major pieces of data that is available through the WB. All the pieces of information mentioned are subject to availability in the WB. Like in the current example WB we do not have the Comments column, so we need to have that column values as null in the generated SQL queries.
     • Secondly, we need to get the service ID of the individual services for which we have generated insert SQL queries in the previous step. After getting the service IDs of all the individual services from the previous step we need to generate the SQL queries to store the behavioural information of the services in relation to the node id and company id etc at hand.
     • Thirdly: we need to generate SQL queries for the z_main_generic_dynamic_services_control table. In this table we store the values present in the Client Settings column in this case. And in this table we also store the any conditional information(defined in the Rule column) related to a current service at hand. In this case we can see that certain service rows are having some rules defined. For each service with a rule:
       • [1], we need to identify to which other service, the current service is dependent on. After that we need to get its service_id and field_key value. So the combined value of service_id and field_key for the parent service will serve as the value for the condition_formkey field column of the current table, for the current dependent service at hand. For example if service_id is 92 and field key is service for the parent service is, then the value will be N'(service)92' for the condition_formkey field column for the current dependent service at hand.
       • [2], as we identified the parent service and its service ID in the previous step, now we need to identify the field type of the parent field upon which our current service at hand is dependent upon. After determining the field type, we need to devise the value for condition_htmlkey field column of the current DB table at hand. The value will have its structure as edit-submitted-authorized-services-service-<[required]parent_service_id>-<[required]parent_service_field_type>-<checked[only if the field type is checkbox]>.
       • [3], now from the Rule column value for the current dependent service we need to extract the value of the parent service upon which the current dependent field is dependent upon. For Example: if rule value is Display if Moving Services (Air) volume is selected and check the box., then the parent service name is Moving Services (Air) and the value of the parent service upon which the current service is dependent upon is volume. The extracted value from the rule column for the current dependent field will serve as the value for the condition_value field column of the current table.
       • [4], lastly we need to translate the client setting column value for the current dependent service into its abbreviated form, and it will serve as the value for condition_result column value for the current table at hand.
6. Conditions are present in discrete columns in the workbook[CASE 1, 2]
   • In this kind of service matrix sheet, we have the service condition(s) specifying rows present before the actual service specifying rows. So Firstly, we need to identify how many rows are present before the actual service list starts. The total number of rows before the actual service list is number of conditions applicable to the following service list in aggregation. We also need to identify the column count till which the condition rows are extending.
   • Previously the conditions were specified as a value under the Rule column for an individual service. But here the conditions are itself provided in exclusive separate columns, following the columns defining a service details. And here the Rule column for the services are probably empty.
   • Secondly, we need to read all the service list column labels, and we need to identify which column corresponds to what piece of information, till we come across the row column, with empty values(it marks start of condtion specifying columns).
   • In this case we have
     • Service List: This column specifies the name of the service.
     • Field Type: This column specifies the field type of the service.
     • If field type = drop down list values: This column specifies the DD values of a services, when the field type of a service is Drop Down(There might be certain sheets where this column would not exist, because there may not be any service whose field type is as DD(select))
     • Rule(list any rules here): This column specifies that whether a particular service is conditionally related to any other service. In very approximate terms this column defines the conditions for the current service at hand. For the current cases at hand this column will probably have empty values.
     • Tooltips: This column specifies the tooltip values for the services.(this column may or may not be present)
     • Comments: This column specifies the comment values for the services.(this column may or may not be present)
     • [No Label columns]: These columns will specify the multiple(if more than one) condition result value for the individual service list.
   • After this we need to read all the pieces of data from the relevant columns and we need to generate the service related SQL queries
   • There are mainly 3 tables relating to which we need to generate the SQL queries
     • z_main_webform_service_list: This table stores the complete information about the services individually.
     • z_main_generic_dynamic_services: This table stores the information of the services, as in how are the services related to a node id and company id etc, along with some other behavioural metrics.
     • z_main_generic_dynamic_services_control: This table stores the information about the service settings along with(if present) the conditional information(exibited by the Rule column) related to the service along with the information that to which node id and company id etc. the current service at hand is related to.
   • Generating SQL queries:
     • Firsly, we need to generate queries to store the service information in the z_main_webform_service_list table. The information majorly consists of the service name, service attributes(DD options values in a serialized format), tooltip and comments. The information to be stored in this table, mentioned previously are the major pieces of data that is available through the WB. All the pieces of information mentioned are subject to availability in the WB. Like in the current example WB we do not have the Comments column, so we need to have that column values as null in the generated SQL queries.
     • Secondly, we need to get the service ID of the individual services for which we have generated insert SQL queries in the previous step. After getting the service IDs of all the individual services from the previous step we need to generate the SQL queries to store the behavioural information of the services in relation to the node id and company id etc at hand.
     • Thirdly: we need to generate SQL queries for the z_main_generic_dynamic_services_control table. In this table we store the values present in the Client Settings column in this case. And in this table we also store the any conditional information(defined in the conditional columns) related to a current service at hand. In this case we can see that certain service rows are having some conditions defined. For each service:
       • [1], we need to identify the conditional rows. Each conditional row, corresponds to a field. Here we will have the field label name, through which we need to fetch the field_key and the html_key for all the conditional field labels. Then the following columns after the conditional field's label name, we have the values for all the conditional fields. For example in [CASE 1]: the field_key for Relocation Type, Assignment Phase, Assignment Policy are relocation_type, assignment_phase and assignment_policy repectively and the html_key values are edit-submitted-relocation-details-relocation-type, webform-component-assignment-details--assignment-phase, webform-component-assignment-details--assignment-policy respectively.
       • [2], then we need to devise the value for condition_formkey field column for the current table at hand. In order to do that we need to aggregate the conditonal fields columns field keys. For example for [CASE 1]: value for condition_formkey will be relocation_type+assignment_phase+assignment_policy.
       • [3], then we need to devise the value for condition_htmlkey field column for the current table at hand. In order to do that we need to aggregate the conditonal fields columns html keys. For example for [CASE 1]: value for condition_htmlkey will be edit-submitted-relocation-details-relocation-type+webform-component-assignment-details--assignment-phase+webform-component-assignment-details--assignment-policy.
       • [4], then we need to devise the value for condition_value field column for the current table at hand. In order to that, we firsly need to understand the how the relationship between the conditional rows values work.

         • 1. Lets consider the [CASE 1] example, where the value of Relocation Type is International(1st set 1 value), the values for Assignment Phase is Assignment Planning(1st set 1 value) and Pre-Departure(2nd set 1 value) and the values for Assignment Policy is STA, LTA, Local Plus, Permanant Transfer, International Hire, Harvest Exchange(1st set 6 values) and STA, LTA, Local Plus, Permanant Transfer, International Hire(2nd set 5 values)

         • 2. The relationship between the conditional field's values is set wise. Like in the current considered example, the 1st set of value for Relocation Type corresponds to 2 sets of values for Assignment Type and then 1st set of Assignment type will correspond to 1st set of values for Assignment Policy and so on...

         • 3. so the value for field column condition_value fot the conditional rows will be the aggregation of the values set wise for the immediate parent from bottom to top. When considering the current example the values for condition_value will be

           • International+Assignment Planning+STA
           • International+Assignment Planning+LTA
           • International+Assignment Planning+Local Plus
           • International+Assignment Planning+Permanant Transfer
           • International+Assignment Planning+International Hire
           • International+Assignment Planning+Harvest Exchange
           • International+Pre-Departure+STA
           • International+Pre-Departure+LTA
           • International+Pre-Departure+Local Plus
           • International+Pre-Departure+Permanant Transfer
           • International+Pre-Departure+International Hire

         • 4. so the number of values for condition_result field column values for the current table for a single service will be equal to the total number of values in all the sets of the last conditional row(as it resides at the bottom of the conditional rows hierarchy)

       • [5], lastly as we mentioned in these cases we will find columns with no label which will consist of all the values for the condition_result field column of the current table at hand for a single service. We need to use the abbreviated form of the values in these column for the current table's condition_result column.