Table with multiple rows and columns that are frozen

Trying to adapt my ongoing equipment rental project for which I use Google Spreadsheets. The biggest challenge I face now is the inability (or is it so?) for CODA to have frozen rows/columns. Maybe there is a clever way to overcome that?

The idea is the following:

  • I have a complex table where I record all the key requirements (date, place, contact info etc) by the clients - one column per event. I also track the status client order in the same column (ex pending, confirmed, contract signed). All this info is in the first ten or so rows and is frozen from scrolling. Then I have a section on equipment - and I place a number which reflects how many pieces of equipment are reserved. An example of such excel is here (Dropbox link)..
  • I also use some complimentary tables to enhance the quality of the table: list of locations for the events with Google Maps hyperlinks and travel time needed; Crew list - used for OPS Planning Sheet and Loading Sheet. All that is visible in the example.

This work requires a lot manual copy paste and I am very keen to built an automated solution. The idea would be that I enter information only once and for the rest of time it is prefilled automatically or uses lists to be selectable. Coda.io is potentially a right tool to solve that.

Any help to make a breakthrough here would be greatly appreciated. The final results would allow me to build a nice ERM (enterprise resource management) solution that suits event equipment rental businesses.

1 Like

Dear @Vytenis,

I suggest to study the below template for some inspiration :bulb:

Instead of frozen rows, in Code you can create different views from one and the same table to focus on the info of concern.

I also recommend you to check this section:

Enjoy,
//JP

2 Likes

You will find Coda much easier than spreadsheets for this use case scenario, but it will need some getting used to. :slight_smile:

As Jean_Pierre_Traets suggested, check the templates and guides, they answer most of your questions.

As a snapshot of what you could do, you can create separate tables for things like: statuses, clients, event types, attributes, etc. Then all these tables feed into your main table. This gives you an auto-complete functionality but also data clarity. And if you need to change the name of a client, let’s say, you only change it in one place, even if it is shown in many.

I am describing the Star Schema from this video: https://coda.io/learn/schema-design/Schema-2-Star

Good luck and have fun!

1 Like

@Jean_Pierre_Traets and @Michael_RnW. Thanks for the insights. After having read the guides and seeing the examples, I do see that I need to build a schema first for my problem understanding. I will revert back with my draft once I make it. It hopefully should not take long.

At the same time I wonder if there is any substitute for frozen rows/columns option in Coda? Filters and views are not always the greatest way to play around.

@Michael_RnW, @Jean_Pierre_Traets and anyone else, who wishes to help. I have followed your advice and have built something to start with: https://coda.io/d/Company-operations_dBxZ1QWhApg/_sukGJ

Master view (Bookings) is the Section where I would like my magic to take place but I am stuck at equipment reservation step (last column). The idea in the last column (Equipment List) is to be able to see which equipment is available on the desired date.

For example: I have two events planned for 2019-02-13. When I select equipment, I have now selected CUBE for both. But since there is only one CUBE, I should be able to do one of the following:

  • not be able to select the CUBE once it is already selected for another event for the same date
  • select CUBE for both but mark them in special way to show that they are reserved.

There is another option - leave to human operator to check that there are no duplicates on the same date. But then I would need some help - a way to see quickly what equipment is reserved and whatis free on the given day. For that I think calendar view should be best but I did not manage to get it working either (See Equipment booking (Calendar)). I only see dates on the calendar and not the list of equipment reserved nor free.

Thank you everyone again for any help.

1 Like

@Vytenis

Would something like this help you at all? :point_down:

1 Like

@Vytenis

I played with this some more for my own purposes.

equip_select-1_2019-02-06_1825 equip_select-2_2019-02-06_1829

3 Likes

@Ander - what is the formula in the “row with matching dates”? I briefly saw this image , but when I tried to replicate - I could not write “CurrentValue” - it said to me this: image . Any comments?

Dear @Vytenis,

As you can see I added a date column from when the “value” will be available and an “available column” with the following formula If(Avail_Date<=Today(),true,false).
Then create a view of the “select value” table, where you filter Available=true

IMPORTANT: select the “view of Select values” table to choose from!

This should be the first step towards implementing this user case.

Special thanks to @Ander, building the formulas :beers:

Unfortunattely I can not assign more time for the moment, but for sure I will do my best to support on the development of this interesting user case :sunglasses:

Kind regards,
//JP

2 Likes

Dear @Ander, @Jean_Pierre_Traets, @Michael_RnW - thank you very much for your inputs. Those are great. Got me on some good track. But I believe they will be helpful at the stage where equipment reservation will need to be compared against what is available and what is not. However, at this stage I am still struggling with the basic concept of marking equipment reserved for specific events. In order to detach that problem, I have created a hopefully “simpler” thread with much simple Excel file. If any of you or anyone else has time to pitch in some ideas, I would be super thankful. Here is the thread: Equipment status table / matrix with two rows of column headings

1 Like