Discussions

Expand all | Collapse all

Help with formulas

  • 1.  Help with formulas

    Posted 12-18-2017 16:39
    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 :(


  • 2.  RE: Help with formulas

    Top
    Contributor
    Posted 12-18-2017 18:32
    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. 


  • 3.  RE: Help with formulas

    Posted 12-19-2017 11:34
    Thank you - this is very helpful! I'll give it another go :)


  • 4.  RE: Help with formulas

    Top
    Contributor
    Posted 12-19-2017 14:17
    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. 


  • 5.  RE: Help with formulas

    Posted 12-19-2017 14:33
    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!


  • 6.  RE: Help with formulas

    Top
    Contributor
    Posted 12-19-2017 14:46
    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.


  • 7.  RE: Help with formulas

    Posted 12-21-2017 14:15
    How do you add child records? Thank you!


  • 8.  RE: Help with formulas

    Top
    Contributor
    Posted 12-21-2017 14:32
    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.