Forum Discussion
MarkShnier__You
Qrew Champion
Sorry, my mistake. I actually forgot to put the formula field on the reference side of the relationship.
So in these cases where the reference field is invalid either because it is referring to a date field or a look up field the solution is to make a scalar data entry field type and have an automation to maintain it to always equal the value of the problem field.
Then I safety net the automation by having an overnight saved table to table copy run by an automation to clean up any possible problems that the live automation did not attach.
Then I make a safety net report subscription to look for anything that the overnight safety net Automation didn't catch. So like a belt, and suspenders, and then more suspenders just to be sure.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
So in these cases where the reference field is invalid either because it is referring to a date field or a look up field the solution is to make a scalar data entry field type and have an automation to maintain it to always equal the value of the problem field.
Then I safety net the automation by having an overnight saved table to table copy run by an automation to clean up any possible problems that the live automation did not attach.
Then I make a safety net report subscription to look for anything that the overnight safety net Automation didn't catch. So like a belt, and suspenders, and then more suspenders just to be sure.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
AndrewNelson
4 years agoQrew Cadet
Yikes. More levels of complexity on top of what was already a complex solution to (what should be) a relatively simple problem. Belt, Suspenders, and extra Suspenders to hold up my clown pants which are then themselves holding up another, smaller pair of pants. Pants that didn't fit too well in the first place. Problems I see with this are:
1) I still have to create a new summary field for each different Equipment Type, and I expect that list to get longer over time.
2) I worry that if I'm relying on automations to "fix" the relationship field, that means there will be a delay. So I can't simply abort the save and display an error message, I have to accept the new record and then try to notify the user after the fact that it was an invalid request.
Before I dive into that, can you think of any other way to accomplish what I'm trying to do without having to do the whole date table setup? A way to simply count, when a reservation is created, how many other reservations share the same date and equipment type.
------------------------------
Andrew Nelson
------------------------------
1) I still have to create a new summary field for each different Equipment Type, and I expect that list to get longer over time.
2) I worry that if I'm relying on automations to "fix" the relationship field, that means there will be a delay. So I can't simply abort the save and display an error message, I have to accept the new record and then try to notify the user after the fact that it was an invalid request.
Before I dive into that, can you think of any other way to accomplish what I'm trying to do without having to do the whole date table setup? A way to simply count, when a reservation is created, how many other reservations share the same date and equipment type.
------------------------------
Andrew Nelson
------------------------------
- MarkShnier__You4 years agoQrew ChampionYou can also make the scalar field required at the field level and just have form rules populate it. Then no automation delay unless the user is in grid edit.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------ - MichaelTamoush4 years agoQrew CaptainI might try to come at this from a totally different angle.
Table 1: Equipment. Fields with equipment name, and the total number available on any given day.
Table 2: Equipment and Date: This table would populate every day via a pipeline or automation. It would create a record for each piece of equipment, and set a date field to today() + 30 days (or however many days in advance people would need to reserve equipment. You could make it +365 days). So if you have 10 pieces of equipment, every day 10 new records are created with the equipment name, the date, and the amount available (copied during the automation from your equipment table). The trick to table 2, is to initialize it, you need to create the first 30 days, or 60, etc of entries. Do this with an import from an excel sheet. After that, it will always remain up to date.
In your reservations table, make a relationship to table 2. Table 2 has many equipment reservations. Use a conditional dropdown on the date to only allow to select equipment from that date.
Use a summary field on Table 2 for total equipment reserved. You can also make a formula field for total equipment remaining if you like. On your related field in Equipment reservations (the one with the conditional dropdown) you can filter to hide equipment with none remaining, or just highlight them in red, or whatever you want to do.
------------------------------
Mike Tamoush
------------------------------