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!
