Dear @Krunal, this is very interesting. I have tried it, and especially liked that you can enter equipment with a button click. I did have difficulty following full process logic, so please allow me to check that with you: I go to Sales Sheet and enter a new Event. Fill all the information including Equipment requirement. At the moment the logic, as I see, does not account for events that have equipment confirmed already on that day?
Then what is the purpose of Events - Equipment Table? I see that I can modify it.
Without contemplating the logic too deeply or doing any testing, I would think that there would be two logic paths representing two different stages of the sales process. This would probably involve distinct sets of columns/formulas for each stage for holding the distinct logic sets. You could have as many stages as you want, but they would each have their own logic paths.
EXAMPLE
Sales Stage 1 - Reservations. This logic would apply to events of type āReservedā and allow for overbooking.
Sales Stage 2 - Confirmed. An event going from type āReservedā to āConfirmedā would trigger this logic and not allow for overbooking.
You might require some automations as an event moves through the different stages of the sales process, or that might not be necessary. TBD.
Capture number of equipment needed.
You could just use a text Notes field for this (simple and free, but limited functionality).
Or, if you need to run calcs on it, you could have a sub-table here that you use for building quick estimates and such (more moving pieces, but more functional).
Just depends on your specific needs for this field.
Dear @Ander. I have been thinking a while and decided that in the end it is best to stick to the modified version of your first idea. The logic should be this:
we have columns for equipment. No need for columns of availability.
in the columns of availability we choose the number of equipment (it is given as a list of numbers from 0 to Max-quantity minus Booked-quantity
Any number of equipment can be chosen from a given list as long as the status of the event is Negotiating
When Event status becomes Confirmed, then all the equipment in that row should become confirmed (colored in GREEN)
Any equipment which is overreserved (status = Negotiating) should be highlighted RED.
It is the responsibility of the seller to troubleshoot the overbooking.
Going with unique codes can be complicated because there may be as many as 20 or more pieces of the same equiment.
For that I have made a table following your ideas. However, I got stuck with the logic somewhat regarding making cells to become colored properly. Green is easy āConfirmedā but Red is tricky. Could you please help me to troubleshoot it. Here is the table:
And one more question - is there any way to put Equipment Quanity next to the Name of Equipment. For ex. instead of _CubeN, I would like to have _CubeN (4). I can do that by hand but it would be nice to have it automated.
See if this works for you: _CubeN > CubeN.equip_quantity - Filter(equip_reservations, event_date = event_date AND event_status = Confirmed)._CubeN.Sum()
You can create a third column in the [equip_list] table with a formula like this: Concatenate(equip_name," (",equip_quantity,")")
Is that what you mean?
(If you want it to be your Display column, remember to set it as such.)
Dear @Ander, it worked well from what I see. I am trying to push now the coding further:
I have introduced the table event_booking, where all the clientās info would be saved. I kept it short just to build the proof of concept. Table equip_reservations references most of essential information from event_booking (date, name, event status).
Ideally I would like that every time I add a row to event_booking, a new row would appear on equip_reservations with prefilled information (date, name, status). Is there a way to do that? Now I use manual approach with lookup values. This would time from coding next rows and making a mistake of doubling info.
In the event_booking there is a column event_equipment. I would like to be the info taken from the list of all reserved or confirmed equipment and listed in a list with format like Cube(3), Bunge(1), SUP (16). Is it possible to do so?
The button solution below isnāt 100% automated. Your sales agents will still have to make one selection during the process of creating the new row. There are other options here, but Iād want to think through their pros/cons, and Iām out of time for now.
I restructured your schema to make this easy. Study the green columns.
Dear @Ander, thank you once again. I have analyzed the proposed solution but to be frank, from what I see, - it takes more effort than alternative - manually add new row and select event name. Therefore I think I should then focus more on optimizing that and finding a way to represent reserved/booked equipment properly. Here is the way I see it:
1.1 Add new row and have selection only from the list of the remaining (unused options). By this I mean that once I create new row in equip_reservations and choose event_name from the list - I would like to see ONLY the options of events that have not been used in the table āequip_reservationsā.
1.2. Why canāt I add new row when event_dates are grouped? Maybe I could use some keyboard shortcut?
Copy equipment with from equip_reservations to event_booking with the format: equip1:quant_equip1 [return] equip2: quant_equip2, [return] etc. but with the logic that if quant_equip = 0, then that row is not shown. I managed to do that for one equip with the formula: if(equip_reservations.Filter(thisRow.event_name=event_name).[_CubeN (4)]>0,Concatenate(āCube:ā,equip_reservations.Filter(thisRow.event_name=event_name).[_CubeN (4)]),"") but that is not scalable to 100 pieces of equipment Or is it scalable?
Itās not so quick and easy. Your sales person will have to:
Navigate to the second table (Where is it located in your doc? Same section? Different section? Top of section? Bottom of section?)
Scroll to the bottom of that table (How many rows are in the table? Scrolling through tons of rows isnāt instantaneous.)
During all of this, retain in their memory exactly what event they are to create when they finally arrive at their destination to add a new row.
Is the prospective client on the phone during all of this?
row button
The button is right in the visual workflow of the sales person, providing a very visual check for the sales person that this task remains to be done.
No navigating anywhere to perform the task, just click the button which is right there already.
No need to remember what needs to be entered, because that information is still right there on the screen, visible to the left of the modal.
During a phone call, the sales person is not limited to only entering the event name. Within the modal, they can enter any value that exists as a column on the new row.
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:
Right mouse click on a row > insert new above/below.
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:
the same dates,
different dates.
I found the user experience to be solid, once I got the hang of it.
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.
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:
Inserting new rows works just like you have described.
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 Sequence then would be for grouped columns to: i) add row; ii) go to row modal and enter date there.
[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?
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.
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?
$.02 normally means āthatās my two centsā worthā, 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)ā.
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
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?
Or is it scalable?
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)ā.
