Skip to main content

I have a field in a transaction list that returns the Employee ID (data type: Text) and another column that contains the Company ID (data type: Text). I need to concatenate these two fields to create a final Employee ID (e.g., “EMPID–COMPID”). This concatenated field should then map to the Employee dimension.

The issue is that I’m unable to tag this concatenated field to the Employee dimension since both source fields are in text format, and I can’t find a way to convert the resulting text into a dimension-compatible value.

To address this, I created a concatenated Employee ID Initial (Text) field and added another property called Employee ID Final (data type: Dimension → Employee). I then tried the following formula: Employee ID Final = item(EE_Paylocity_Actuals.'Employee ID Initial','Employee - Final'.'Employee ID'). 

However, this only works if the Employee dimension is already pre-populated with all Employee IDs. That’s not ideal because it would require the client to manually maintain or update the Employee dimension each time a new employee is added, which adds unnecessary manual effort.

Is there a way to make the system automatically create or recognize new Employee IDs in the dimension when they appear in the transaction list?

Hi ​@Nusayba ,

There are 2 options which might work to automatically create new ‘EMPID–COMPID’ items in the Employee Dimension.

  1. If the transaction list is getting the data from external source
    • Then fetch the ‘EMPID–COMPID’ right from the source
    • Create a column in the transaction list for ‘Final Emp Id’ with datatype: Employee Dimension
    • This column should be imported from the external source / CSV file (in the mapping configuration)
    • Make sure to toggle On the ‘Automatically create new items’ (refer below image)
Example: Transaction List with ‘Final Employee ID’ property of datatype: Employee dimension
 

This will automatically create new items in the Employee Dimension whenever there’s a new ID in transaction list.

 

  1. Approach 2: Alternatively, you can try to use this approach using  List-to-List import
    • Create a concatenated text property in transaction list which will contain both emp id - company id (e.g., “EMPID–COMPID”)
    • In the employee dimension list, use the ‘List to List’ feature to import the above column: “EMPID–COMPID” from transaction list to dimension list
    • Save the Import configuration
    • Create a data action process flow (using the saved configuration) to automatically create new items in the employee dimension
    • Schedule the data flow to run on a daily basis
    • https://kb.pigment.com/docs/set-up-list-to-list-import
    • https://kb.pigment.com/docs/create-and-manage-data-flows

I would highly suggest you to try the first approach .

Hope, it helps!

Just in case, if you are following the 2nd approach:
You’d need to sure that you are having the concatenated name

  • Create a text property for ‘concatenated name’
    Example: Transaction list name is “Employees Master Data”
  • Text Property (e.g., “EMPID–COMPID”) in the transaction list with the below formula: 
    ‘Employees Master Data’.EMPID & “-” & ‘Employees Master Data’.COMPID
    Assuming: EMPID , COMPID are 2 properties of the transaction list

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement