Equipment status table / matrix with two rows of column headings

[Vytenis]

I am trying to create a table of equipment status for different events. The events are described uniquely by two attributes: Date & Location. For example, I can have three events on the same day but each of them would be in a different location. Event also have status (Negotiating, Confirmed, Cancelled).

In the first column, as headers of rows, I have list of equipment. In column headers I have Dates and above them - Locations, and above them - Events Status. This way each column is a unique combination of Date & Location plus I see Event Status.

The table is filled with two types of information:

• number of equipment booked/reserved for that day (if the cell is empty, it means the equipment is available)
• color (red, amber, green) indicating booking status (available, reserved but not booked, booked). Equipment booking status is determined by Event status

IF Event Status = Confirmed THEN Equipment Booking status = Red for the equipment that is selected for the event. The empty cells are green indicating that equipment is available.
IF Event Status = Negotiating THEN Equipment Booking status = Amber for the equipment that is selected for the event. The empty cells are green indicating that equipment is available.
IF Event Status = Cancelled THEN all equipment is erased and all cells light up Green.

Here is the EXCEL sheet (Dropbox link)

There is also a further level to this sheet - ability to check whether total number of confirmed equipment on a given day does not exceed total number of available equipment. But I am happy to explore such solution at a later stage.

What I wish to achieve/learn - how to code such table in Coda. If not everything is possible - what is then possible.

If needed, I would gladly rethink entire layout. The aim in the end is to manage equipment rental properly - with as much automation as possible in order to save time, and minimize copy-paste mistakes.

I have also discussed a broader topic where this table comes from here: Table with multiple rows and columns that are frozen - #9 by Jean_Pierre_Traets, but I think the topic was too broad to start with and I will try to go to a smaller parts of the problem.

Thank you to everyone who has helped me so far and everyone who will pitch in more.

[Ander]

@Vytenis

Here’s one possible approach.

Scroll down the section to the [events] table.

Play around with it and study the formulas. If you have specific questions, ask them.

It’s rough, but maybe it will keep you moving forward.

There are some buttons/automations I would probably add to this once the basic architecture is settled.

[Vytenis]

Dear @Ander, Awesome! Thanks a million. Let me dig in more. One important question, however, - is this scalable? For example, our real inventory could be 50 or even 100+ different items. And we run 300+ events a year.

[Ander]

@Vytenis

That’s what I figured. That’s why I flipped your axes. I have the same issue in two of my projects. There have been lots of requests for more programmatic control of columns for these types of situations and others. @mallika @Krunal_Sheth

I suppose the data structure could be broken down into smaller chunks. But I almost always want maximum data available with minimal navigation, especially when I’m on the phone with a prospect trying to close a sale.

That said, I can tell you that I have built mine and so far they are working for me. 300 rows/yr should be fine. 50-100 column pairs (100-200 columns total) might have some performance issues in table view. But maybe not since your data are small numeric values and formulas. But then again, that much conditional formatting and grouping does create performance issues at times, especially with that many columns. You’ll want to test it before doing a big build out. @Glenn_Jaume

There are also some ways to work around these performance issues using views and filters. In your use case I would think these would work pretty well.

So I’d recommend sticking with it until you get your business logic working how you want it, at which point I’m confident you can figure out ways to scale it to your satisfaction, assuming it doesn’t just scale right out of the gate.

[Jean_Pierre_Traets]

Dear @Ander,

Thank you sharing your approach

In an effort to support, I shared the below, but yours is many steps ahead. I wil go to explore a copy of your input to develop my way of thinking and learning

As you mentioned also the business logic is an important factor, better calling it the foundation of each reaction in Coda.

Kind regards,
//JP

[Vytenis]

Dear @Ander and @Jean_Pierre_Traets. Enormous thanks to both of you. I am especially glad to hear that I am not the only one thinking of this issue. I will dedicate my day tomorrow to review all in detail, what has been suggested, but meanwhile I wanted to comment on the Jean’s Q-1:

• indeed there is always a balance of reserving the right amount of equipment (during negotiation stage) - if too little, some equipment might not be rented out, if too much - then it could overlap with other events. My philosophy is the following: over reserve and use “first confirms, first gets” principle. Thus, I leave the nuances of how much to oversell not to software but to the sales people, who in the manageable inventory volume as we have, usually do very well in balancing this.
  @Jean_Pierre_Traets, hopefully this goes in support of your thesis. As for the Event “Quote” stage - yes, it is the same as “Negotiation” in my case. “Confirmed” means they given verbal or written go for contracting. There are also stages of “Contract issued” and “Contract signed” but I tried to keep them out at first. My aim is to learn the principles of coding it first and then add new layers of complexity and (conditional) formatting in the final end.

I also very much support @Ander’s view [quote=“Ander, post:4, topic:6173”]
But I almost always want maximum data available with minimal navigation, especially when I’m on the phone with a prospect trying to close a sale.[/quote] - it is exactly how I feel and think about it. That is why having Google Spreadsheet was my first go (not some complex CRM). But I am stuck with Google Spreadsheet as it brings more limitations that I like, and I hope to address those with Coda.

[Vytenis]

Dear @Ander,

I have been investigating your tables but I failed to replicate some of them. Especially this part: Sequence(0,cube.inventory - (Filter(thisTable,CurrentValue.date=thisRow.date AND [event status]=Confirmed).cube.Sum() -cube) ).

Any time I tried to write cube.inventory, I am always corrected to CubeT.inventory and CubeT refers to the column “cube” not to the particular cell in the equip inventory you had made. Probably there is something I do not understand in grammar. Here what I get:

As for logic, I plan to do the following: “Event Booking” table where I will enter all the requirements from the clients and it will have one column (multi select list) for equipment but it should be filled automatically from the second table “Equipment Booking”, where I would have only Event name (selected from “Event Booking” by lookup) and Date (filled automatically) and then full attention to the Equipment as per your suggestion. Also, I would like to keep equip inventory table in a separate sheet - to keep Equipment Booking as clean as possible. What do you think?

[Ander]

@Vytenis

Any time I tried to write cube.inventory, I am always corrected to…

Right @Vytenis, I meant to note that. I believe the terminology for this is “@ref”, because it accesses a specific row directly via the “@” prefix.

Remember that the “name” of a row is whatever is in the Display column.

In the case of cube.inventory, “cube” is in the Display column. So you can directly access that row by typing “@cube”. And once you’ve accessed the row, you just access columns in that row using dot access: “@cube.inventory”.

@Juanmata I meant to note this for you also. This is how I referenced the VAT rate: type “@” followed by “VAT rate” (the row, not the table), followed by “.rate” for the column.

NOTE: If you’re already in a formula, you can type “@” to access rows directly. If not, you must first type “=” in order to begin a formula.

@Vytenis, Let me return to your business logic question later when I have more time.

[Krunal_Sheth]

FWIW my 2c - Subtables would work out really well here, In events table, you could create a formulaic column that pull out all the equipments for that Events. and then render it in layout with subtable

Alsp, havent had a chance to look at schema of any of the document. but just reading through original description, I would think creating a join table would make it bit easier to build on. so have an Event, Equipment tables and then another join table Event-Equipment that tracks required number of a specific equipment for an event. This way inventory management would be much easier to implement. (its many-to-many relationship here, so it always helps to have a join table FYI @jaime )

p.s Thank you @Ander and @Jean_Pierre_Traets for helping other community members. I appreciate how you guys are always there to help other members and take time to pass feedback to us.

[Ander]

@Vytenis

Regarding your logic plan, I’m having trouble following it written out this way. Maybe you could throw together a skeleton version of what you have in mind and either share a screenshot or the doc itself (even better).

[Vytenis]

Let me start with the schema. Here is my suggestion to look at the problem. I tried to reflect the real world situation the way my business functions now and how I imagine it could/should be coded in/automated. There are possibilities for two things: process improvement (not the scope of his exercise yet) and better automation (this is where my focus is and Coda is a potential solution).

Step 1. Sales process - Client’s requirements.
Our sales people negotiate our rental projects with potential customers. Sales person receives requirements that have to be captured. They include: EventDate, Times (From, Till), Location, Client’s details, Type of event, Language(s), Additional Remarks, Number of Participants. ; Then our sales people using this information Go to Equipment List, check what is available (Equipment List), create a quote that matches Client’s requirements, and make Client an offer (Price), Determine the number of our people needed (Crew), checks Event Booking Status. If the client confirms, then we start project by assigning Responsible person (from within the Company).

To capture this process, I made a mindmap. On the left - things that we get from Client. On the right - things that we create ourselves (of course, in close response of Client’s need).

In Coda this table looks like this. Please note my remarks, for easier understanding where info comes by hand, where it is fed from other tables:

What is not seen - some columns that belong to Client (Representative, Phone number) are hidden. They can quickly be looked up by hovering on the Client entry.

The most complex here is the Equipment List Column. It should be filled automatically from Equipment Bookings Table (see Step 2 below).

Step 2: Offer (Equipment + Price). Though mostly covered what is needed in Step 1, the details of logic here are the following:

1. Sales person needs to see all equipment Reserved and Available for the given day. For that I envision a Table with Dates and Equipment Status for each of equipment.
2. Sales person needs to be able to mark equipment as per his/her needs on this Table.
3. Sales person needs to be able to calculate the rental cost for his/her combination of equipment. This cost is only suggestive. The Sales person has full right and responsibility to adjust it as context requires. Plus final price is always more than just equipment rental price.
4. Equipment can be more than one unit of the same identical equipment.
5. Equipment is measured in pieces, though in the future envision some more complex cases for some equipment which is rented not by pieces by but other measurements. Ex: obstacle course one piece is 9 m. long. We rent certain length, like 45 m. The client things of 45 meters, while we need to think in terms of 5 pieces (45/9).
6. Rules for Equipment status:
   6.1. Equipment is by default Available
   6.2. Equipment is Reserved when quoted to the client and Event Booking Status is Negotiating.
   6.3. Equipment is Confirmed when Event Booking Status is Confirmed.
   6.4. Equipment is back to Available if Event Booking Status is Cancelled.
   6.5. Decision on how much Equipment is Reserved is made by Sales Person. Over Reserving can be done. For example the same piece can be Reserved for two events on the same day.
   6.6. Equipment is not rented twice in the same day.
   6.7. Overbooking of Equipment with Status Confirmed is not allowed. For ex. once the equipment is Confirmed, the Reserved equipment needs to be highlighted/notified or marked somehow else for Sales Person to make decision what to do next.

The best solution so far to this was offered by @Ander. I am in the process of trying to implement it. The biggest challenge I see if the scale - if one grows to 100+ pieces of Equipment, it may become unwieldy on the screen. Secondly, now it takes two columns per each equipment (Equipment piece, and How many remain). That scales 2x with each new Equipment piece.

Here is @Ander’s table screenshot. I will modify it, however, to include slightly different format for Event Names, and will get rid of location column. The focus is on Date, EventName and then Equipment and its status.

I am also curious to hear @Krunal_Sheth suggestion on Joining tables. Not sure how that is done in practice.

Step 3. Assuming that event(s) are confirmed, we make loading sheets for our warehouse. They would contain list of Equipment to load and relevant info such as Event name, Date, Location, Departure time, Expected Return, Reponsible person, Format:

Step 4. Operation Planning list is generated where we see a view which shows the following:
Info about event /(Date, Client, Location, Link to the location, Travel Time, Departure from Warehouse time, Event starting time, Planned return to Warehouse, Duration of the event, Number of transpor, Crew number, Event Format, Responsible person, Remarks)

If I manage to get this done, then I have plans for few more things:

