Cannot create a summary field using a relationship where the reference field is a lookup field.

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered
  • (Edited)
I get an error quoting exactly what I specified in the title: "Cannot create a summary field using a relationship where the reference field is a lookup field." when I attempt to add a summary field to an existing relationship. Is there a way around this?
Photo of Austin Hayes

Austin Hayes

  • 130 Points 100 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Alas,
"we are sorry, but at this time you may not ...."

How many times have I seen that message in the last 14 years. Too many.

So the only solution is to make the reference field in the relationship not be dependent on a lookup field.  The way to do that is to create a new field called say [Related Parent populated by Action (or Automation)]

ie, you need to mirror that ineligible fields with a pure data entry type field maintain the field to be equal to the original field with an Automation or an Action..

So for example, you can make saved table to table import with a filer on where those two fields do not match.  Then create a saved table to table import, Merging on [Record ID#] to map the one field into the other, subject to the filter that the two don't match.

Then set up an Automation or you may need more than 1 to trigger on whatever edits or creates which might cause those to not match.


Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 676 Points 500 badge 2x thumb
This is a really deep and complex topic, so I thought I would add some color here. Quick Base is an in-memory database that computes very complex calculations extremely quickly. For some more details on this, here is a helpful use case I wrote some detail on.

As you can imagine, summary fields get enormously complex. This is especially true when tables have millions of records and then that summary is driving other lookups, formulas, or more summary fields. To keep Quick Base performant and ensure data accuracy, there are always tradeoffs that have to be considered.

In the case of summary fields, Quick Base leverages extensive logic to build the summary fields dynamically so that the calculations are very fast. The nature of a dynamic field though means that it can change based on anything - i.e., the time of day down to a millisecond. Because of this, there is the chance that data as part of a relationship where a calculated field is the key, might not be accurate, and therefore we impose a protection at this point to ensure the accuracy of data.

The workaround Mark suggested is solid and would work because the actual key is a true data field QB can rely on behind-the-scenes. Of course, you have to know to capture the changes at the right spots in the application.

All that being said, I do have a work item in my backlog to evaluate how we can do some of this for you on the backend and open up more use cases here. It isn't prioritized yet but it is something I am interested in diving into. Feel free to post your use cases here so I can learn about all of them and see if we can factor them into future releases.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Harrison,

I really appreciate the explanation, I have never known  for sure why this has been an issue for so long.

But maybe, just maybe, you are the one who will crack this tough nut.

I will think of some use cases.