Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Connect Knowledge Base

Definitive answers from Connect experts.

Bring Your Own Metadata Using Custom Fields

KaterinaR
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&2: 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
cy_zhang
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 @cy_zhang 

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

sglynn14
8 - Asteroid

Hey @KaterinaR I only see the xlsx export added as an attachment to your post.  I don't see the workflow attached.  Would you be able to share that?

 

Best,

Sean

lepome
Alteryx Alumni (Retired)

@sglynn14 
Which case is this for?  I tried to republish this article with the workflow attached, but for the moment, just tell me the case number and I'll get it sent to you.

sglynn14
8 - Asteroid

Hey @lepome  I don't have a case number open for this.  I was just exploring the capabilities of custom fields and came across this post.  Would you like to take down my email and send it to me that way?

lepome
Alteryx Alumni (Retired)

@sglynn14 
We much prefer to have all customers holding paid licenses open cases through the Case Portal.  It allows us to track their issues, and it prevents situations where customer requests might otherwise slip through cracks if any one Support Engineer is out of office or busy with something else.

For users who have unpaid licenses (Alteryx for Good or Trial) their primary resource is the Community, and I am flagging the article so that it will be addressed as soon as we can get to it.  I don't have a time frame of when that will be, however.