Need to create a countif formula to count repeated values in table. Is this possible/how?

  • 1
  • 3
  • Question
  • Updated 3 years ago
  • Answered

Need to create a countif formula to count repeated values in table. Is this possible/how in Quick Base? Been messing with "Summary field options", but can't quite get what I'm after. Could do it in excel and import back in, but want value to update automatically as I add more records to my table.

Photo of Taylor

Taylor

  • 20 Points

Posted 3 years ago

  • 1
  • 3
Have you tried s Summary report on that field, and click the column twice to get it to sort duplicates at the top?
Photo of Taylor

Taylor

  • 20 Points
Doesn't a summary report by definition filter out duplicates, similar to a pivot table in excel? What I need are the values the summary report summarizes (e.g. John Smith appears on 12 records) to be reportable and dynamic to add new records meeting criteria.

A field to say the value in the field X appears Y amount of times in the whole table.

I can solve this by exporting the Summary Report to a new Table, but then I lose the ability for it to update automatically.
If you need an actual field, then yes you will need to have some daily process like a button to click  to merge in the unique values to a summary table.  I have not tested, but you may be able to make a saved table to table copy  where the values of the field are "merged' into a table where the Key field is the field in question, and have it thus add new records if needed.  Then a user would push that button from time to time to update the master table of unique fields.

The button would  used the urlroot() & "db/" &[_DBID_MY_TABLE_ID] & "?act=API_RunImport&ID=10" as part of its formula ..
Photo of Taylor

Taylor

  • 20 Points
I was hoping to apply this to multiple fields: Phone Number, Address, Email etc. to show me how many times I have different Customer Numbers (my key field, do not want to change). A countif gets me what I need in excel, but would have to operate out of a spreadsheet and do not want to do that. No solution for countif (within the same table) in Quick Base?
I think you'd be into scripting, as opposed to native QuickBase. There is no CountIF functionally aside from  relationship Summary fields.
Photo of Taylor

Taylor

  • 20 Points
What info do you need from me/my app in order to do this via scripting? Would love to see how this could be done to solve the issue we're trying to solve.
I suggest looking up Dan Diebolt on this forum. His contact info is on his Profile. He is all over this forum.

https://quickbase-community.intuit.com/users/513-dandiebolt_qb_pirate#
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Post sample data and desired output report. I am stuck in Microsoft Azure Machine Learning presentation and they have no respect for the math and I am bored ...
Photo of Taylor

Taylor

  • 20 Points
Sample data below.
Don't want a report per say, but want to see when I select Customer Number - 1 there's a field (countif formula) that shows the exact same value in [Customer Phone] occurs 4 times in this table (countif in excel). If it was possible for the value in my countif formula once I click it to bring me to a report with all matching numbers, would be ideal. Then I need to apply this logic/formula to many other fields.

Customer Number Customer Name Customer Phone
1 John Smith 123-456-7890
2 Jane Does 123-456-7890
3 Gilbert Grape 123-456-7890
4 John Deere 321-456-7890
5 Darth Vader 123-456-7890
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
I am on a tablet so I can't do this now. But I think I will just use the Kaaggle Titanic dataset to better demonstrate how to perform a variety of summaries and grouping beyond countiff. Using underscore.js (which QuickBase uses) it is extremely easy to do this using GroupBy, CountBy, Index by and SortBy
methods.
Photo of Taylor

Taylor

  • 20 Points
Any help is appreciated. Thanks for your time!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
I loaded the Kaagle Titanic Dataset which is used for studying Machine Learning Algorithms. This may appear to be unrelated to your CountIf problem but what I will show you will vastly enlarge your universe. Prepare to be blown away as I show you how to use script to produce all manner of summary reports, distinct counts, treemaps etc using this QuickBase data.

Kaaggle Titanic Problem
https://www.kaggle.com/c/titanic

Triangulation 57: Jeremy Howard founder of Kaagle
https://www.youtube.com/watch?v=hxB-rEQvBeM

HighCharts Treemap
http://www.highcharts.com/demo/treemap-large-dataset
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
D> Post sample data and desired output report
T> Don't want a report ...

By output report I meant output in the most general sense. Here is my view of your question: You are asking for a countif feature similar to what is used in Excel. The bottom line is that there is no analogous native functionality in QuickBase. The countif function in Excel takes a (1) range of cells in an Excel worksheet and (2) a criteria and counts up the number of cells in the range that match the criteria and displays this count in some other cell in the worksheet:
COUNTIF( range, criteria )
There is no concept of database tables in Excel and the only native counting you can do in QuickBase is through (1) a summary field defined for a parent child relationship or (2) through a summary report of a single table. So if the counting you want to do does not fit this mold you are out of luck using native QuickBase. You have to take an entirely different approach to find a solution and that solution involves using (1) script, (2) the QuickBase API, and (3) some JavaScript libraries that QuickBase already uses within its pages (notably jQuery and Underscore).
The first step is to obtain the raw data using the API. Unfortunately QuickBase's API returns XML so a short utility function will be used to return JSON from API calls. Next, the JSON representing the raw data is processed is some manner and typically involves counting, indexing, sorting, grouping or performing some arithmetic and concatenation of raw data. This second step is done with script and frequently uses methods in the underscore library. The third step is to apply a template to the processed data to generate some type of HTML output which might be a simple table or results or a complex svg graphic of some sort (for instance a HighChart chart).
Kaaggle Titanic Tasks (click on Summarize By Sex and Embarkation Button)https://haversineconsulting.quickbase.com/db/bkjtq43us?a=td

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

template.html (Underscore Template) https://haversineconsulting.quickbase.com/db/bkjtbpfei?a=dbpage&pagename=template.html
BySexAndEmbarkation.js https://haversineconsulting.quickbase.com/db/bkjtbpfei?a=dbpage&pagename=BySexAndEmbarkation.js
Notes

(1) more to come when I have time ...