Forms to Sharepoint with Column conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-1448660%22%20slang%3D%22en-US%22%3EForms%20to%20Sharepoint%20with%20Column%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448660%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20would%20I%20loop%20through%20multiple%20items%20in%20a%20SharePoint%20column%20to%20add%20a%20value%20in%20another%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EI%20have%20a%20Microsoft%20Form%20that%20is%20used%20as%20an%20order%20form%20and%20has%20an%20option%20for%20laptops.%20Once%20the%20form%20is%20filled%20in%2C%20it%20will%20trigger%20a%20Flow%20which%20adds%20the%20items%20to%20a%20SP%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20if%20someone%20orders%202%20laptops%2C%20it%20adds%20the%20items%20in%20an%20array%20in%20a%20column%20in%20the%20list.%20I%20have%20another%20column%20that%20I'm%20using%20to%20create%20the%20cost%20of%20the%20items.%20I%20am%20using%20an%20indexOf%20expression%20which%20to%20look%20for%20a%20value%20and%20then%20add%20that%20value%20to%20the%20new%20column.%20When%20there%20are%20multiple%20items%20chosen%2C%20it%20will%20only%20show%20the%20first%20items%20cost%20rather%20than%20adding%20them%20all%20together.%20Anyone%20any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1448660%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1449349%22%20slang%3D%22en-US%22%3ERe%3A%20Forms%20to%20Sharepoint%20with%20Column%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1449349%22%20slang%3D%22en-US%22%3EThanks%20for%20that%20Rob.%20I'll%20have%20to%20try%20that%20out.%20How%20would%20they%20go%20about%20selecting%20more%20than%20one%20item%20-%20for%20example%202%20headsets%20and%202%20monitors%3F%20Would%20the%20user%20need%20to%20go%20through%20the%20form%20for%20each%20item%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1449370%22%20slang%3D%22en-US%22%3ERe%3A%20Forms%20to%20Sharepoint%20with%20Column%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1449370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292517%22%20target%3D%22_blank%22%3E%40timparsons%3C%2FA%3E%26nbsp%3Byes%20the%20way%20I've%20done%20it%20they%20would%20need%201%20form%20for%20however%20many%20laptops%20they%20wanted%20and%20a%20separate%20form%20for%20however%20many%20headsets%20etc.%20I'll%20post%20up%20the%20method%20for%20combining%20item%20types%20as%20soon%20as%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1449088%22%20slang%3D%22en-US%22%3ERe%3A%20Forms%20to%20Sharepoint%20with%20Column%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1449088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292517%22%20target%3D%22_blank%22%3E%40timparsons%3C%2FA%3E%26nbsp%3Bthe%20way%20I%20do%20this%20sort%20of%20thing%20is%20as%20follows%20and%20doesn't%20involve%20arrays.%20I%20have%202%20lists%2C%20the%20main%20one%20for%20the%20orders%20and%20a%20second%20one%20that%20holds%20the%20unit%20cost%20for%20each%20item%20that%20might%20be%20ordered%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-List-Orders.png%22%20style%3D%22width%3A%20844px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197345i3C47D14FBDD44B64%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SP-List-Orders.png%22%20alt%3D%220-SP-List-Orders.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-List-Orders-UnitCost.png%22%20style%3D%22width%3A%20432px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197344iDE5A85F4F60D06F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SP-List-Orders-UnitCost.png%22%20alt%3D%220-SP-List-Orders-UnitCost.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollowing%20is%20the%20form%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Form.png%22%20style%3D%22width%3A%20868px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197346i3D172F3E56EC70FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221-Form.png%22%20alt%3D%221-Form.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20flow%20is%20then%20below.%20The%20trigger%20is%20%3CEM%3EWhen%20a%20new%20response%20is%20submitted%3C%2FEM%3E%20and%20the%20first%20action%20is%20%3CEM%3EGet%20response%20details%3C%2FEM%3E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20add%20a%20SharePoint%20%3CEM%3EGet%20items%3C%2FEM%3E%20gets%20the%20items%20from%20the%20UnitCost%20list%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222a-Flow-trigger-getResponse-getItems.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197348iEEFDA0E2510C96F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222a-Flow-trigger-getResponse-getItems.png%22%20alt%3D%222a-Flow-trigger-getResponse-getItems.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20add%20an%20%3CEM%3EApply%20to%20each%3C%2FEM%3E%20control%20and%20select%20%3CSTRONG%3Evalue%3C%2FSTRONG%3E%20from%20the%20Get%20items%20section%20of%20the%20dynamic%20content.%20Next%2C%20add%20a%20Condition%20and%20in%20the%20left%20box%20select%20%3CSTRONG%3ETitle%3C%2FSTRONG%3E%20from%20the%20Get%20items%20section%20of%20the%20dynamic%20content%20%3CSTRONG%3Eis%20equal%20to%3C%2FSTRONG%3E%20then%20select%3CSTRONG%3E%20Item%3C%2FSTRONG%3E%20from%20the%20Get%20response%20details%20section%20of%20the%20dynamic%20content.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20though%20you%20would%20have%20asked%20for%20the%20quantity%20in%20your%20form%2C%20Forms%20nevertheless%20saves%20it%20as%20text.%20So%20add%20a%20%3CEM%3ECompose%3C%2FEM%3E%20and%20add%20the%20following%20expression%20(in%20the%20Expression%20tab%20of%20the%20dynamic%20content%20box)%3A%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSTRONG%3Eint(body('Get_response_details')%3F%5B'r4a70bd783b204c608871dd004c360add'%5D)%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eyou%20won't%20need%20to%20add%20the%20ID%20for%20the%20quantity%20question%2C%20just%20select%20it%20from%20the%20dynamic%20content%20tab%20and%20it%20will%20add%20the%20relevant%20question%20id.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EAdd%20another%20%3CEM%3ECompose%3C%2FEM%3E%20and%20add%20the%20UnitCost%20column%20from%20the%20Get%20items%20section%20of%20the%20dynamic%20content%20box.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20always%20like%20Compose%20controls%20so%20add%20another%20one%20for%20the%20total%20cost%20where%20the%20expression%20will%20be%20a%20multiplication%20of%20the%202%20previous%20Compose%20controls%2C%20i.e.%20UnitCost%20*%20Quantity%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CSTRONG%3Emul(outputs('ComposeUnitCost')%2Coutputs('ComposeQuantity'))%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EFinally%20we%20add%20the%20SharePoint%20Create%20item%20action%20and%20add%20in%20the%20outputs%20from%20the%20Compose%20controls%20and%20the%20other%20relevant%20fields.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222b-apply-Compose-Create.png%22%20style%3D%22width%3A%20761px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197349iB6268F3549253B0D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222b-apply-Compose-Create.png%22%20alt%3D%222b-apply-Compose-Create.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20the%20number%20ordered%2C%20their%20unit%20cost%20and%20the%20total%20cost%20are%20added%20into%20the%20SharePoint%20list%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-List-Orders.png%22%20style%3D%22width%3A%20844px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197358iF50DD8EDDB465269%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SP-List-Orders.png%22%20alt%3D%220-SP-List-Orders.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

