Hey Folks! I wanted to add multiple record, but there has been a single field used by multiple records!

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Hey Guys i wanted to create a Field which will calculate the total from two other fields. I have explained Below.

There are fields called Capex Budget NA & We Conversion Capex Budget. These fields are being used by many projects. Projects are nothing but the records which i created with various fields. As mentioned the above mentioned fields are being used by many projects and these are basically Numerical fields and each of the project has their vlaues in the mentioned fields. Now i wanted to created a Formula Numeric fields which will sum up the above mentioned two fields and give me the total value. I have tried this in all possible way but didnt work as i have tried in a simpler way by creating a formula numeric Field in the name of "Total Capex Budget" (Capex Budget NA + WE Conversion Capex Budget) but it gives me "0" Value but there are numerical in the above mentioned two fields.

 

Please Help need a solution vary badly.

 

Abhishek Mishra

Photo of Abhishek

Abhishek

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
Try this

[Capex Budget NA] + [WE Conversion Capex Budget]

There is a box at the side of the formula builder which will allow you to select fields for your formula rather than typing them.
Photo of Abhishek

Abhishek

  • 0 Points
I have tried doing this and that's what i have mentioned above in my question. But still i am getting a "0" Result
Photo of Abhishek

Abhishek

  • 0 Points
Is there any other way
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
I would check and example record to see what value are in those two fields on an example record.  They must both be blank or zero.  What value is in each of those two fields which you are adding together?
Photo of Abhishek

Abhishek

  • 0 Points
In some records both the fields are blank and in few records they are there. I guess the total should calculate the numbers where it's been there but its not doing the same. In case if you want further discussion please let me know so that i will set up a quick Meeting sometimes on monday. Please do share your email ID.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
I offer one on one assistance, but only on a consulting basis.  Feel free to contact me via the information in my Profile.  I gather now, that you issue is that the total for the column is zero?  Is that what you are saying?

If you have blanks in some of your fields, and want them to be treated as zeros in calculations, then there is a field property setting to "treat blanks as zeros"
Photo of Abhishek

Abhishek

  • 0 Points
Hi Mark, I have already tick the checkbox in the field property setting to treat blank as Zeros but still the total field is showing up Zero value and its not calculating the values.
Photo of Abhishek

Abhishek

  • 0 Points
Hey Mark i just realized that it's a calculation mistake and i actually convert the Pounds and Euro to Dollars and then i am taking a total but the conversion field has been mapped to wrong one so it's been giving Zero result in return.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
So it's all good now?
Photo of Abhishek

Abhishek

  • 0 Points
I got the problem Actually i have created a Table in the name of "Conversion Rates" and there i have added all the currency conversion rates and then i have created a relationship between Conversion Rates and Project. Project is actually a table where i have the previous mentioned fields are present those are WE Capex Budget and 2015 Capex Budget - NA. I am planning to convert the WE Capex budget to $ so i have created a conversion table separately and created a formula field in the name of WE Capex Budget Conversion and added a formula "If(([Currency to Display]="GBP"),[British Pound: USD]*[WE Capex Budget-2015], ([Currency to Display]="Euro"),[Euro: USD]*[WE Capex Budget-2015])" but i realized that the Conversion rates which i have created in the Conversion table and not getting populated in the Project Table. So is the reason why i am getting a Zero Value.

Please help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
Typically in a relationships there is a field like [Related Currency] in the project table.  You will need to ensure that is being populated.  Typically for a Currently table the Key field would be set to be the field  "Currency Code" and then the project would use that as a drop down list to get populated.  Then you would lookup the currency value.

I also suggest that you read the help info in snap shot fields so you can freeze the value of the currency and not have it keep changing your project values once the lookup is done.

http://www.quickbase.com/user-assistance/Default.html#setting_up_snapshot_fields.html
Photo of Abhishek

Abhishek

  • 0 Points
Thanks Guys all your comments and suggestions are really helpful