Selecting first row from a group

[Mike_Alvarez]

Hello Coda community. I have been working in Coda for ~2 months and am super impressed with the speed I can build apps but have been stuck on something for a couple of days and after reading and researching quite a few topics I have yet to solve this one, but I would think this would be a common problem.

Let’s say I have a list of orders in a table where each row represents the orders that are to be processed on each day but a person can have multiple orders in a day. I want to pull out the next set of orders for everyone on that day but ONLY process a single order for a user at a time… while I wait for a response from that user then I will process the next order.

Pretend it’s a coffee shop and I may have 3 coffees in a day but I only want to send one coffee to the person at a time, waiting for them to say they are finished with the previous.

Basically I want “Group By” syntax so I can only process the first row for each user and I am doing this via a formula in a button.

Thanks in advance.

[Pch]

Hi @Mike_Alvarez and Welcome to the Community !

I’m really too short on time to give any explanations at the moment (sorry ) but maybe this could help you …
(Look at the blue button, specifically)

I’ll do my best to come back later and add some info …
And/or at least, add descriptions and comments in the formulas

[Mike_Alvarez]

Thanks @Pch !! Reviewing what you put together.

[Mike_Alvarez]

This may help explain as well.

The button has a formula like the below and I know Unique() will not work by trying to understand how I get just the order that is up next for that customer.

[Pch]

Ah!

So you want to add to your [Outgoing Order Queue] the first order from [All Orders] from each client if the [Date to Order] is Today()?

E.g.: if today is the 4/25/2023 (from my side of the screen ) that would be 4 rows to add :

1. Bob → Eye Opener
2. Sally → Eye Opener
3. Aman → Eye Opener
4. Maria → Mid Day Pick Me Up

Is that it ?

[Mike_Alvarez]

Yes, exactly! Then once the order is processed (box is checked) it would find the next order for Bob, etc, etc… I would schedule that button to be clicked ever 30 min or so.

[Pch]

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 …

As [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 :

If(
  thisRow.[Order Consumed],
  0, 
  [All Orders].Filter(
    [Date to Order] = thisRow.[Date to Order] 
    AND
    Clients = thisRow.Clients 
    AND 
    [Order Consumed].Not()
    ).Find(thisRow)
)

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]

AND

• CurrentValue.Clients = thisRow.Clients

AND

• 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 1.

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) .

Of course 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:

1. Mark the order as consumed by setting the toggle to true
2. 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 :

@Mike_Alvarez

I would schedule that button to be clicked ever 30 min or so.

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 …

[Mike_Alvarez]

Thank you @Pch !! I really appreciate you spending so much time to help me with this. Champions like you make this a great platform.

This would be a pretty easy solve if I could define variables and just store the previous row and if the names match I just skip them until I find a new name. I tried this with a helper table but it didn’t work. I think the iterations in a loop were too quick for the helper table to update in time for the next loop iteration.

My solution is more “headless” so as long as things are running as planned there is no reason for a person to interact with the tables which is why I tried to fit the logic in a canvas button and I have an integration that looks for and processes any new rows in the outgoing queue (so not sure a filtered view would work but I can try). I have several of these which I can schedule and chain together. I’ve learned to look at buttons like functions in other languages so I can decompose my logic into discrete buttons and it is easy to understand.

I do like you idea of breaking down some of my logic and embedding into a row and taking advantage of thisRow to set the next order vs performing all of the logic in the canvas button.

I’ll continue to digest your last update and again, BIG thank you for your time spent!!

[Pch]

@Mike_Alvarez

Champions like you make this a great platform.

Oh my … ! Thank you ! I’m just trying my best !

Well, I still gave a shot at the canvas button idea but I still needed a button in the table [Outgoing Order Queue] (which this time, is a completely different table) not only to mark an order as consumed but to create the loop with the next order in the queue for a client already in the table …

(I’ll admit I used a button in the [Outgoing Order Queue] because it was easier but also because of time … So I didn’t really got the opportunity to look at how to do this using only canvas buttons )

The setup is similar to the one in my previous sample… So, there’s still an [Index - Next Order] doing the same thing as previously but there’s also an [Index - Next Client] which determines who comes first based on the their current position in the table (this is used later) .

And below the table [All Orders] you’ll find again a simple date canvas control simulating Today() for test purposes …

Next to it, you’ll find a canvas button and last bu not least, the table [Outgoing Order Queue].

The canvas button only adds, when enabled, the very first order of each client for the date selected in the date canvas control … or potential missing ones…

I.e.: (a) brand new first order(s) that would have been added mid-day for (a) client(s) not yet in the table [Outgoing Order Queue].
(I’ve voluntarily left a missing order so you could see what I mean )

So, when enabled and pushed, the canvas button pin-point the right rows in the table [All Orders] and adds their references to a Relation field called [All Orders - Current] in the table [Outgoing Order Queue]. (So, the table [All Orders] is now linked to the table [Outgoing Order Queue])

To respect the queue of clients created in [All Orders] by the field [Index - Next Client], I’ve added the related column [Index - Next Client] and used it to sort the table [Outgoing Order Queue].

The other fields Clients and Products are also related columns added through the relation field [All Orders - Current].

Then, there’s another relation field [All Orders - Next] which, depending on the order in the field [All Orders - Current], the client and the date selected in the canvas control, retrieves the upcoming order for the client, if any…

The value in this field is used by the button [Mark as...] to either update the value in [All Orders - Current] with the next one for the client in the row or delete the row entirely if there is no order left for that client on that day, after marking the order that was previously sitting in [All Orders - Current] as consumed ( ).
And because the table is sorted, its very last action, as in my previous sample, is to “reset” the focus on the table [Outgoing Order Queue].

And this is pretty much how far I could go with the time I had …

This is just a probably imperfect idea that might need some adjustments … but I hope this helps a little .