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:
- 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.
- Sales person needs to be able to mark equipment as per his/her needs on this Table.
- 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.
- Equipment can be more than one unit of the same identical equipment.
- 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).
- 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?