Relating F1 Position with Points Gone Wrong

[peterkota]

Hi all,
Im building a little app for my wife who is having an F1 watch party whereby everyone who attends can add their race predictions in from 1st-10th.

Each position they get correct they should receive the certain amount of points relative to that position, but I cant seem to make this work.

As a test, i have entered my predictions and some fake race results.

The problem is even if a driver is selected but doesnt finish in the same position as my prediction, i still get points for it.

eg I Selected Alexander Albon to finish 2nd, but he finished Last, yet i still get 5 points when i get 0 as my prediction was wrong.

See screenshots attached!

[Atom]

You’re on the right track! You really just need to add a couple more columns in the Predictions table.

1. Add a Lookup for the Driver name for each of the Relations. Do 1st_Driver, 2nd_Driver etc.

2. Add an If → Then → Else column where: "IF 1st is 1st_Driver, then 1st_LOOK, Else 0 "

3. You can then add a math column to sum up all the IF THEN ELSE columns for a total points Column.

Let me know if you have any questions!

[ThinhDinh]

You might also want to bake in a column for a “Race ID”, in case you want ot have more of these watch parties later and reuse the app.

[peterkota]

Thanks so much for the help, it makes sense, but i dont know what im doing wrong! See attached.

[peterkota]

That would be awesome!

Would i add RaceID into the Predictions AND Race Results? Where should it live?

[Atom]

You need to do the following:

1. Add a Row ID column to the Race Results table

2. Add another Lookup column in the Prediction table, that looks up the Row ID column of the Race Results. Call it ID for reference:

3. Change the If → Then → Else to “IF ID = [MANUAL], THEN 1st_LOOK, ELSE 0”

Where [MANUAL] is the Row ID that corresponds to that position. For example, the 1st_IF value for [MANUAL] should be the ROW ID of “1st Position” in the Race Results table.

[ThinhDinh]

I would do it like this:

Races: Add a table for Races, with RowID, Race Names.

Predictions: Add a column for RaceID (RowID) coming from Races, I assume people will be able to predict 1st, 2nd, 3rd etc. Craft a JSON column to have a centralized “string” for all predictions from a player.

E.g:

{
"1st": "John Doe",
"2nd": "James Doe"
}

Race Results: Add a column for RaceID, and then a JSON like this.

{
"Position": "{POS}",
"Driver": "{DRI}",
"Points": {PTS}
}

In Predictions, use a multiple relation with the RaceID to the Race Resuls table, then a Joined List column to get all the templates back, separate by a comma and a new line character.

Add a template column with the template being:

[
R
]

With R being the Joined List above (formatted to true JSON).

Then, with a JSON for the player’s prediction, and a JSON array for the race results, you would use JavaScript to get the final points.

[ThinhDinh]

Based on your data, sample JSONs would look like this:

Player Prediction JSON:

{
  "1st": "Carlos Sainz",
  "2nd": "Alexander Albon",
  "3rd": "Lando Norris"
}

Race Results JSON:

[
  {"Position": "1st", "Driver": "Carlos Sainz", "Points": 25},
  {"Position": "2nd", "Driver": "Lando Norris", "Points": 18},
  {"Position": "3rd", "Driver": "Oscar Piastri", "Points": 15},
  {"Position": "Last Place", "Driver": "Alexander Albon", "Points": 5}
]

The JS function:

function calculatePoints(predictionsJSON, raceResultsJSON) {
  // Parse the input JSON strings
  const predictions = JSON.parse(predictionsJSON);
  const raceResults = JSON.parse(raceResultsJSON);

  // Create a mapping of positions to drivers from the race results
  const resultsMap = {};
  raceResults.forEach(result => {
    resultsMap[result.Position] = { driver: result.Driver, points: result.Points };
  });

  // Initialize total score
  let totalPoints = 0;

  // Compare predictions with actual results and calculate points
  for (const [position, predictedDriver] of Object.entries(predictions)) {
    if (resultsMap[position] && resultsMap[position].driver === predictedDriver) {
      totalPoints += resultsMap[position].points;
    }
  }

  return totalPoints;
}

return calculatePoints(p1, p2)

I got 25 for predicting the 1st position right.