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.

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.

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.

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]))

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.

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.

Summary Field of a Formula | Qrew Discussions

QuickBaseCoachD
Qrew Captain
8 years ago
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.
MichaelFrishman
Qrew Cadet
8 years ago
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.
QuickBaseCoachD
Qrew Captain
8 years ago
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]))
MichaelFrishman
Qrew Cadet
8 years ago
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.
QuickBaseCoachD
Qrew Captain
8 years ago
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.
MichaelFrishman
Qrew Cadet
8 years ago
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!
QuickBaseCoachD
Qrew Captain
8 years ago
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.
QuickBaseCoachD
Qrew Captain
8 years ago
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! :)
- LoganAmster
  Qrew Member
  8 years ago
  Did this action end up working?
MichaelFrishman
Qrew Cadet
8 years ago
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?
QuickBaseCoachD
Qrew Captain
8 years ago
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#]

Forum Discussion

Summary Field of a Formula

Related Content

Summary Reports

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Color Code Date Field based on Formula

RT Formula Checkbox Summary field help

Formula Url Buttons