How to multiply all the record values of a Field?

  • 2
  • 2
  • Question
  • Updated 6 months ago
  • Answered
  • (Edited)
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.
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb

Posted 6 months ago

  • 2
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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...
(Edited)
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
Hi, I din't understand the second line. " Then make a formula on your details table of 1 and make a relationship". 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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.
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
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"?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
>Native Quick Base can sum a field using a relationship but not multiply it.

use logarithms and exponentiation 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
> 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

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.
(Edited)
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
Thanks guys, it's working. Learnt new thing today. 
(Edited)