Help with Running Totals

[jkatz1797]

I've created a base for my softball league and could use a second set of eyes on a problem I can't seem to crack. I want to be able to input the game scores in the "Schedule" tab and then have everything else update accordingly (runs scored, runs allowed, standings, etc.). I'm not sure how to accomplish this. 

LINK TO THE BASE: https://airtable.com/appyRHUtLhxlpEWFY/shrn9oSvblGrwjaEY

I also have this script I could run, but would need to upgrade my plan and I'd rather not have to do that:

let gamesTable = base.getTable("Games");
let standingsTable = base.getTable("Standings");

let gamesQuery = await gamesTable.selectRecordsAsync();
let standingsQuery = await standingsTable.selectRecordsAsync();

let standings = {};

standingsQuery.records.forEach(record => {
    standings[record.getCellValue("Team")] = record;
});

gamesQuery.records.forEach(record => {
    let homeTeam = record.getCellValue("Home Team");
    let awayTeam = record.getCellValue("Away Team");
    let homeScore = record.getCellValue("Home Team Score");
    let awayScore = record.getCellValue("Away Team Score");
    
    updateStanding(homeTeam, homeScore);
    updateStanding(awayTeam, awayScore);
});

async function updateStanding(team, score) {
    let standingRecord = standings[team];
    let currentScore = standingRecord ? standingRecord.getCellValue("Total Score") : 0;
    let newScore = currentScore + score;
    
    if (standingRecord) {
        await standingsTable.updateRecordAsync(standingRecord, {
            "Total Score": newScore
        });
    } else {
        await standingsTable.createRecordAsync({
            "Team": team,
            "Total Score": newScore
        });
    }
}

[jkatz1797]

@kuovonne Could you take a look at this? I see your answers all over these forums and I feel like I'm losing my mind 😕 or if you point me to someone else who could help! I'm even using an automation and script and nothing is happening.

let table = base.getTable("Spring Schedule 2024");

let standingsTable = base.getTable("Spring 2024 Divisional Standings");

let teamRecords = {};

let teamRecordsQuery = await standingsTable.selectRecordsAsync();

for (let record of teamRecordsQuery.records) {

let teamName = record.getCellValue("Team Name");

teamRecords[teamName] = record;

}

let query = await table.selectRecordsAsync({

fields: ["Away Team", "Home Team", "Away Team Runs", "Home Team Runs", "Winner"]

});

for (let record of query.records) {

let awayTeam = record.getCellValue("Away Team");

let homeTeam = record.getCellValue("Home Team");

let awayRuns = record.getCellValue("Away Team Runs") || 0;

let homeRuns = record.getCellValue("Home Team Runs") || 0;

let winner = record.getCellValueAsString("Winner");

if (winner) {

await updateStandings(awayTeam, homeTeam, awayRuns, homeRuns, winner);

}

}

async function updateStandings(awayTeam, homeTeam, awayRuns, homeRuns, winner) {

let awayStandingsRecord = teamRecords[awayTeam];

let homeStandingsRecord = teamRecords[homeTeam];

if (awayStandingsRecord && homeStandingsRecord) {

let awayWins = awayStandingsRecord.getCellValue("Wins") || 0;

let awayLosses = awayStandingsRecord.getCellValue("Losses") || 0;

let awayTies = awayStandingsRecord.getCellValue("Ties") || 0;

let awayRunsScored = awayStandingsRecord.getCellValue("Runs Scored") || 0;

let awayRunsAllowed = awayStandingsRecord.getCellValue("Runs Allowed") || 0;

let homeWins = homeStandingsRecord.getCellValue("Wins") || 0;

let homeLosses = homeStandingsRecord.getCellValue("Losses") || 0;

let homeTies = homeStandingsRecord.getCellValue("Ties") || 0;

let homeRunsScored = homeStandingsRecord.getCellValue("Runs Scored") || 0;

let homeRunsAllowed = homeStandingsRecord.getCellValue("Runs Allowed") || 0;

if (winner === awayTeam) {

awayWins++;

homeLosses++;

} else if (winner === homeTeam) {

homeWins++;

awayLosses++;

} else if (winner === "Tie") {

awayTies++;

homeTies++;

}

awayRunsScored += awayRuns;

awayRunsAllowed += homeRuns;

homeRunsScored += homeRuns;

homeRunsAllowed += awayRuns;

await standingsTable.updateRecordAsync(awayStandingsRecord.id, {

"Wins": awayWins,

"Losses": awayLosses,

"Ties": awayTies,

"Runs Scored": awayRunsScored,

"Runs Allowed": awayRunsAllowed

});

await standingsTable.updateRecordAsync(homeStandingsRecord.id, {

"Wins": homeWins,

"Losses": homeLosses,

"Ties": homeTies,

"Runs Scored": homeRunsScored,

"Runs Allowed": homeRunsAllowed

});

}

}

[kuovonne]

I'm afraid that it is not clear why your script is not doing what you want it to do. It looks like you figured out that you need to await your async function. But I do not think that revising this script is the way to go.

It looks like you are using single-select fields to indicate your away and home teams, instead of linked record fields. I suspect that if you had a different schema that used linked record fields, you would be able to calculate the statistics you want using rollup fields, without any scripting.

The support website has info on linked relationships and rollup fields. If you need more guidance on revising your schema (and if you have budget), consider hiring a consultant to go over your schema.