Out-of-the-Box Behavior
Semarchy’s standard import mechanism expects foreign key fields (references) to be populated with the internal ID (typically the FID or primary key) of the referenced entity. This is why, during import, users cannot simply enter the name of a product team—they must provide the corresponding ID.
Is There a Native Way to Accept Names?
Currently, there is no direct, out-of-the-box configuration in Semarchy xDM that allows business users to enter a reference entity’s name (e.g., "Product Team Name") in Excel and have the platform automatically resolve it to the correct ID during import. The import process does not natively support lookups or mapping by alternate fields such as name or code.
Workarounds and Best Practices
1. Pre-Processing the Excel File
Manual Lookup: Before importing, business users or support staff must manually look up and replace names with IDs in the Excel sheet. This is the current pain point.
VLOOKUP Automation: Provide a reference tab in the Excel template containing all valid names and IDs. Users can use Excel’s VLOOKUP to populate the ID column automatically based on the name they enter. This reduces manual lookup but still requires some Excel proficiency.
2. Enhance the Import Experience
Custom Import Logic: Develop a pre-processing script or lightweight tool (e.g., in Python, Power Automate, or a simple Excel macro) that reads the names entered by users and replaces them with the correct IDs before submitting the data to Semarchy. This can be run by business users or as part of the import pipeline.
Data Integration Tools: Use an ETL or data integration tool to perform the lookup and transformation before loading data into Semarchy.
3. Model and UI Adjustments
Expose Reference Data: Ensure that exports and import templates include both the ID and name/code for referenced entities, making it easier for users to find the correct value.
User Training: Provide clear instructions and template files that guide users on how to perform the lookup or use VLOOKUP.
Ivar.gjerde
Hi,
Is there a way to configure import from excel so that a business user can import data without having to look up the IDs for any referenced entities? It's causing a lot of pushback from our business users and steals dev time when we need data engineers to support with what should be basic imports of data into Semarchy
Details:
We utilize reference entities in our models to hold LoV type attributes. E.g. we have product lines that we use to categorize our products in our product model. This enables us to let Business Users maintain their own reference data without involving developers. And also lets us add additional attributes to the entities like which product team the product line is managed by. The Product team is another reference entity where we have a list of the product teams to ensure consistency in the input.
We have an issue when the business users want to use the import/export to excel functionality to update the data sets. E.g. if we want to bulk add multiple product lines the business user will add the new lines to the excel sheet, but for the Product teams, Semarchy will ask for the ID of the row from the Product Teams entity. It will not accept e.g. the name of the team. The Business User does not know the ID, and then we need to involve the Dev team to support even if it shouldn't be necessary to do so. We use FDN_ProductTeam in the form that has been configured to support the import. Is there a better way to do this where we can make it easy enough that a business user can import data without having to look up all the IDs for any referenced entities
1 person has this question