I can’t wrap my head around how to solve this little problem:
I have the following table:
What I basically want to do is to loop through these line items and stop, when a certain condition is met.
The condition is this
IF item supplier X’s total value exceeds 100,000
I considered a loop and doing a Variable Condition Loop End Node, following the approach that @rs1 described in this workflow on the Community Hub:
However, I can’t get the inner part of the loop to be working. I already played around with the GroupBy Node as well as the Moving Aggregator Node but they do not help me inside the loop (or I use them the wrong way, which is probably more realistic )
I have provided a (pretty basic) example workflow where I also have a Double Widget Node to interactively set the threshold value (the 100,000 from the IF statement above).
What I basically want to do is to assign the “values” to X until their threshold (100,000 in this example) is met, then assign the rest of the “values” to the other supplier, Y in this case. Line items details however should be maintained.
Based on your above feedback, I am understanding that from Row0 till Row4, the “item supplier” column would be “X” (as the cumumulative sum value till this row is 100,000) and for rest of the rows, it would be “Y”.
Also, the above item supplier column would be a new one or the existing column needs to be updated?
@kowisoft without having looked deeply into it you will have to collect what is happening and store the intermediate values. I constructed this example but there is also the recursive loop that can somehow do that automatically but I am not that comfortable with.
Question is do you want some sort of optimization where you would try to come as close to the 100k as possible with your values.
Thank you for providing a link to that post @ajit555
If I get it right, it means that the input ports of the Recursive Loop Node in that example do the following:
Input Port: takes the ‘current batch’ aka the current supplier that is assigned to the project (sub)list
Input Port: takes the remaining projects, where no supplier is yet assigned
Input Port: takes the list of the remaining suppliers, who have not yet been assigned to a project
Is that correct?
Also big THANK YOU to @mlauber71 for reminding me that I have asked a similar question some time ago. Sorry for ‘double posting’ (kind of), I knew I had this issue before in a pretty similar use case but didn’t scroll ‘far enough’ to find that old post of mine.
always passes through the projects table - on first iteration in full, in any subsequent iteration it is passing through “what is left over” (bottom port of 6)
Always passes through the suppliers table - on first iteration in full, in any subsequent iteration only the remaining suppliers (bottom port of 7)
Collection output port - collects those projects enriched with an allocated supplier, where the cumultative value was below the threshold (top port of 6)
recursion port for projects table - passes those projects that came after the cumultative threshold was met back to the beginning of the loop for the next iteration. Important: Table columns need to match the initial input 1:1 - that’s why the cumultative sum column gets removed before passing the table back
recursion port for suppliers table - passes through the remaining suppliers to be processed (bottom port of 7)
Thank you for your explanation @MartinDDDD - this made it a lot clearer.
I have provided my ‘final’ dummy workflow below:
Some caveats in my use case that required a (slightly) different approach:
I had some ‘required’ vendors / suppliers in my list, e. g. for products that can only be sourced from those suppliers. Therefore I was using a Row Splitter to split up the original table before I start the Recursive Loop. The basic idea is to separate these ‘already assigned’ line items to a separate branch and merge them back together after the loop
Because of these ‘required’ suppliers I also deducted the ‘already assigned’ volume from these required line items from the threshold value (driven by the Double Widget Node) like so → see screenshot below. Important: I only do this for Supplier 1 (S1) because I only have 2 suppliers → see also next bullet point
I only have two suppliers (in this example and in my real world use case). As a result of this I limited the Maximum Number of Iterations to 1. This basically says…
Assign line items up to the (adjusted) volume to S1, the rest stays unassigned
Because I only have 2 suppliers, I also made sure that I have 2 collector ports (compared to 2 recursion ports) for the Recursive Loop End Node so I get the items that were assigned S1 at output port 1 and the ‘remainder’ at output port 2
Then it’s just a question of conditional logic (Rule Engine Node) and a little bit of concatenation to bring it all back together
At the end, I have just a simple GroupBy (not needed in my real world use case) to test if the workflow delivers the results as expected.
Thanks a lot for all your help in this thread @MartinDDDD , @ajit555 and @mlauber71 - I tested this now with all kinds of different values and it works and seems to be robust enough.
The one thing that remains for me now is to apply this to my real world use case, where I work more with variables
)