How would I loop through multiple items in a SharePoint column to add a value in another column.

 

Example:

I have a Microsoft Form that is used as an order form and has an option for laptops. Once the form is filled in, it will trigger a Flow which adds the items to a SP list. 

 

The problem is, if someone orders 2 laptops, it adds the items in an array in a column in the list. I have another column that I'm using to create the cost of the items. I am using an indexOf expression which to look for a value and then add that value to the new column. When there are multiple items chosen, it will only show the first items cost rather than adding them all together. Anyone any suggestions?

3 Replies

@timparsons the way I do this sort of thing is as follows and doesn't involve arrays. I have 2 lists, the main one for the orders and a second one that holds the unit cost for each item that might be ordered:

 

0-SP-List-Orders.png

 

0-SP-List-Orders-UnitCost.png

 

Following is the form:

1-Form.png

 

The flow is then below. The trigger is When a new response is submitted and the first action is Get response details

 

Next, add a SharePoint Get items gets the items from the UnitCost list:

 

2a-Flow-trigger-getResponse-getItems.png

 

Next add an Apply to each control and select value from the Get items section of the dynamic content. Next, add a Condition and in the left box select Title from the Get items section of the dynamic content is equal to then select Item from the Get response details section of the dynamic content.

 

Even though you would have asked for the quantity in your form, Forms nevertheless saves it as text. So add a Compose and add the following expression (in the Expression tab of the dynamic content box): 

int(body('Get_response_details')?['r4a70bd783b204c608871dd004c360add'])
you won't need to add the ID for the quantity question, just select it from the dynamic content tab and it will add the relevant question id.
 
Add another Compose and add the UnitCost column from the Get items section of the dynamic content box.
 
I always like Compose controls so add another one for the total cost where the expression will be a multiplication of the 2 previous Compose controls, i.e. UnitCost * Quantity:
mul(outputs('ComposeUnitCost'),outputs('ComposeQuantity'))
 
Finally we add the SharePoint Create item action and add in the outputs from the Compose controls and the other relevant fields.

 

2b-apply-Compose-Create.png

 

As you can see, the number ordered, their unit cost and the total cost are added into the SharePoint list:


0-SP-List-Orders.png

 

Hope that helps.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Thanks for that Rob. I'll have to try that out. How would they go about selecting more than one item - for example 2 headsets and 2 monitors? Would the user need to go through the form for each item?

@timparsons yes the way I've done it they would need 1 form for however many laptops they wanted and a separate form for however many headsets etc. I'll post up the method for combining item types as soon as possible.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User