Forum Discussion

QBuserQBuser's avatar
QBuserQBuser
Qrew Cadet
8 years ago

Cannot use formula and have a summary field using this relationship.

I am getting the following error when trying to use summary fields in a relationship.

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

I know from previous posts that I can click through error and it will still work.  Unfortunately it randomly stops working from time to time and all the values are blank.  I have to then delete the formula and recopy and save it.  It is very frustrating that Quickbase cannot make this work.

I have over 400,000 records in a linked table that updates each night.  I need to concatenate the date, location, and customer information.  I do not think a form rule will work because the data is updated through the link.  Is there any work around to this?  I just need that concatenated value automatically copied over to a text field, but I cannot figure out how to do it.
Errors
  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago

    Can you create a formula text field to concatenate those fields for you?

    [Date]&[Location]&[Cust Info]

    Or am I misunderstanding?

    • QBuserQBuser's avatar
      QBuserQBuser
      Qrew Cadet
      8 years ago
      That is what I have done, but then quickbase does not allow you to summarize records if you do this.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Lookup fields may not be used as the reference field on the right aside of a relationship if you need to have summary fields on the relationship.  You might "trick" it from time to time to get it to not error on you, but the results will often be flaky - ie the Summary fields will not be correct.

    1. Is there a way to make the link based on fields which are not lookup fields- for example [Related Location] and [Related Customer], ie those fields would typically not be lookup fields.

    2. Alternatively it would not take much to make a formula URL button to update a data entry text field to match a formula field, then base your relationship on a clean data entry field that will work.  However, you would need to click the button each day to update the links.  Perhaps ACTIONS could also be used to maintain the integrity match between the formula field and the data entry field.
  • QBuserQBuser's avatar
    QBuserQBuser
    Qrew Cadet
    8 years ago
    I am not sure I am following #1.  What is the difference between looking up the customer id and relating it like you mention?  Is the lookup field the issue or is the issue that I am using a Text Formula field in the relationship?  Could I create a new field and use the ToText function to get around this?

    Can you help me on how I would go about doing #2?  I prefer that a button would not have to be clicked if possible.  It would be good if it could run after the table is updated in the middle of the night.
    • QBuserQBuser's avatar
      QBuserQBuser
      Qrew Cadet
      8 years ago
      My Customer Field is actually a forecast group field from another table so that is why I am looking it up.  Location is not a look up field.  I also have reporting year which looks up the year based on dates from another table since the year runs September-August.  My formula looks like 

      [Reporting Year]&" - "&[Month Text]&[Month Half]&[Forecast Group]&[FacName]

    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      This sounds like it will require one on one assistance to sort out a solution.  I can be contacted via my gmail address on my Contact me page.  QuickBaseCoach.com
    • QBuserQBuser's avatar
      QBuserQBuser
      Qrew Cadet
      8 years ago
      I gave this some more thought and figured out that I could replace my lookup fields with some IF statements to generate the data.  This then fixed the summary error.  I would have preferred looking up the values so that they are easier to update in the future, but looks like this is the best and easiest solution until quickbase fixes the issue.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    I've had to do this several times and I found this works well.

    Use your formula field  and form rule to actually copy a value (your formula that contains the lookup field) into some other text field.  Once that text field has a value, you then drive the relationship based on the text field.  Then QB doesn't have issues with summaries or other fields.

         If form rules aren't an option, (i.e. Grid edit, import). You can use an Action to copy that value over once its created or modified.  So it wont be connected right off the bat, but withing seconds of saving the record.