Enable additional custom fields

[harrisward]

We only get data from cases and leads for custom tables. We need to pull in all custom fields for the modules that we extract data from

[samus-aran]

Hey Harris,

What are the steps for this? I can't figure out the config to insert these new custom tables unless its added to the populateCustomFieldsMapping.ktr

`

cases_cstm cases D_CASES CASE_ID

  <line>
    <item>leads_cstm</item>
    <item>leads</item>
    <item>D_LEADS</item>
    <item>LEAD_ID</item>
  </line>
</data>

` I added in accounts_cstm, accounts, D_ACCOUNTS and ACCOUNT_ID and it worked, the custom fields were mapped into the CUSTOM_FIELDS table.

2019/12/17 13:18:43 - - -------------------------------------------------- 2019/12/17 13:18:43 - - Populating Custom SuiteCRM Tables 2019/12/17 13:18:43 - - -------------------------------------------------- 2019/12/17 13:18:43 - populateCustomTables - Starting entry [Populate Custom Fields Mapping] 2019/12/17 13:18:43 - Populate Custom Fields Mapping - Loading transformation from XML file [file:///var/www/html/suitecrm-data-integration/suitecrm-data-integration-server/solution/DWH/DIMENSIONS/populateCustomFieldsMapping.ktr] 2019/12/17 13:18:43 - Populate Custom Fields Mapping - Using run configuration [Pentaho local] 2019/12/17 13:18:43 - Populate Custom Fields Mapping - Using legacy execution engine 2019/12/17 13:18:43 - populateCustomFieldsMapping - Dispatching started for transformation [populateCustomFieldsMapping] 2019/12/17 13:18:43 - Custom Table Fields.0 - Finished processing (I=0, O=0, R=0, W=3, U=0, E=0) 2019/12/17 13:18:43 - Get SuiteCRM Database Name.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0) 2019/12/17 13:18:43 - Filter Property.0 - Finished processing (I=0, O=0, R=2, W=1, U=0, E=0) 2019/12/17 13:18:43 - Generate Target Field.0 - Optimization level set to 9. 2019/12/17 13:18:43 - Join.0 - Finished processing (I=0, O=0, R=4, W=3, U=0, E=0) 2019/12/17 13:18:43 - Get Custom Table Fields.0 - Finished processing (I=0, O=0, R=3, W=87, U=0, E=0) 2019/12/17 13:18:43 - Filter Blacklist Fields.0 - Finished processing (I=0, O=0, R=87, W=84, U=0, E=0) 2019/12/17 13:18:43 - Modify Fields.0 - Finished processing (I=0, O=0, R=84, W=84, U=0, E=0) 2019/12/17 13:18:43 - Has Length?.0 - Finished processing (I=0, O=0, R=84, W=84, U=0, E=0) 2019/12/17 13:18:43 - Generate Target Field.0 - Finished processing (I=0, O=0, R=84, W=84, U=0, E=0) 2019/12/17 13:18:44 - Insert / Update CUSTOM_FIELDS.0 - Finished processing (I=84, O=29, R=84, W=84, U=0, E=0) 2019/12/17 13:18:44 - populateCustomTables - Starting entry [Fields Enabled?] 2019/12/17 13:18:44 - populateCustomTables - Starting entry [Success] 2019/12/17 13:18:44 - populateCustomTables - Finished job entry [Success] (result=[true]) 2019/12/17 13:18:44 - populateCustomTables - Finished job entry [Fields Enabled?] (result=[true]) 2019/12/17 13:18:44 - populateCustomTables - Finished job entry [Populate Custom Fields Mapping] (result=[true]) 2019/12/17 13:18:44 - populateCustomTables - Finished job entry [Populating Custom Tables] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Starting entry [Success] 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [Success] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [Populate Custom Tables] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [populateCustomFacts] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [populateCustomDimensions] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [populateFacts] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [populateDimensions] (result=[true]) 2019/12/17 13:18:44 - populateDataWarehouse - Finished job entry [Initiate Data Warehouse] (result=[true]) 2019/12/17 13:18:44 - suitecrm - Starting entry [PostExecution]

However, I see that the fields are not being generated into the expected TARGET_TABLE in order to pull the data from the SuiteCRM master database. Is there a missing job/reference somewhere?

[samus-aran]

Oh nevermind, found the trigger that FIELD_ENABLED needs to be set to 1 and then re-run the ./run-suitecrm-data-integration.sh

Ok, so running that it seems some things worked. I can see cases,leads and accounts custom fields appearing in the Dimensions tables.

However, I couldn't see all the expected data being pulled through for example, there is a custom field in accounts called: assigned_team_id_c And this becomes ASSIGNED_TEAM_ID But I can't see the data for a particular record that should be inserted into that column.

2019/12/17 13:34:57 - Write to log.0 - 2019/12/17 13:34:57 - Write to log.0 - ------------> Linenr 1------------------------------ 2019/12/17 13:34:57 - Write to log.0 - CUSTOM_TABLE_NAME = accounts 2019/12/17 13:34:57 - Write to log.0 - 2019/12/17 13:34:57 - Write to log.0 - ==================== 2019/12/17 13:34:57 - Set Variables.0 - Setting environment variables... 2019/12/17 13:34:57 - Set Variables.0 - Set variable CUSTOM_TABLE_NAME to value [accounts] 2019/12/17 13:34:57 - Write to log.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0) 2019/12/17 13:34:57 - Set Variables.0 - Finished after 1 rows. 2019/12/17 13:34:57 - Set Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0) 2019/12/17 13:34:57 - loopCustomTables - Starting entry [Populate Custom Fields] 2019/12/17 13:34:57 - Populate Custom Fields - Using run configuration [Pentaho local] 2019/12/17 13:34:57 - populateCustomFields - Starting entry [populateCustomFields] 2019/12/17 13:34:57 - populateCustomFields - Loading transformation from XML file [file:///var/www/html/suitecrm-data-integration/suitecrm-data-integration-server/solution/DWH/DIMENSIONS/populateCustomFields.ktr] 2019/12/17 13:34:58 - populateCustomFields - Using run configuration [Pentaho local] 2019/12/17 13:34:58 - populateCustomFields - Using legacy execution engine 2019/12/17 13:34:58 - populateCustomFields - Dispatching started for transformation [populateCustomFields] 2019/12/17 13:34:58 - Update Constants.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0) 2019/12/17 13:34:58 - Get Custom Fields.0 - Finished reading query, closing connection. 2019/12/17 13:34:58 - Get Custom Fields.0 - Finished processing (I=10, O=0, R=0, W=20, U=0, E=0) 2019/12/17 13:34:58 - Group Select Fields.0 - Finished processing (I=0, O=0, R=10, W=1, U=0, E=0) 2019/12/17 13:34:58 - Generate Select SQL.0 - Optimization level set to 9. 2019/12/17 13:34:58 - Generate Select SQL.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0) 2019/12/17 13:34:58 - populateCustomFieldsTemplate - Dispatching started for transformation [populateCustomFieldsTemplate] 2019/12/17 13:34:58 - Get Custom Table Data.0 - Finished reading query, closing connection. 2019/12/17 13:34:58 - Get Custom Table Data.0 - Finished processing (I=9845, O=0, R=0, W=9845, U=0, E=0) 2019/12/17 13:35:00 - Update Target Table.0 - Finished processing (I=9845, O=0, R=9845, W=9825, U=0, E=20) 2019/12/17 13:35:00 - Error.0 - Finished processing (I=0, O=0, R=20, W=20, U=0, E=0)

[samus-aran]

What a muppet! I've been doing this on the master branch.

Let me start again on 1.1 :ok:

[samus-aran]

Okay, Dokay...

So yeah my points are still standing that a little user documentation is required to add custom fields. But I can see Leads, Cases, Accounts and Opportunities custom fields are being generated upon original setup/run.

I then enable all those custom fields within CUSTOM_FIELDS and re run the script.

Again, looks like that has worked. BUT I again have some discrepancies mostly in the accounts table.

There appears to be no logs or errors raised.

[samus-aran]

Perhaps a little explanation is required. I yet AGAIN forgot to enable the custom fields so my steps are: setup the ETL, run it, THEN enable custom fields, run it again.

Obviously that cause differences, but in my case I lost all data from my F_LEAD_TRACKING table when I had about 3000+ in the S_LEADS_AUDIT (though they may not correlate to each other).

ALSO I really really dislike having to go to a different place to watch the logs. Can we have a parameter via the ./run script to allow us to watch the logs. I find it difficult to know what logs to look at - I have just been looking at the ETL-output.log

[samus-aran]

Just restarted the exercise again and yeah, can't run the script without having to import first so if someone wants to have a fully transcribed database the administrator would have to run ./run-suitecrm-data-integration.sh twice.

Ignore the F_LEADS_TRACKING as I'll make a comment in a different issue.

[harrisward]

Okay I think I follow all of the above.

Two things have to come out of this. The easy one is to add a run etl script that outputs logs to the screen. Easy to add such a script

Next we need to find a way to pre-populate the customg tables / fields mapping. This is only usually done via a initial ETL and then subsequent ETL runs. Perhaps we can add some ETL to the setup of SuiteCRM Data Integration that will allow users to configure this table before the first ETL run.

I'll create tickets