Summary Field of a Formula

  • 1
  • 2
  • Question
  • Updated 1 year ago
  • Answered
  • (Edited)
I'm a longtime QB user and seem to run up against this limitation often. I was wondering if anyone has found a good workaround.

My data consists of three fields, "Date", "Division", and "Amount". I need to be able to summarize the table by amount based on two criteria, "Division", which is a number, and "Month/Year".  
Each record has an amount.
I figure I can do this by concatenating the division and month/year into a formula and using that as the reference field of the child table to match the unique key field of the parent table. However, I keep running into the same limitation with "We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is a lookup field." I've tried the workaround of making the reference field a static (non-formula) field, creating the summary, then changing the reference field to a formula, which works sporadically, but sometimes it likes to show me zeros for all amounts. It can't be trusted to update. Any bright ideas on how to do this? I have several other instances where I need to create summaries based on formulas but can never figure out how to get around this limitation. Thanks in advance.
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
So a Summary report is not good enough for your needs? If that is the case, and you need to have an actual table which holds these values, then you will need to create a table which has a key field which includes the YYYY-MM-NN where the NN is the division number. So you could make that formula in your details data and create a summary report, and then from time to Tom export that list of key field values to a Summary table to update the complete list. Then make a relationship and summarize the data.

Quick base does have a roadmap plan to allow automated table to table copies, so maybe in the future the need to manually create those index records will be eliminated.

The other possibility is that you may be able to use excel to create the next say 10 years of every combination of the YYYY MM and the division, that's probably a better idea.
(Edited)
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb
Thanks for the reply. I need to do further calculations with the cost data that need to summarize, so a summary report isn't going to work.

I'm not following your last comment. I have created a parent table with every possible combination of YYYY-MM-DIV, which is the unique key field in the parent table, but in order to make the relationship to the same data in the details table, I'd have to use a function to concatenate the three fields to one, which doesn't work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Re: I'd have to use a function to concatenate the three fields to one, which doesn't work.

List("-", ToText([Year]), ToText([Month]), Totext([Div]))
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb
Thanks, but I mean using this function as the reference field gives me the "can't create a summary field where the reference field is a lookup field." error message.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
OK, I did  not read your original post carefully enough before plunging in.  Yes, now I understand the issue.  So, what you will need to do is to create a Webhook or an Action to copy the value of he formula field to the data entry text field.  At EMPOWER, Kirk Trachy said that you can trick the Action  to being able to edit the record which was modified since the children are defined by a Report link and you can thus match up a Parent with itself based on the record ID#.  I have not tried this myself, but Actions are gong to be easier to maintain.

Then you will be able to make that Relationships based on a pure text field and not have that limitation.
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb
Ah, that makes a lot of sense. I've been playing with Actions but this is a new use for those. I'll try that. Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
So the setup is that you can add a record (which is not useful to you) or edit related records. Let me know if you get it working to trick it to edit the "Parent" record.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
So I just ran into that same limitation myself. :(  

So Webhook or Action had to be built.  So I built my first Action and did that cheat by first creating a Report Link field where I mapped the [Record ID#] field on the left to the (long journey) to the same Application, same Table and same Field [Record ID#] on the right.

It worked first try  :) man-o-man way easier than a webhook setup!  :)
Photo of Logan Amster

Logan Amster

  • 90 Points 75 badge 2x thumb
Did this action end up working?
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb
Thanks for the info, I wouldn't have thought of using a Report Link to allow the system to relate the record to itself. Otherwise Actions only allows me to add a record, which I don't want.

I'm still running into a problem. I created the Report Link to tie the existing key to the same application, table, and field. Even tho this is a Quickbase Action, I actually receive a "Quickbase Webhook Error" email from QB saying:

400 Error: <?xml version="1.0" ?> <qdbapi> <action>API_EditRelatedRecords</action> <errcode>1</errcode> <errtext>Unknown error</errtext> <errdetail>You cannot include the record ID if it is not the key field.</errdetail> <num_recs_updated>0</num_recs_updated> <num_recs_unchanged>0</num_recs_unchanged> </qdbapi>

My key field is *not* [Record ID#]. I made the report link using my key field, which is a unique, imported text field. My link has nothing to do with the [Record ID#]. I wonder if something is hard-coded to look for the [Record ID#] even if it's not the key?

It seems your test used the [Record ID#] and it worked. Any thoughts?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Re: Any thoughts?

Use the [Record ID#] for your Report Link field.

:)  Let me know if that works.  Who says that you need to use the Key field?  If it wants the [Record ID#] give it the [Record ID#]
Photo of Michael Frishman

Michael Frishman

  • 284 Points 250 badge 2x thumb
Yeah, tried that too. No dice. Have a support ticket into QB to look into this. Maybe this only works if the [Record ID#] is also the key. Thanks anyway.

<errdetail>You cannot include the record ID if it is not the key field.</errdetail>
Photo of Logan Amster

Logan Amster

  • 90 Points 75 badge 2x thumb
Did you all ever get this to work? I'm having the same issue. In particular, I can't seem to get my Quick Base Action to trigger... :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Logan please post a new question.