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.
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.
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!