• invoicing and finance tracking
• deeper CRM
• maybe event Contract generation

Thank you once again to everyone who is helping me! Maybe it will be useful for others when we make the final product?

Vytenis

[Vytenis]

Dear @Ander, I am stuck with one piece of your proposed solution - the equation which determines how many pieces of a particular equipment it is possible to select. Your equation looks like this:

However, when I make my own version, I fail to get results. The only explanation I can see is that Cube has Numerical value, mine has Text value. Do you have any ideas why is it so?

[Ander]

@Vytenis

Can’t tell from your screenshot – are you saying that the dropdown select menu for the Cube column is empty?

(Anyway, I’m working on a better way to do this.)

[Vytenis]

Yes, the column is empty for some inexplicable reason.

Vytenis

[Vytenis]

Here is my Document I am working on:

[Ander]

@Vytenis @mallika

Ok, I just reproduced what you’re experiencing. This is a weird behavior that I would consider buggy. I ran into it when I was building this page for you the other day, and then forgot about it. For some reason the Select formula modal is not recognizing the column as a number column, but instead defaulting to text column, even when the user has previously configured the column to be a number column via the column config UI.

I just verified that this formula works: sequence(0,cube.inventory - Filter(thisTable,date=thisRow.date).cube4.Sum() + cube4,1)

To get around the buggy behavior, follow this sequence:

1. Create a new column
2. Enter a number into any cell of the column.
3. In the Select list modal, in the “Value for New Rows” input, type 0 (zero).
4. After clicking away from that input it will give you an option to apply to all empty rows or something like that. Do this.
5. Then enter the Select list formula.
6. It should now work.

NOTE: I suspect that #2 registers the column as a “number” column type in the back end in a way that the Select list modal can read properly, but that’s just a guess. Could also be #3 or #4, but I didn’t isolate.

[Vytenis]

Dear @Ander,

THANK YOU. Followed your instructions to squish this bug. Things started to work until I tried to play more and suddenly the weirdest thing happened - every time I select new value for equipment, maximum would increase.

Here is my equation. Cube.quantity is 4.

Then I select from the list 4

Then I see the lookup value and … the maximum is 8 now ;-(

I keep selecting more and the maximum value goes larger and larger… What’s going on Totally confused.

Tried again in a new table - same mistake.

[Ander]

@Vytenis

Check this out instead.
Read the Notes first.
Then play around with it.
Focus on how it serves date-based inventory availability to a Sales Person.

[Krunal_Sheth]

Hello,

I havent had a chance to complete this yet, but here’s a Work in Progress. Open it in Coda to try it out. Today’s view shows ‘Events’ planned for Today and ‘Equipments’ needed for today.

sales section shows all Events as well as allows a creation of a new event. you will add equipments needed for a sales event using the subtable while creating a new event.

Thanks.

[Vytenis]

Dear @Ander,

This is very impressive work. I also think that you have rightly written that the trickiest part is to work our inventory selection and reservation piece.

I could not find any bugs or non working parts thought I tried extensively to play with it.

Also, Screen Option 1 would be probably the best solution. Adding and coding in 100 columns is not an issue.

I also have one bigger and one smaller challenge with this solution.

1. It assumes that no over reservation of equipment are possible! That’s an issue. For about 10-15% of our equipment we over reserve it. It means when we have two cubes, we can reserve 3 or even more during sales process and we serve it with First Confirmed First Booked approach. Can you think of such a solution for over reserving? This requirement is rather important because without it we would not have any ability to capture that information properly and could loose clients.

2. One more nuance - in requested equip types Sales Person does not capture number of equipment needed. That’s in essence not a major issue since he would keep the numbers required in his/her mind. However, if there is any way to capture that information elegantly, it would be great. The case when two or more pieces of the same type are hired is probably around 40-50%. But as I mentioned, this is not critically important element because it is possible to capture quantity needed requirements elsewhere and use it as a note rather an object in calculations.

Thank you a million for sticking with me and helping to push this project further.