Discussions

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?

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.

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.

Thanks for the info Harrison. I understand the limitation from your end.

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.

If anyone was wondering, you can't fix this by using snapshot fields instead of lookup fields. I just tried and got this error "We are sorry -- at this time, you cannot use this formula and have a summary field using this relationship." 

Yes, it�s too bad that you can�t use a Snapchat filter. You will need to use an automation or an action to copy the value of the look up field into another field and use that second field for the relationship.

This is the work around I have implemented for this functionality. I can confirm; it works.