Best way to analyze data?

[Brian_Schuster]

What’s the best way to do a simple analysis of data? One of the most basic things I want to do is count the number of records that match a set of filter criteria.

[Bill_French]

Brian_Schuster:

What’s the best way to do a simple analysis of data?

Are you skilled in simple javascript?

[Brian_Schuster]

I would not say skilled, but I’ve written some. What would you type into the JS block to do it?

[Bill_French]

Brian,

Brian_Schuster:

What would you type into the JS block to do it?

Let’s set some expectations at the outset. We’ve not defined “it” or “simple analysis of data”. Until we know what these terms actually mean, there is no helpful answer to this question and there is nothing to type into a Script Block. :winking_face:

Analysis of data is at least on the doorstep of data science which requires a little planning and a stated objective. Below is a reference to one script block I built ages ago that generates a forecast based on historical data and then takes that model to a little higher level with machine learning (simply, as new operational results are added, the forecasts learn and become more precise. This example includes the script block code and liberal comments so you can get a sense of the script itself.

Forecasting & Machine Learning Scripting app

While scoping a new Airtable solution for a client, the topic of forecasting came up and specifically by employing machine learning. There are many definitions of machine learning and while my focus was to at least prove to myself that a Script Block could perform the forecasting process natively (i.e., without calling out to any external services), I took a stab at crafting a simple data science block to forecast sales by month for the next three years. Setting the Table - Aggregation by Month…

To get a few enterprising script block designers to guide you in this community, I recommend you take some time to give us an example of some data and an analysis you’d like to perform. Be as detailed as possible because no one like to write code for free to later discover that they wrote the wrong code.

[Brian_Schuster]

Sure, yes details are good to have before beginning a project.

Two bases of interest:

• General Items
• Specific Items

A general item is created by me through Airtable. A specific item is created by a user through an app. Each general item has a list of specific items that belong to it from various users. The app software I’m using doesn’t currently allow custom Javascript unfortunately. I am also limited in that I cannot create linked fields with the data coming from the app, so I cannot use rollup fields.

So my goal in Airtable is:
Count the number of specific items that belong to a general item without using any linked relationship fields. I am using General Item ID as a field in the Specific Items base to make the connection manually.

[Bill_French]

@Brian_Schuster,

Here’s a start … it’s a block that lets you determine the occurrence of a given string in a text field for all records in the selected table.

Source Code
/*

   ***********************************************************
   Airdrop - Counting Example
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

output.markdown('# Counting Example');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Field to be Counted");
let sourceField = await input.fieldAsync("Pick the name of the field to be counted:", sourceTable.id);
let sourceFieldName = sourceField.name;

// set the value to be counted
output.markdown("### Enter the Value to be Counted:");
let thisQueryValue = await input.textAsync('What value do you want to count?');

// get the records
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// iterate across all the records
let thisCount = 0;
let thisFieldValue;
for (var r = 0; r < sourceRecords.length; r++)
{
    // get the field value for this record
    thisFieldValue = sourceRecords[r].getCellValue(sourceFieldName);

    if (thisFieldValue != null) {
        
        if (thisFieldValue.toString().toLowerCase().includes(thisQueryValue.toString().toLowerCase()))
            thisCount += 1;
    }

}

// display the count
output.markdown("Counting the occurrence of **'" + thisQueryValue + "'** in field **'" + sourceFieldName + "'** = **" + thisCount + "**");

[Brian_Schuster]

hey Bill, I like the simplicity of the layout.

It doesn’t quite do what I need though. This is how I would break it down:

• In the General Item base, modify the first record’s “Specific Item Count” and then iterate across all General Item records.
• For each entry, look up all of the Specific Item base records that have a “General Item ID” property that matches the General Item ID in the General Item base for the record currently being reviewed.
• Count those records and enter the quantity into the Specific Item Count. Iterate across all General Item records.

[Brian_Schuster]

I see in the Javascript block examples there is an example for Running Total for a particular base view. Is there a way to apply that script to make counts for filtered groups within a view?

[Bill_French]

Brian_Schuster:

Is there a way to apply that script to make counts for filtered groups within a view?

Sure - it’s just a matter of writing script to embrace the data in a way that hives you the expected metrics.