Relationship/Summary Restrictions on Date fields

Question

I am working on building out a system where our foremen can "schedule" certain equipment to be used for specific days.&nbsp; For simplicity's sake, they do not need to check out a specific piece of equipment, but rather just reserve one of a TYPE of equipment for that date.&nbsp; Part of this system is that I need a way to alert the user when they request a piece of equipment that has already been reserved too many times for that particular date.My intended structure was something like this: a table called "Equipment Reservations" with the following fields:Date - the date the equipment is reserved forEquipment Type - A multiple-choice Text field that lets the user select what type of equipment is being reservedEquipment Count - this is a formula-numeric field that returns how many pieces are available based on the Equipment Type field.&nbsp; Essentially just a case() statement and a list of quantities for each option in Equipment TypeBasically, I need a way to count up how many Equipment Reservations there are that have the same Date and the same Equipment Type, and if it is more than a certain value, either a) trigger a checkbox field on the reservation to highlight the request as invalid, or b) just halt the request and not allow it to be submitted at all.&nbsp;&nbsp;In the past, I've solved this problem in a somewhat roundabout way.&nbsp; I created a table called "Calendar Date" and just pre-populated it with a record for every date for the next 20 years.&nbsp; I use the short date text as the unique identifier and then created a relationship between the Equipment Reservations table and the Calendar Date table, using a formula-text field that simply contains "toText([Date])" as the reference field.&nbsp; Then I could set up Summary Fields for each Equipment Type that would count the number of Equipment Reservations related to that Calendar Date with the same Equipment Type.&nbsp; However, now when I go to set that up, it no longer works - Quickbase will not allow me to create ANY summary fields on this relationship because the reference field is a toText([Date]).
What is the point of this restriction?&nbsp; Why was it implemented?&nbsp; Is there other any way to count the number of "siblings" that share more than one field in common?------------------------------Andrew Andrew------------------------------

markshnier__you · Answer

Is that date field a lookup field?------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

andrewnelson · Answer

No, [Date] is a normal Date field, and [Date ID] (the reference field for the relationship) is just a Formula-Text field containing toText([Date]). It appears that because I have used a derivative of a date field as the reference field, QuickBase simply won't allow me to create ANY summary fields on the relationship.

I have done this in the past, and from what I can tell it is still working where it was already implemented. But even when I try to add another summary field to THAT relationship I get this message:

We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is this type of formula field. Please visit our limits page (https://XXXXX.quickbase.com/db/main?act=dohelp&page=quickbase_limits) or contact Care for more information.

------------------------------
Andrew Nelson
------------------------------

markshnier__you · Answer

I just did a test&nbsp; relationship and it sees to work for me.&nbsp; Key field of the Parent was a text field and on the child side my formula was a totext([date field type])------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

andrewnelson · Answer

It lets you create the relationship?&nbsp; Or it lets you add summary fields?&nbsp; I can create the relationship, it just won't allow any summary fields.The limits page the error message linked to states:
You will be able to create a summary field only when the foreign key meets the following conditions:

The field must not have any custom permissions.

Any referenced fields in the formula must not have any custom permissions.

The formula field only references other fields which are either scalar or formulas that follow these rules.

The maximum number of text values that a combined text summary field can accumulate is 150.

Only the following formula functions are supported when not referencing dates, datetimes, or users.

ToText()

Nz

ToFormattedText that does not reference a timezone

ToNumber

ToBoolean

Mathematical functions like log, mod, etc.

Text functions like right, left, mid, etc.

URLEncode

Duration conversions like ToDays, ToMSeconds, etc.
Note: Any function above, like ToText(), referencing a date or time will not be allowed.

Which makes it clear that what I'm trying to do is not allowed.&nbsp; I'm just trying to understand WHY (and, more importantly, trying to find a workaround that lets me do what I'm trying to do).------------------------------Andrew Nelson------------------------------

markshnier__you · Answer

Sorry, my mistake. I actually forgot to put the formula field on the reference side of the relationship.&nbsp;&nbsp;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.&nbsp;&nbsp;Then I make a safety net report subscription to look for anything that the overnight safety net Automation didn't catch.&nbsp; So like a belt,&nbsp; and suspenders, and then more suspenders just to be sure.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Relationship/Summary Restrictions on Date fields

8 Replies

Related Content

How to create a Combined Text summary field using a relationship field

New Forms Restricted Access Field

An API(?) button to copy fields from the form, to other fields

Multiple reference proxy fields help

Summary Fields and Relationships

Recent Discussions

Question about relationships

Summary Table Question

Google Search is back

Can I use quickbase actions within the same table?

Looking for Consultant: Add time tracking to existing task management app.