Total of a field , without relationship

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
I have a table in that i have one field called amount , so without any relationship how to get the total of the particular column in the same table
Photo of DILEEP THOMAS

DILEEP THOMAS

  • 100 Points 100 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
no can do.

I you want to visually see a total on a report, then look at the report.  but if you need an actual field, you would need to make a dummy table just to hold the Total(s) that you want.   

For example, new table called Sales Totals. make a single record in it. It will be [Record ID#] of 1.
Then lock it down so no one cal add another record.

Then on the sales table make a formula numeric field with a formula of 1.  It will calculate, not surprisingly to 1.

Then make a relationship based on that field and make your Totals Summary field(s) and then look them up back down to the Sales table.

There is no =SUMIF like Excel has.
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
I have done something like the following, which will require some coding though:

1-Create a "Text-Formula" field and allow HTML tags in it
2-Add "<img> " and have the onload() function to run a script that will run api_genResultsTable&jsa=1 query, clist for the column to be calculated and get the total in javascript. 
3-Add "<p>" with an ID that have the record ID composed in it so you can use in it a report
4-Return the calculated value in that <p> tag

The only downside is that you cannot export result, it will show as script in the CSV file. But it is good to study and check visually.
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
You can return a javascript array with the field you want to sum using the api_GenResultsTable, and then calculate the total and return it. Below link might be helpful, You can use something like:
<img ....><p id='x"&[Record ID#]&"'>Loading ... </p> 
So you can use the field in a report, but please remember, the results will not be exportable.
https://help.quickbase.com/api-guide/gen_results_table.html

Please let me know if you need more clarification

Regards
(Edited)
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,710 Points 5k badge 2x thumb
Hi Aiden,

I have a similar need to calculate the total hours logged for a day.
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
Have you tried to get the alert mentioned in my first comment?
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,710 Points 5k badge 2x thumb
Yes, it's done.
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
Are familiar with "API_GenResultsTable" call? It can return a javascript Array with values that you need them totaled! 

Regards
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,372 Points 20k badge 2x thumb
>no can do.

Actually you can do this naively. I really hate that word "natively" -  it is so  self-limiting.
Expand your repertoire of skills, challenge your longstanding mindset and generally embiggen your world.
Check out this application which introduces a new technique I call Total Recall where the table has exactly three fields:

[Name] - Text Field
[Amount] - Numeric Currency Field
[Percentage] - Formula Numeric Field = 100 * [Amount] / ToNumber([Total])



Total Recall

https://haversineconsulting.quickbase.com/db/bnhmr8df6?a=td

There is no image onload field being used in this application and again the table has precisely three fields: [Name], [Amount] and [Percentage].

So how did I do it? Well the obvious question is where the field [Total] coming from? Well the simple answer is that it is not a field - it is a user defined variable whose value is calculated (via script) and set just prior to the report being displayed.

If you move to the Tasks table you will see there is a button labeled Total.js:



Clicking this button will execute the script in the pastie below and result in (1) calculating a new total of the [Amount] field in all records, (2) place the value in the user defined variable [Total] and (3)  redirect to the List All report:

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

Now this may seem like a very convoluted way of generating a simple total. Why not just create a dummy or faux table to be the parent of the table holding all the data? 

There is a very meaningful answer to this obvious question and good reason pursue solutions like this.

When you run into a limitation on what QuickBase can do "natively" you can often achieve your overall goal by performing some type of pre-processing operation to modify the QuickBase environment by temporarily creating (a) a user defined variable or (b) a report or chart, or (c) a temporary table.

Viewed in this more expansive light, once the pre-processing operation is complete you can then use "native" techniques to achieve some spectacular tasks (that are otherwise impossible) such as:

  • Create Custom Summary Reports
  • Create Custom Email Notifications
  • Create or Modify a QuickBase Generated Chart
(Edited)
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
The "<img> onload" technique is used to calculate the total when you load the page without having to press a button. 

If you have a report of 50 records that the total need to be calculated for each one of them and displayed in the same row, it wouldn't be feasible to click the button 50 times! 

Regards