Equipment status table / matrix with two rows of column headings

@Vytenis

I ungrouped them for some reason that I don’t even remember, and just forgot to group them back.

However, you can add new rows when grouped.

The blue [+] button for adding new rows is not available. @mallika

But you can:

  1. Right mouse click on a row > insert new above/below.

  2. Click on the last row in a group > hit enter > adds new row to group.

NOTE: I was unable to enter a date in the grouped date column, but I was able to open the row modal (Ctrl+Shift+Enter) and enter the date there just fine. Grouped columns have some limitations yet to reach parity (conditional formatting, scale icons, sticky column headers, etc). I assume this date input issue falls within this known area. @mallika

You might consider taking a few minutes to practice entering new events with:

  1. the same dates,

  2. different dates.

I found the user experience to be solid, once I got the hang of it.

@Vytenis

It’s not very scalable, on the development side. I have some formulas that took me over an hour to build. :stuck_out_tongue_winking_eye:

This is another example where it would be powerful to:

  1. Have programmatic access to columns.

  2. Have some minimal text editing tooling in the formula modal for developing large formulas.

Programmatic access to columns would be a MASSIVE differentiator over spreadsheets. I have MANY use cases for this.

@alden @Krunal_Sheth @mallika

Nonetheless @Vytenis , it’s scalable for the end users, once it’s in place.

NOTE: You may run into performance issues when building a formula based off of 100 columns, or you may not. I haven’t tested that many columns in a formula… yet.

Dear @Ander

Was away sick in bed, and finally back exploring Coda again. Thank you for having taken time to answer all the questions. Here is my first reaction to your part on adding rows:

  1. Inserting new rows works just like you have described.
  2. For row modal you probably meant CTRL+SHIFT+SpaceBar? Then it works well, just like you described. I am still getting used to the fact that there is no Save button :wink: Sequence then would be for grouped columns to: i) add row; ii) go to row modal and enter date there.
  3. [quote=“Ander, post:29, topic:6173”]
    Grouped columns have some limitations yet to reach parity (conditional formatting, scale icons, sticky column headers, etc).
    [/quote]
  • this is slightly disturbing. Do I understand correctly that grouped columns do not have Conditional filters working properly on them?

I have also noticed with Conditional Formatting that sequence of the conditions matters (that’s logical). However, there is no apparent way to change the condition sequence except for writing them again or duplicating and deleting the older ones. That could be improved in my opinion.

Furthermore, copying and pasting formulas also does not work well. If the formula has reference, then usually I have to rewrite it again. Also sometimes parts of formula like thisrow.xxx - change to xxx and “thisrow” disappear, even though it is needed for the formula to work.

One more idea - just do not know to whom I should suggest it. Conditional formatting often block the view of the rows that I am testing whether it works. Here is an example: https://i.imgur.com/QlXxq0F.png. Why not to have the formula window above the table, not on top of rows? Or have an ability to move it?

Dear @Ander and @Vytenis,

Impressed on the tool you are developing and hopeful soon it will meet the business needs.

I got an idea to might be useful in your use case, it came forward from this post:

The technique used by @shishir, solving step my step the challenge is very helpful and makes it more easy to understand

In my use case I need the months to be extracted, in your use case you could extract the days or hours equipment is rented out and offering certain benefits to the clients that can drive your business.

Example: Day price is X Euro, when renting more then Y days you get Z% discount on the days >= 3
Or repeating clients, to receive a discount after the annual sum of X days rented equipement.

Success and have a good week ahead,
//JP

PS. I _have been in Lithuania several times inspecting facilities producing apparel and had the chance to see the beautiful countryside and Vilnius.

1 Like

Dear @Ander,

I have double checked again the button - it does make better sense now.

I intend to have equipment_reservation table in a separate sheet.

  • Sales person could use two monitors and have two instances of Coda opened. Capture information what is given by client and immediately have a look at the equipment status on that day just in case he/she needs that.
  • Sales person could learn to filter quickly the view to see only dates he/she is interested.

Some nuances to clarify:

  • Button does not have default option of the event which I have just created? I always have to select in row modale from the list?
  • Event_name from Select to Lookup from Table was done why? To simplify the action of Button?
  • Can we lock the table equip_reservations that only rows can be added by Button (not manually in the table directly)? This is to avoid accidental additions? Maybe that’s too excessive?

