Is there any way to create a Summary Field when the Reference Field that the Relationship is based on is a Formula?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I seem to have run up against a restriction in QuickBase that I didn't know existed. 

I have a table that contains "Work Instances." Each Work Instance has a date.

I have another table called "Work Weeks" that is in a many Work Instances to One Work Week relationship to Work Instances.

I wanted to automate the process of associating Weeks to Instances, so I set the Work Key Field to a Date field representing the first day of every week, and then set the Reference Field In the Work Instances table to a Date formula field that takes the Date the work was done and uses the FirstDayOfWeek() formula function to find what the first day of the week was that the Work Instance was done on.

This works great! Except that when I try to create a Summary Field in this relationship, I get a message telling me that:

"We are sorry -- at this time, you cannot use this formula and have a summary field using this relationship."

That's a bummer, but I'm wondering if there's a workaround? Or maybe an entirely different way to go about what I'm trying to do? (Basically, the whole reason I got myself into this mess is because I wanted to generate an notification when a certain number of Work Instances had been created within a week, and I used this suggestion: https://quickbase-community.intuit.com/questions/1126070-how-do-i-create-an-email-notification-based...

Please let me know if any clarification would help.

Photo of Shaund Grey

Shaund Grey

  • 80 Points 75 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
If you go into your formula reference field and cut the formula out and save it with no formula, then go create your summary field and then paste your formula back in the formula reference field and save - ignoring the warning you get at that point, that should work for you as a work around. Kinda wonky but it gets the job done without needing any code. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I have sometimes seen, well a few years ago, wonky results when i tried to cheat the system like that.  So I stopped cheating.

The other alternative is to have a a field called [First Day of week not a formula] field  and then have a form rule that saves the value of the field in the calculated field to a regular date field.  Then use that one for your  relationship.
Photo of Shaund Grey

Shaund Grey

  • 80 Points 75 badge 2x thumb
Ah, I was certain that would work! But unfortunately, when I try to change the reference field back into a formula, QuickBase gives me the same error again.
Photo of Shaund Grey

Shaund Grey

  • 80 Points 75 badge 2x thumb
Actually, I may have spoke too soon - while it does give me that error, the formula is still functional.

Still, I feel like I'm teetering on the edge of something. I hope QuickBase supports this functionality in a future update. Maybe if any of you have the ears of the developers, you can push them in that direction. Or if there's an alternate solution (I may look into that form-based solution, although we use the API for a lot of stuff here, so that may require some extra legwork).

Thank you all in any case!