Team Forms
Log in

Join the Team Forms community

Updated 2 months ago

Displaying multiple items in separate rows in team forms

Help
At a glance
The community member has created three linked forms that allow users to select a supplier and the items sold by that supplier. The data is then added to an Excel sheet with columns for OrderID, Supplier, Items ordered, and Quantity. In the second form, users can select an OrderID and input a purchase order number, which is added to the Excel sheet. The community member's query is how to display the items ordered in separate rows in the third form, with input fields for the received quantity of each item. A community member provided an answer suggesting that the community member update the Power Automate workflow to store the data in a more optimal structure, with each item and its quantity in a separate row. This would simplify the JavaScript needed to populate a data-grid component and make the Excel data easier to read, filter, and report on. Another community member confirmed that they were able to solve the issue using JavaScript to iterate over the array of items.
AAnonymous

I have created three forms which are linked to one another, in which first form takes the input of supplier name and based on selected supplier, the user can select the items sold by that particular supplier For eg, Dell (supplier) sells Mouse, keyboard, Monitor, Laptop (Items sold by dell supplier which will be shown in items list) and when the user submits it'll add entry to excel as:

OrderID | Supplier | Items ordered | Quantity
01 | Dell | ["Mouse","Keyboard","Monitor","Laptop"] | [2,3,1,5]

Now in second form there is orderID drop down list and when the user selects the ID - 1, he can input the purchase order number, which will shown in same excel sheet and adds the purchase number as:

OrderID | Supplier | Items ordered | Quantity | Purchase Number
01 | Dell | ["Mouse","Keyboard","Monitor","Laptop"] | [2,3,1,5] | PN-0001

My query is that====>Now, In my last form i want to show these items in separate rows for eg, the user ordered 2-mouse, 3-keyboard, 1-monitor, 5-laptop SO, i want to display these as:

OrderID | Supplier | Items ordered | Quantity | Purchase Number
01 | Dell | "Mouse" | 2 | PN-0001
01 | Dell | "Keyboard" | 3 | PN-0001
01 | Dell | "Monitor" | 1 | PN-0001
01 | Dell | "Laptop" | 5 | PN-0001

Because, based on the above table in excel in third form i have third input field where i have to write the received quantity like how much quantity of items the user received and corresponding to the items it should display the input field.

EErin Dwyer
Marked as solution

Hi There,

It is certinly possible to achieve this (populate a data-grid component using data from SharePoint) with some custom JavaScript. However before we dive into that I think the structure of your Excel data is not optimal. You should consider first updating the Power Automate workflow to store your data in the structure shown below:

| OrderID | Supplier | Items ordered | Quantity |

|---------|----------------|----------------|----------|
|01 | Dell | Mouse | 2 |
|01 | Dell | Keyboard | 3 |
|01 | Dell | Monitor | 1 |
|01 | Dell | Laptop | 5 |


The structure above will simplify the JavaScript used to populate a data-grid in addition your excel data will be easier to read, filter and report on. It should be fairly simple to achieve this in Microsoft Power Automate with a loop.


View full solution
E
A
1 comment·1 reply
EErin Dwyer

Hi There,

It is certinly possible to achieve this (populate a data-grid component using data from SharePoint) with some custom JavaScript. However before we dive into that I think the structure of your Excel data is not optimal. You should consider first updating the Power Automate workflow to store your data in the structure shown below:

| OrderID | Supplier | Items ordered | Quantity |

|---------|----------------|----------------|----------|
|01 | Dell | Mouse | 2 |
|01 | Dell | Keyboard | 3 |
|01 | Dell | Monitor | 1 |
|01 | Dell | Laptop | 5 |


The structure above will simplify the JavaScript used to populate a data-grid in addition your excel data will be easier to read, filter and report on. It should be fairly simple to achieve this in Microsoft Power Automate with a loop.


AAnonymous

Hi Erin,
Thank you for the help. It was really helpful! I managed to solve the issue by using JavaScript to iterate over the array of items with the split and rowIndex functions.

Thanks again for your guidance!