Skip to Content (Press Enter)
2021-03-19 09-32-13
Soren Staun
123Views
3Comments
Solved
Importing relationships from Excel
Question
Reactive
Import/Export Data
Application Type
Reactive
Service Studio Version
11.10.15 (Build 40206)

Yeah, so. I made an example Excel with buckets on one tab and material on the other (see attached). And as a learning exercise I want to import the relationships from the Excel between the two. My current procedure works, but it's a bit entangled I'd say. Can you help make suggestions to make it better?

  1. Import All Materials (all fields including the Material as a string)
  2. Import All Buckets
  3. Add a relationship field to my buckets pointing to materials
  4. Make a server action with an aggregate where I added both Bucket and Material with Join condition Bucket.Material=Material.Material
  5. Loop over the rows and update the Bucket.MaterialID = Material.Id
  6. Update the Bucket Entities

This works, but I'm also stuck with both a Material (string) and a MaterialId in my Buckets which I need to clean up afterwards. Can I import the Buckets directly with a direct ID based (long int) to Materials table? 

(And yes, I studied https://www.outsystems.com/forums/discussion/46375/import-excel-for-two-entities-with-relatioship/ intently, but didn't crack the code yet)

Buckets.xlsx
0
0
25 Mar 2021
Copy Link
2019-03-24 20-44-19
Hugo Dias
Solution

Hi Soren,

You could import Materials into a table called Material as a first step.

In a second step you would import Buckets to a table called Bucket. While iterating the excel lines for each Bucket you should loop over the table Material where the Bucket Material Name (from the excel) equals the Material.Name (from the table created in step 1) to get the foreign key to populate the attribute Bucket.MaterialId in order to be equal to the Material.Id. The action Create_Bucket should be called only after this.

Hope it helps.

Regards.


See solution in context
1
0
25 Mar 2021
Copy Link
2018-12-10 12-16-21
Goncalo Duarte Almeida
Solution

Hi Soren

In addition to what Hugo suggests, you can check the oml attached.

Regards

Gonçalo Almeida

See solution in context
Importexcel.oml
0
0
26 Mar 2021
Copy Link
2021-03-19 09-32-13
Soren Staun

(I'm documenting this a bit more explicitly to make this thread more valuable for people finding it in the future)

Your solution is a good example of doing this. What you do is:

  1. Import the materials (top of the diagram below)
  2. Loop over the Bucket lines (ConvertFromExcel)
  3. Look up each Material ID via an aggregate called GetMaterialsByMaterial where there is a filter that returns the line where the Excel_Buckets.Material = Material.Material
  4. Assign the fields, including BucketRecord.Bucket.MaterialId = GetMaterialsByMaterial.List.Current.Material.Id (This uses the fact that the aggregate only returns one line back to us, so we can assume Current row to be the right one)
  5. Create Bucket call

This effectively avoids storing temp columns in my database. Solved!

(Goncalo's drawing for reference)


1
0
26 Mar 2021
Copy Link
2019-03-24 20-44-19
Hugo Dias
Solution

Hi Soren,

You could import Materials into a table called Material as a first step.

In a second step you would import Buckets to a table called Bucket. While iterating the excel lines for each Bucket you should loop over the table Material where the Bucket Material Name (from the excel) equals the Material.Name (from the table created in step 1) to get the foreign key to populate the attribute Bucket.MaterialId in order to be equal to the Material.Id. The action Create_Bucket should be called only after this.

Hope it helps.

Regards.


1
0
25 Mar 2021
Copy Link
2018-12-10 12-16-21
Goncalo Duarte Almeida
Solution

Hi Soren

In addition to what Hugo suggests, you can check the oml attached.

Regards

Gonçalo Almeida

Importexcel.oml
0
0
26 Mar 2021
Copy Link
2021-03-19 09-32-13
Soren Staun

(I'm documenting this a bit more explicitly to make this thread more valuable for people finding it in the future)

Your solution is a good example of doing this. What you do is:

  1. Import the materials (top of the diagram below)
  2. Loop over the Bucket lines (ConvertFromExcel)
  3. Look up each Material ID via an aggregate called GetMaterialsByMaterial where there is a filter that returns the line where the Excel_Buckets.Material = Material.Material
  4. Assign the fields, including BucketRecord.Bucket.MaterialId = GetMaterialsByMaterial.List.Current.Material.Id (This uses the fact that the aggregate only returns one line back to us, so we can assume Current row to be the right one)
  5. Create Bucket call

This effectively avoids storing temp columns in my database. Solved!

(Goncalo's drawing for reference)


1
0
26 Mar 2021
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.