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.
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…
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?
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.
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.
I will play around with that and see what works. I am struggling now with conditional formatting - the equation which seemed to work until recently for some reason stopped working for some columns but works for others. I will share it if I do not find a solution.
There is another question, however. It is regarding Event Status. Now it is used as a value from lookup table. Values can be Confirmed, Negotiating, Cancelled. I did try, however, to reword some of them and noticed that if I change them in the original table, values in the other tables do not change. No conditional formatting formulas. I wonder why? Probably because it is treated like a string. Let me give you an example:
I tried to change Negotiating into Negotiate in the Event Booking Status table. And the values in the already used table book_event did not change, though the options did.
More help is needed. I have managed to get 70 pieces of equipment in a table nicely. No issues there only time required. Works fast.
Now started working on conditionality and reservations numbers and really got stuck due to several issues:
Formula that gives list of available equipment is this (in bold - any chosen equipment):
Sequence(0,BungeeRunN.equip_quantity - (Filter(thisTable,CurrentValue.event_date=thisRow.event_date AND event_status=“Confirmed”).[_BungeeRunN (2)].Sum())) has the following shortcoming:
If event is confirmed and equipment for it is chosen not as a maximum possible quantity (for ex. BungeeRun max = 2, but we have chosen 1 for the event, then changes in the equipment become tricky as it considers itself already. Below is an example. Though it should allow 2 bungees to select (on that date there is no other confirmed BungeeRun except for itself. A workaround - select 0 and then it allows for selection of max number again (if there are no other events confirmed with BungeeRun)
Some error in Conditional Formatting equation or Conditional Formatting interpretation. When I have max number of equipment possible even for event which is confirmed and is the only event on that day, the cells fire and light up red - see example on the bottom row with today’s date:
Take some time to understand the difference between a Lookup column and a Select list column. I added a section to this doc for you, with instructions for you at the top.
Got you. Select does not react to any changes in the original value. Meanwhile Lookup is reflected immediately. However, if I add new row - both provide only list of new options. Was this the message?
