Showing results for 
Search instead for 
Did you mean: 
New Member

Ideas please! How to assign items in a list a parcel reference based on their collective value.

Hello All,


I'm after some ideas please. I have a SP List which contains orders created on any given day. The orders range in value from £200 - £1000. When I post the orders, I need to group them in to parcels, the maximum parcel value can be £2500. I do this grouping activity once at the end of the day, currently this sends me an email with a HTML table using the Get Items step with an appropriate odata filter for date.


Therefore I need a way to assign the orders to a parcel number based on their combined value. Ideally I don't want any wasted space in a parcel, it is important to get as close to £2500 as possible to reduce postage costs.


Ideas so far:


When a list item is written, get the value of the previous items and add them together. Apply a condition, if the value of the new item and previous items with the same parcel reference number is less than £2500, give the same parcel reference number (the reference will simply be 1, 2, 3 etc, unique to that day). If the value of all items plus the new item is greater than £2500, give a new reference number of parcel reference+1.


Therefore each time it would be looking at the parcel so far, considering the value, and either adding an item in to the parcel or starting a new one.


This does not calculate the optimum assignments, it only considers the order in which they are sold, not the overall best way.


Even with this approach I will have a few challenges, such as using sums in Power Automate (with decimal points I have always ran in to issues), getting the reference number and having a counter method that increases the reference number (I think I can manage this).


Another idea is to have a staging spreadsheet, where I can use the formula function of excel which is more versatile. Have Power Automate write a formula to Excel, some sort of countif, or sumif value. Again, I can't think of what exactly is best.


Any ideas would be appreciated!


Helpful resources

Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,191)