Split array items into rows in an SQL table

[abyars]

Currently, I have an array that keep up with items and quantity of items:

I want to create a new row for each item in the array. For instance:

SQL Table

----------------------------------------------------------------------------------------------------------------------------

 ID                           Item                                                                                             Quantity

 1                            How many boxes of large bags do you need? (2 max)                 2

 1                            How many TN450 toner cartridges?                                             1

etc...

Any help would be appreciated!

[lbendlin]

You iterate through the array and insert the individual rows into the SQL server table.  Note: Requires Premium license.

[abyars]

Could you show me an example? I'm pretty new to Flow.

[lbendlin]

Sure. Please post a usable sample version of the raw array (not a screenshot).

[abyars]

[

  {

    "Item": "How many 4\" hooks? (48 max)",

    "Quantity": "12"

  },

  {

    "Item": "How many boxes of large bags do you need? (2 max)",

    "Quantity": "2"

  },

  {

    "Item": "How many TN450 toner cartridges?",

    "Quantity": "1"

  },

  {

    "Item": "How many TN660 toner cartridges?",

    "Quantity": "1"

  },

  {

    "Item": "How many boxes of small bags do you need? (2 max)",

    "Quantity": "2"

  },

  {

    "Item": "How many bracelet bars do you need? (3 max)",

    "Quantity": "3"

  }

]

[lbendlin · Accepted solution]

Something like this.  You may want to have a primary key/index column in the target table, and maybe another column with the insert date and user who inserted etc.

[abyars]

Wow, that was much less confusing than I was expecting. That worked!

Unfortunately... I've encountered another problem. In the SQL table I have another column called SupplyID. I am pulling a SKU number for the item if item()?['Item'] = the same item in a Sharepoint list. When I add the SKU in the table, it does not return the values for Item or Quantity, only the SKU number:

Here's what my flow currently looks like:

 I'm marking the previous response a solution but would also love your input on this as well if possible.

[lbendlin]

You lost me a bit. Maybe you can describe the whole process?

[abyars]

@lbendlin,

I have a completely separate Sharepoint list that contains the list of all the items and their SKU number (backend product number):

If the current item()?['Item'] = Item in Sharepoint list, it should return the SKU number that corresponds with the Item in the list, then return that value in the SupplyID column in my SQL table:

However, when I try to populate the SupplyID, the SupplyDesc and SupplyQty columns return blank, and only the SupplyID column populates:

But whenever I remove the SKU field from the Insert SQL row, it populates the SupplyDesc and SupplyQty columns properly:

So somehow, the Get items from Sharepoint list function is keeping the SupplyDesc and SupplyQty from populating correctly.

If you need any additional information, please let me know.

[lbendlin]

There is no need to fetch the SKU list from the sharepoint every time.  It is sufficient to fetch that once at the beginning of the flow.

Then inside the single (!) Apply to each you can do your required lookup (which I don't see yet in your flow).  You need to use a yes/no action or that.

[abyars]

I currently have this outside of the apply to each:

But when I try to insert the SKU field into the table, it automatically creates a second apply to each:

The flow runs fine, but at the end it does not create the table:

[lbendlin]

You are missing the "is it in the list"  test.

Can you provide sample data for that list which matches some of the array samples?