Importing data-items with a many-to-many relationship

Hello Triggre Community,

I am developing an application in which I am linking different Brands (e.g. Coca Cola, Pepsi) to different Regions (e.g. Europe, Russia). The following is the case:

  1. A Brand MAY be linked to multiple Regions in which it operates.
  2. Inversely, a Region MAY have multple Brands that operate in it.

Our customer wants to import data for Brands via an Excel-import. For illustration:
image

I want to import data by Brand Name (Name[ID]) and link it to regions by name (Region_Name). The above should link Coca Cola to both USA and the Netherlands. Kindercola is only linked to the Netherlands. This means the Netherlands does have two brands (Coca Cola and the Netherlands).

When I import this however I do not get the desired result. As far as I have gathered, when I check “Do not allow duplicates” it will ignore the second occurence of “Coca Cola” (makes sense). If I don’t, it will create two records for Coca Cola, which isn’t what I want either.

I know other Nocode platforms solve this by allowing a CSV field like below, which I tried but also to no avail:
image

Obviously I could use a linking table in between. However, I refuse to compromise by adding complexity to my data-model since this will negatively impact all future development.

I’m guessing there must be an intermediate way of importing using maybe temporary table, but I’m not sure how to do this. Didn’t find it on the community nor on the Triggre docs. I’m curious to hear how this can be done in Triggre.

Kind regards,
Pascal

1 Like

Hi Pascal,

A solution I am thinking of, knowing you don’t want to put a permanent linking entity between the two data-items, is to create a dedicated import data-item.

  1. Create a data-item, let’s say you name it ‘Brand and Region import’. You create two attributes: Name en Region, both texts of course.
  2. Then create a flow part with a Brand and Region import reference as your input attribute.
  3. Add as the first two actions a search action on both of the target data-items, let’s take Region.
  • Your first query states that Region should be equal to the Region attribute of the current (newly created) record.
  • Then in the second search action you search for the Brand, again with a search action: Brand should be equal to the Brand attribute of the created record.
    You now have both the Region and the Brand records available.

Note: Since I don not know the full extent of your functionality I will leave out the oppportunity to create a record if nothing has been found, you probably know that you can if necessary.

  1. Then you add an update action on the Region data-tem for the found Region record and select the Brands attribute you created in the data-item. If this has been defined as a list, and I assume it has, you will see a popup in Triggre that asks you what to do.
    Select ‘Add a Brand from the flow part to the already referenced Brand’ like in the example below:

image

Obviously you select the found Brand record to add to the Region. You can end the flow part after that, no output is needed. By referencing one to the other, the reference is automatically created the other way around.

  1. Create an Automation flow on the data trigger of creating a Brand and Region import record to complete the functionality. Add the new Flow Part to the Automation flow and save it.

  2. Rename your worksheet to ‘Brand and Region import’, matching the name of the data-item and make sure the header names do too match the names of the attributes.

  3. Run the import and see the magic at work :slight_smile:

Hope this helps. If not, feel free to add your questions below.

4 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.