Hello @Julien Pahaut  ,

You can achieve your use case by following:

The data model consists of the following entities and relationships:

1. Locations:

   • Attributes: Id, Label (e.g., "Garage", "Room One").
   • Linked to Objects via LocationId.

2. Objects:

   • Attributes: Id, Name, Quantity, LocationId, OriginsId.
   • Represents the main object entity.

3. ObjectTags:

   • Attributes: Id, ObjectId (FK to Objects), TagId (FK to test\Tags).
   • Intermediate table linking objects to tags.

4. Origins:

   • Attributes: Id, Label (e.g., "Europe", "US").
   • Linked to Objects via OriginsId.

5. Tags:

   • Attributes: Id, Label (e.g., "Food", "Liquid", "Organic").
   • Linked to ObjectTags for many-to-many relationships with objects.

1.  Start

• The process begins with the ExcelData list being passed as input. This contains the rows of data uploaded from the Excel file.

2. For Each Excel Row

• A For Each loop iterates through the ExcelData list. Each row is processed individually, referred to as ExcelData.Current.

3. Check Location

• Use the GetLocation aggregate to check if the location in the current row exists in the Location entity.
• Filter:

  Location.LocationName = ExcelData.Current.YourStructure.Location

• 
  
  • If the location does not exist (GetLocation.List.Empty = True):
    • Call the CreateLocation action to insert the new location.
  • If the location exists (GetLocation.List.Empty = False):
    • Skip location creation and proceed.

5. Check Origin

• Use the GetOrigin aggregate to check if the origin in the current row exists in the Origin entity.
• Filter:

  Origin.OriginName = ExcelData.Current.YourStructure.Origin

• 
  
  • If the origin does not exist (GetOrigin.List.Empty = True):
    • Call the CreateOrigin action to insert the new origin.
  • If the origin exists (GetOrigin.List.Empty = False):
    • Skip origin creation and proceed.

6. Check Object

• Use the GetObjectByName aggregate to check if the object (identified by its Name) exists in the Object entity.
• Filter:

  Object.Name = ExcelData.Current.YourStructure.Name

• 

  • If the object does not exist (GetObjectByName.List.Empty = True):
    • Call the CreateObject action to insert the new object (e.g., with Name and Quantity).
  • If the object exists (GetObjectByName.List.Empty = False):
    • Skip object creation and proceed.

7. Process Tags

• Use a helper function (String_Split) to split the Tags field (comma-separated) into individual tags.
• For each tag in the list:
  • Use an aggregate (GetTag) to check if the tag exists in test\Tags.
  • Filter:

    Tags.Label = String_Split.Current.Tag

  • Decision:
    • If the tag does not exist (GetTag.List.Empty = True):
      • Call the CreateTag action to insert the tag and retrieve its Id.
    • If the tag exists (GetTag.List.Empty = False):
      • Retrieve the existing TagId.

8. Create Object-Tag Relationships 

For each tag in the split list:

• Insert a record into ObjectTags with:
  • ObjectId (from the object created/retrieved earlier).
  • TagId (from the tag created/retrieved earlier).

Your database scheme should look something like:

Hello @Julien Pahaut, 
You can see attached an example of how the data import process would look for the respective tables, following the best practices of OutSystems. 

If you have any further doubts or questions, I will be more than happy to help.

Rui 

@Julien Pahaut

In OutSystems, there is no fully automatic way to directly split an Excel file into multiple entities with relationships. Instead, you need to implement logic to:

1. Process the Excel data:

   • Parse the file into a list using built-in or Forge components.

2. Identify unique data:

   • Extract distinct records for entities based on business rules (e.g., unique locations, tags, origins).

3. Insert data into the appropriate entities:

   • Insert records into separate entities, ensuring no duplicates (e.g., Location, Tag, Origin).

4. Maintain relationships:

   • While inserting data, verify and establish relationships in intermediate tables (Object_Location, Object_Tag, Object_Origin).
     
     You must write logic to identify unique records and handle relationships between entities.