Update database count from another things records

Need help Database
1

Hi all,

I am working on an app to track results and performance of tennis matches with my friends. At the moment I have ‘hard coded’ it on the front end where against each player I count the ‘results’ where they appear.

I think it makes more sense to have the calculation in the back end and then the front end can access the unique counted fields which I can then filter with also.

What I am trying to figure out is:

  • ‘User’ has fields for ‘Singles Played’, ‘Singles Won’ and ‘Singles Win %’
  • When a new ‘Singles result’ is created then:
  • ‘Singles Played’ count of ‘Singles results’ where User is present (in a list of ‘allPlayers’)
  • ‘Singles Won’ count of ‘Singles results’ where User = ‘Winning player’ field
  • And then run a new calculation within ‘User’ of ‘Singles won’ / ‘Singles Played’

I have been searching forever and cannot figure out how to initiate the counting of records and updated all of the new users records to stay up to date.

My current solution is when a ‘Singles result’ is created > scheduled API workflow to + 1 to each of the above mentioned fields and then run the calculation as a Result of Step 1 for example.

Any thoughts? and help would be appreciated.

Thanks,
Nick

2

you’d have to initiate the count whenever a record is created or deleted

  1. record created
  2. trigger backend workflow
  3. update counts and math on user

although you will likely find that it will be more performant to just calc this on page load unless it is used in a lot of places in the app or used in searches (in which case you have to store it to be able to search by it)

I wouldn’t do +1 but instead do a search for:count as this will ensure the count is always accurate - whereas +1 -1 can easily miscount over time