Fruit Equipment Picker: Modifying a List of Rows from Another Table's Lookup Column

Here’s a doc that lets you plan the fruit equipment you’ll bring to fruit-related events you’re planning.

Buttons on the item rows let you add items to the event you’re currently working on.

The buttons work by adding the event to a lookup column on the item’s row. If the item has never been booked before, it puts the event in its column. If other events are already there, they are ListCombined. (For more details, see threads on ListCombine and referencing another table’s rows)

Toward the bottom of the Picker section though, you’ll see where I’m struggling: Bundles. If you’re like me, you frequently need to add all your knives to the list, so I made a bundle for that. Can’t get the formula to work though, because of the complexity of traversing Lists from two tables…

Similarly, I want to be able to clone the equipment list from a previous event that’s similar to the event I’m working on.

Any ideas for the formulas on my bundle add and clone buttons?

Hello Nick! I managed to get the button working with the next formula:

thisRow.Items.FormulaMap(If(CurrentValue.[Associated Events].contains([Event we are packing]), _NoOp(), ModifyRows([Gear List Master].Filter(Item=CurrentValue.ToText()).First(),[Associated Events],if(CurrentValue.[Associated Events].IsBlank(),[Event we are packing],[Associated Events].ListCombine([Event we are packing]).Unique()))))

PS. I see that you have almost finished the your file, but there is an easier way of arranging the file to make it easier to use and mantain (no need for these complicated formulas), If you are interested in this I can make a quick mockup to show you how

1 Like

I couldn’t contain and made it! :speak_no_evil:


Awesome, thanks so much @Saul_Garcia! Just on mobile now but am excited to check this out.

1 Like

Dear @Saul_Garcia,

Really impressive master piece :diamond_shape_with_a_dot_inside:, thanks giving the opportunity to learn from your skills :handshake:

1 Like

Dear @Nick_HE,

You made a great sample doc, including expressing your desired outcome :+1:

Hopeful this constructive way of asking support will be adapted by as many as possible in this community

1 Like

@Saul_Garcia thanks again for your great work on this. Finally got a chance to dig into it again.

Your Complex Formula Approach

This is great. Really helped me get my head around the proper way to iterate over the multiple nested lists.

The one part that makes me uncomfortable is the ToText() comparison - maybe I’m being paranoid but I like to reserve ToText() for display situations. I’ve modified it to ....Filter(myRowID = CurrentValue.myRowID).First() which also seems to work.

Reorganized Doc Approach

To summarize your strategy, you’re making join tables for Picked Gear and Items in Bundles.

At first I thought I could get away without these - after all, I was using formula columns to simplify things, so that Gear List had a column for all associated events, and Events had a column for all associated items, so I could reference whichever direction I needed.

But I see now how that’s not enough. Moving some of the complexity out of massive formulas like the one above, into columns of join tables, greatly simplifies interacting with that data elsewhere in the doc.

I suppose my reluctance was partly due to a vague sense that too many large tables is not as performant. My real-world use case involves hundreds of events per year and ~50 items per event. Do you know if Coda will perform ok with this kind of a table size? (perhaps it helps that I won’t be viewing those tables directly, just referencing them in formulas). [Edit: this page suggests your more atomic approach may run faster than complex formulas after all]

Other things I like:

  • Having formulas push buttons. E.g. for Bundles and Cloning, you don’t repeat the code for associating an item with an event - you just filter to the item you want, and click its Pick button.
  • Simplifying the filtering of “Currently Picked Gear” (which also simplifies the “Remove” button) - overall things like this make the doc more natively Coda-ish (to the point that you can sometimes even use GUI filtering and button actions, rather than writing formulas)
  • Better display of conflicting items and their associated events (including the ability to show the “Remove” button column, simplifying quick removal of conflicting items)

Overall, the takeaway seems to be don’t be afraid to break complex actions like this into visible intermediate steps in tables and column formulas.

Next Steps

I think this is really close. The last thing I want to do before beta testing this with my team is to make it multi-user, so that different people can plan different events simultaneously. I plan to replace the main “Event we are packing” control with a filtered table that has one row per user (as per this trick from Shishir)

Thanks again!!

P.S. @Jean_Pierre_Traets - I was just following the golden rule - ask questions in the format that you would like questions to be asked of you :slight_smile: It also helps that the Coda team has made embedding and sharing docs so fluid on the forum.

1 Like