Help with formulas

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
i need a formula that uses the (total of field a) minus (field b) instead of (field a) - (field b) which quick base only allows me to do, but when i view my table it shows a total. i need this set up based all on separate fields too. Please can someone help :(
Photo of Demi Butler

Demi Butler

  • 100 Points 100 badge 2x thumb
  • Confused and frustrated

Posted 1 year ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,416 Points 10k badge 2x thumb
Hi Demi,

What you are looking to do is going to require more than just a formula. In Quick Base a formula is focused to do math just on data that is stored in fields in one particular record. So math like (field a) - (field b) is possible since both pieces of data exist on the same record. Instead when you are looking to do math for a value like the total of all records in field A that data doesn't exist by default in a place where the formula can reference it. 

Instead what typically needs to happen is you need to leverage a relationship in order to make it possible for data to be summarized and then made available for analysis. So for example if I had a set up where I had two tables Projects and Tasks and I realized I wanted to be able to see on each Task record how much the rest of the Projects Costs are minus the current task. So I have the cost of the current task in my record on the Tasks table but I do not have the total cost of all tasks, that is what I need to get to make this formula possible.

In this instance what I would do is go into the relationship between Projects and Tasks and add a summary field to summarize the Cost field from all Task records to their related Project. This way each Project record gets a field that shows the cost of all the Tasks assigned to it. Now to get that information back down into each Task I need to go into the relationship between Projects and Tasks and instead of creating a summary field I now create a look up field that pulls down the new Total Cost of All Tasks field to display on each Task record. This means I now have the Total Cost of All Tasks displaying on each Task. This way I can now do. [Total Cost of All Tasks]-[Current Task Cost] to get my new formula. In order to do this formula there has to be a place where this total is being summarized up to another table and then passed back down. 

I hope this information helps point you in the right direction Demi. 
Photo of Demi Butler

Demi Butler

  • 100 Points 100 badge 2x thumb
Thank you - this is very helpful! I'll give it another go :)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,416 Points 10k badge 2x thumb
Excellent, and let me know if you have any further questions. These summary calculations can be a little tricky to set up when you are first learning them but can be very helpful. 
Photo of Demi Butler

Demi Butler

  • 100 Points 100 badge 2x thumb
Now that you mention it.... I am struggling to set up the relationship in a way that gives the correct figures. At the moment, once I have the summary field set up the column stays empty and I can't relate this figure onto my other table. Any tips on how to set this up correctly? If it helps, I am trying to relate a total figure from one table onto another table grouped by names. Thank you!
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,416 Points 10k badge 2x thumb
Usually if the summary field isn't populating with values for a record that means those records aren't related yet. When you look at the related fields do the parent records have child records related to them? If this is a newly created record you would need to make sure to assign the child records to the right parent records.
Photo of Demi Butler

Demi Butler

  • 100 Points 100 badge 2x thumb
How do you add child records? Thank you!
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,416 Points 10k badge 2x thumb
Hi Demi,

You would want to go into your existing child records in your table that you are summarizing up from and edit them to make sure each child record has a Parent Assigned. You can figure out what field this is by looking at your relationship between the Parent and Child table, it will list the reference field for that relationship which has to be filled in for the relationship to function. If you are having issues with this I would suggest putting in a support case so that you and a rep can look at your relationship and records together as there might be some disconnect that is causing the issues you are running into.  You can create a new case in Quick Base if you click Help in the upper right hand corner of the page when signed into Quick Base > Manage Support Cases > + New Support Case. If you are still having issues getting Parent and Child records to sync up they can help look at the relationship with you and identify what is causing those summary fields not to bring up your data.