ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Connect Knowledge Base

Definitive answers from Connect experts.

Bring Your Own Metadata Using Custom Fields

KaterinaRutova
Alteryx
Alteryx
Created
Since the introduction of Custom Fields in Connect allowing to define your own fields (for example to assign Terms and fill in respective values), we have received many questions on whether you can automatically load these fields. As of now – in Connect 20.3 - the answer is YES; you can do it for database assets! In this article we will show you how.

The process is straight forward:
  1. Define Custom Fields in your Connect instance.
  2. Assign Custom Fields to Asset type(s). 
  3. Push Custom Field values mapping through our API with the following sample workflow: Sample workflow provided as an attachment below. 
Steps 1%262: How to Create and Assign Custom Fields are already described in this article: https://community.alteryx.com/t5/Alteryx-Connect-Knowledge-Base/How-To-Use-Custom-Fields-in-Connect/...
There’s only one small change if you want to load Custom Fields. You need to check the Can be loaded option in its definition (as you can see on the following screenshot).
Please note that if you check the Can be loaded box when creating a Custom Field, you will make this field managed by the loader. It means you can update it with the loading job, but you won't be able to edit its values from the Connect UI.



Step 3 Push Custom Fields Values Mapping
Mapping dataset consists of four fields. For those we have to do mapping and value assignment:
  • LOAD_CODE – is concept unique identification of the run from the source system. You don’t need to bother too much with it and keep it for example as “CustomFields_001”. Once you have different source systems and you would like to have different load batches, every batch should have a unique LOAD_CODE. Either some unique numbers or hostname of the system can be used.
  • PARENT_XID – is unique identification of an Asset in Connect. You can find it in the URL once you are on the Asset details. The loaded Custom Fields will be shown on this object(xid). 
   
  • CUSTOM_FIELD_VALUE – is the value you are assigning to a combination of specific Asset (PARENT_XID) and specific Custom Field (CUSTOM_FIELD_CODE).
Note:
  • Relation field should be filled as XID of linked Asset. 
  • Dropdown field should be specified as code of value from the dropdown. If the field value has multiple words, for example “top secret”, such code will be “top_secret” (spaces are replaced by underscores). 

Now let's see how to prepare the Excel file.

In Alteryx Connect navigate to the root object of interest. For example it can be a database schema or a database server (Home / Data Sources / Databases). In our example the whole database server content is exported.
Click on the selected server object and open the three-dot context menu located in the asset menu on the right side.



From the exported dataset I kept only two columns: xid and entryName. Why did I do this? While creating the Custom Fields, I would like to know where they belong in a readable format. 

Further, add a new column for every Custom Field you would like to load. In my case, I’m adding three columns (DataSteward, DataClassification, and Text).

In addition to these columns, I’m adding the column LOAD_CODE.

The Excel file after updates will look like this:



Now let’s use our attached sample workflow which will push Custom Fields into Connect.



Check each tool configuration.

The readable fields we added before (DataSteward, DataClassification, and Text) are renamed to the Custom Field IDs.



Key Columns are xid and LOAD_CODE. 
Data Columns are our Custom Fields. 



Rename field names before pushing.



Check the push configuration:



After performing the whole procedure described above, we should have the Custom Fields assigned to the specified Assets:



Sample data used in examples above attached below.
Attachments
Comments
ColinZhang
6 - Meteoroid

Hi Katrina,

 

Thanks for sharing this, may I ask if the custom field values will be overwritten with every reload (referencing the "Remove Pre-Existing Data for this Load Code" option). 

 

On a separate scenario, if we only changed the Data Classification field, will the results for Data Steward or Text be wiped as well?

 

 

Thank you!

PetrH
Alteryx
Alteryx

Hi @ColinZhang 

Basically if you initially send the large dataset, containing multiple fields, and need to change just one, it woud be ideal to use same file for updating just one field in order to not loose other fields values from previous run. Or you can use the otther approach and send only one custom fields (for Example Data Classification) in one dataset and for the other use the other LOAD_CODE.

Since 2020.3 you can use also Connect API which would may be ideal for your use case. Its slightly mentioned here: https://community.alteryx.com/t5/Alteryx-Connect-Discussions/Dynamically-Load-Tags-for-Database-Tabl... and also in release notes. https://help.alteryx.com/release-notes/connect/connect-20203-release-notes

 

Hope it help.


Petr Havel