How to divide multiline texts into multiple rows in table

[Kai_Jie_Ang]

Hello world,

Does anyone know how can we insert new rows to have each line of multiline text in separate row?

I am creating an order dashboard. The current database has all the order items listed in one cell for each order and I would like to have every order item in each separate row. That way I can join with product catalogue database and pull out cost for each order item on the table too.

This is how the current table looks like:

And the desired output is as follow:

Please can someone help me out...

[Rati_Bakhtadze]

Hey,
I tried to recreate what you have. Here are the results of how you can handle it.
Here is the data:

Here is Javascript Query:

Code you can copy:

const result = [];

existedData.data.forEach(dataItem => {
    const orderItems = dataItem.order_items.split('\n');
    orderItems.forEach(orderItem => {
        result.push({
            name: dataItem.name,
            email: dataItem.email,
            order_items: orderItem
        });
    });
});

return result;

You can adjust and add them as you wish.

and here is saving in the new table. Please note to have the new table with the same column names and types.

[Kai_Jie_Ang]

Hi Rati, thank you so much for the prompt response.

I followed exactly the steps above but there's error with the JavaScript

It says "existedData.data.forEach is not a function"

Below is the existedData.data structure which i hope will help.

Appreciate your help.

[Rati_Bakhtadze]

Have you transformed the data? As it is in the first picture:

return formatDataAsArray(data)

[Francisco_Villarreal]

@Kai_Jie_Ang Hi Kai, just checking in to see if you were able to get this to work. I Modified the script that Rati shared with you a little bit to include the array transformation that he suggested. This worked as expected and should give you the results you expect. Here is the script:

var result = [];

formatDataAsArray(data).forEach(dataItem => {
  var orderItems = dataItem.order_items.split('\n');
  
  orderItems.forEach(orderItem => {
    result.push({
      order_id: dataItem.order_id,
      college: dataItem.college,
      name: dataItem.name,
      email_address: dataItem.email_address,
      order_value: dataItem.order_value,
      order_items: orderItem
    });
  });
});

return result;

You can add this in the 'Transform results' section of your query as shown below:

I hope this helps. Let me know if you have any other questions.

[Kai_Jie_Ang]

Hey thanks for all these, Rati's method works just fine and love to see the alternative above!