Hi @Mike_Alvarez !
Any specific reason you want to use a different table to track the
[Outgoing Order Queue] using a canvas button ?
Because this is doable using a filtered connected view of the table
[All Orders] and adding to it an “Index” field to determine the visual position of an order in the table
[All Orders] based on the client who ordered something, the date and if the order has been consumed or not …
[All Orders] and its view
[Outgoing Order Queue] mirror each other, what happens in one table also happens in the other so the next order per client for today can be recalculated depending on the value in the field
[Order Consumed] and filtered out once fulfilled .
I don’t have enough coffee in my system yet, so to illustrate, here’s a sample where I’ve somewhat reproduced your setup using relations fields for the clients and the ordered product
So the first table is the (source) table
[All Orders] where I’ve added a field called
[Index - Next Order] .
This field has this formula :
[Date to Order] = thisRow.[Date to Order]
Clients = thisRow.Clients
And what this formula does is :
If the order has been marked as
consumed by setting the toggle in the row to
true, regardless of the client and the date, it outputs
0 (because Find() outputs
-1 if something’s not found and I just find
0 nicer … So the
If() in itself is optional only the
[All Orders].Filter( ... ).Find(thisRow) is really needed ).
Else: it retrieves the visual position (literally) of
thisRow in the list of rows returned by the
Filter() and the list of rows here would contain the rows where:
CurrentValue.[Date to Order] =
thisRow.[Date to Order]
CurrentValue.[Order Consumed].Not() (the current value of the toggle is false)
Now because this formula pin-point the visual position of a row when an order for Client X on Date Y has been marked as consumed, the index will be recalculated and output
0 and the upcoming order for that same client on that day will be replaced by
The recalculation will also happen if you manually rearrange the rows in the table .
Below the table
[All Orders] you’ll find a canvas date control to manually simulate today’s date (it’s optional and just there for testing) and the connected and filtered view of
[All Orders] :
[Outgoing Order Queue]
And the filter applied to this view is simply :
thisRow.[Index - Next Order] = 1 AND thisRow.[Date to Order].Matches(Today)
which returns only the very first order per client on the date chosen in the canvas control and still allows you to easily mark an order as consumed (which will then filter the row out, pick up the next one, etc… until there’s none left for that day) .
thisRow.[Date to Order].Matches(Today) in the filter of
[Outgoing Order Queue] can be replaced by
thisRow.[Date to Order] = Today()
Now a small trouble with this is that if you manually mark an order as consumed in
[Outgoing Order Queue], you’ll also need to “reset” the focus on the upcoming order for Client X manually.
I mean, as the rows are filtered out, the next ones appear … and this can take a moment while the focus is still on the row currently marked as consumed… which can make the view a little bit confusing .
The only way to go faster there is to mark an order as consumed and then, directly click outside of the view .
But I’ve added a button to facilitate this process and which will:
- Mark the order as consumed by setting the toggle to true
- Reset the focus on the view
I can still take a look at how to do something similar with 2 different tables and a canvas button but I can already tell you that to do this :
You’ll probably need a
Time-based automation but the smaller unit automations offer is
hour, so that would be a problem…
The free Scheduler pack might help though (but it’ll require a little bit more work) .
Small Add-On : Note that the rows in
[Outgoing Order Queue] will appear as they are in
[All Orders] as the index currently returns a visual position of a row in a table…
This means that if Bob has placed 3 orders before Sally (as it’s the case here), Bob will stay on top of
[Outgoing Order Queue] as long as his orders has not been all consumed that day.
After that will come Sally, then Aman, Maria … and finally again Bob who placed a last order after everybody else …
So maybe this workflow might need some adjustments depending on your use case …