Discussions

Expand all | Collapse all

How to multiply all the record values of a Field?

  • 1.  How to multiply all the record values of a Field?

    Posted 05-18-2018 11:21
    Field1     Field2
    1             3
    2             5
    3             7
    4             2



    I need to do 3*5*7*2=210
    And I want to show the result as


    Field1     Field2       Field3 
    1             3               210
    2             5               210
    3             7               210
    4             2               210


    I'm able to figure out the way to make Multiplication possible. Please Help.


  • 2.  RE: How to multiply all the record values of a Field?

    Posted 05-18-2018 11:53
    You will need to create a new table in the app with a single record in it. It will be Record ID 1.


    Then make a formula on your details table of 1 and make a relationship.


    Then make a sum of a field which multiplied the two numbers.


    That is called the �dot product�.

    https://en.m.wikipedia.org/wiki/Dot_p...


  • 3.  RE: How to multiply all the record values of a Field?

    Posted 05-18-2018 12:55
    Hi, I din't understand the second line. " Then make a formula on your details table of 1 and make a relationship". 


  • 4.  RE: How to multiply all the record values of a Field?

    Posted 05-18-2018 12:57
    You need to connect every one of your records to hat single record in he new table.  The record ID of the single record will be record ID 1.
    So just make a formula field with a formula of 1 and use that as the reference field in the relationship on he right hand side.


  • 5.  RE: How to multiply all the record values of a Field?

    Posted 05-18-2018 14:49
    Yah but, How can I make a formula of Multiplication "3*5....." in the formula Field. As 3 and 5 are from different rows?
    And What exactly you are saying when you are saying "formula of 1"?


  • 6.  RE: How to multiply all the record values of a Field?

    Posted 05-18-2018 15:34
    Vivek,
    I think that I misunderstood your question.  I now see that you want to be able to multiply data from a field in different rows.

    I do not think that there is a way to do that with native Quick Base.  Native Quick Base can sum a field using a relationship but not multiply it.

    Perhaps if you can explain your use case, I can think of an alternative approach.


  • 7.  RE: How to multiply all the record values of a Field?

    Top
    Contributor
    Posted 05-18-2018 15:42
    >Native Quick Base can sum a field using a relationship but not multiply it.

    use logarithms and exponentiation 


  • 8.  RE: How to multiply all the record values of a Field?

    Top
    Contributor
    Posted 05-18-2018 15:27
    Follow these steps:
    1. Create a field Ln([Field2]) in the child table (your existing table)
    2. Summarize this field to the parent field named [SumLog] using addition
    3. Create a formula field in the parent  table named [Product] and set it equal to the formula Exp([Product])
    4. In the child table lookup the field [Product] from the parent
    This works because Law of Logarithms:

    3 * 5 * 7 * 2 = exp( ln(3) + ln(5) + ln(7) + ln(2) )= 210

    Then go watch Standard Deviants:

    Standard Deviants: Pre-Calculus Module 6 - Logarithms
    https://www.amazon.com/Standard-Deviants-Pre-Calculus-Module-Logarithms/dp/B000A2EKTA


  • 9.  RE: How to multiply all the record values of a Field?

    Posted 05-19-2018 00:06
    Thx Dan, I can't imagine when i would need this, but you never know.  I tried is on a calculator and it works.  I think in a previous life I used to know that.


  • 10.  RE: How to multiply all the record values of a Field?

    Top
    Contributor
    Posted 05-20-2018 00:18
    > I tried is on a calculator and it works.

    Of course it works - its just simple math.

    As a remedial measure I suggest you binge watch a few seasons of Numb3ers starting with this promo:



    He's a mathematician. And He's an FBI agent. And they're both brothers.
    https://www.youtube.com/watch?v=EVSFzswY3XY

    I created a demo



    Multiply Child Fields ~ View [Record ID#]= 1
    https://haversineconsulting.quickbase.com/db/bnp7nxxq7?a=dr&r=b&rid=1

    There are two formulas - one in the child table where [Ln] is calculated as:

    Ln([Number])

    and one in the parent table where [Product] is calculated as:

    Exp([SumLn])

    [SumLn] is a Summary field in Table #1 and [Product] is a lookup table in Table #2.




    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=655">https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=655">https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=655

    Notes:

    (1) you can add your own data to test the application

    (2) you can't enter negative numbers as Ln(x) is not defined for negative numbers.


  • 11.  RE: How to multiply all the record values of a Field?

    Posted 05-21-2018 12:39
    Thanks guys, it's working. Learnt new thing today.