Forum Discussion

AndrewNelson's avatar
AndrewNelson
Qrew Cadet
4 years ago

Relationship/Summary Restrictions on Date fields

I am working on building out a system where our foremen can "schedule" certain equipment to be used for specific days.  For simplicity's sake, they do not need to check out a specific piece of equipm...
Relationships
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
I just did a test  relationship and it sees to work for me.  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 Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
AndrewNelson's avatar
AndrewNelson
Qrew Cadet
4 years ago
It lets you create the relationship?  Or it lets you add summary fields?  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.  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's avatar
    MarkShnier__You
    Qrew Champion
    4 years ago
    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
    ------------------------------
    • AndrewNelson's avatar
      AndrewNelson
      Qrew Cadet
      4 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        4 years ago
        You 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
        ------------------------------