As for showing all the equipment in event_booking table:

  • equipment_reservation_row - is required in order to simplify/shorter equation in event_equipment?
  • is there are way to prepare equation in external editor and then just paste it? If so, I could prepare a long equation much faster

Not related. What does this mean in Coda discussion? Some money? :wink:

@Vytenis

I think your schema can be improved. I did some work on it over the weekend, but ran out of time before I could solve it.

I read through your recent questions. I’ll address them later, when I have more time.

$.02 normally means ‘that’s my two cents’ worth’, :slight_smile: not sure whether you’re a native English speaker, but it’s an idiom which means ‘here is my contribution (which may be of little value, but it is the best I can do)’.

3 Likes

I’m not a native speaker but I’m familiar with this idiom. Well done! I thought it is a Coda feature, maybe some future monetization idea :wink:

1 Like

Hi @Ander, have you by chance got a chance to put down the answers to the questions. I very much appreciate that and feel sligtly embarrased to push as it is all done here on volunteer basis :wink:

And one more question regarding lists. I see that it is possible to make Column in List format using values from another Table in two ways: by choosing List Format and referring Table in Filter part:

Or by choosing Lookup from Table option.

Is any of them better than the other for a particular reason? Like speed, use for other manipulations, etc?

@Vytenis

I believe grouped columns are subject to filters.

You can drag each conditional formatting rule up or down to establish its priority relative to the other conditional formatting rules.

+1

That was the only way I could get it to work.

Whenever it’s an option, I always prefer a direct Lookup to Select list. In this case, it makes it easy to auto-populate columns in the button-added row. Look at the formula in event_status2 to see how.

I haven’t seen anything about locking tables.

It makes it easier to build that big old honking concat formula in event_equipment3.

Yes, you can. (Not everything can be pasted though, like @refs. Those must be typed into the formula editor.)

I’d like an official answer to this question also. :grinning:

1 Like

Dear @Ander,

Thank you for the answers. They are great even if some are left for Coda people to answer :wink:

I also have some issue with your proposed button - it stopped working. Maybe Coda has changed something… ;-(

When I click the button, I get empty Row modale. Then I select the event and it gets this error messages:

image

image

Any idea what’s going on?

@Ander - you rock!!

I am reading through this thread and will work with the team to get back to you on answers.

1 Like

@Vytenis

No idea. I tested it a few times and didn’t have any problems.

However, I notice in your screenshot that you’re populating the event_name field. Unless you modified the doc, you actually want to select a value for event_name2. But I tested that also, and didn’t get your error message, so… :question:

I have found mistakes and the table now works. I am building a full size version now. Will see how the sheet will handle it all.

Dear @Ander,

It just dawned to me something very important - by using the solution that we have decided on (with you incredible help), we in fact loose relationship between the equipment we reserve and equipment table we have. Specifically from your example: column headings of “equip_reservations” table such as _CubeN (4), _SUP(20) etc. have no relationship with the Equipment in the table “Equipment List”. However, there should be one since it is the same equipment only named for convenience with the quantity in the brackets. Thus Cube = _CubeN(4).

It would be a shame to loose this relationship for later analysis. For example, if I wanted to check how many Cubes have been used in 2018, I will be working with _CubeN(4) but will not be able to quickly determine what type _CubeN(4) is (=Inflatable) - I will need to build manually relationship between the entities.

Do you have any suggestion how to manage that? Maybe add another row in Equipment List which lists the equivalent Column-Heading-Name for each Equipment?

Vytenis

1 Like

A separate question - is there a way to format Column heading with colors?

@Vytenis

Yes, this is an issue.

Off the top of my head, I would just set up column pairs, like this, where the second column in the pair is an @ref to the equip object in equip_list. These secondary columns can then be hidden and used for running queries against your transactions for reporting and analysis.

CubeN_2019-03-05_0821

If you mean “column” rather than “row,” then yes, this would be an interesting approach. You could @ref the column in equip_reservations to establish a data linkage between that column and the equipment row in equip_list. That would require a level of programmatic access to columns which we don’t yet have. The suggestion above is the inverse of this. Rather than adding a column @ref to a row (not yet possible), instead add a row @ref to a column that is created for the purpose of holding that @ref.

NOTE: This is all off the top of my head. I haven’t tested any of this, much less used it in my own projects. There might be a better solution, and if so, I’d like to learn it.

Not that I know of.