Update / Drive Data In A Table From Another Table?

[jwilgus]

Hi,

 

Newb to Caspio, but it is VERY cool.  Wondering if there is a way to update one table from a query of data from another table.  I'm writing an App to learn Caspio and hopefully get certified.

 

For example, I have table with Game Schedules which also supports a submission form to enter in the results of a game.  Simple enough.

 

I also have a Team table that I want to use for Standings ... Wins and losses to be determined FROM the scores entered in the Game Schedules rows.

 

If this were straight SQL tables, I could update Wins as a sum where joining by Team ID and where team score greater than opponents score, etc.

 

So ... In Caspio, is there a way to allow a process that is similar where results from one table cause or can be caused to update values in another table?

 

Thanks.

[Jan]

Hello jwilgus,

 

If I understand correctly, you enter results of a game to your Game Schedules table, and you want to update records in your Team table.

 

I think, you can add Virtual Hidden fields to your Submission form - ids of teams and results for every team.

Then you can use a JavaScript like this to calculate what Team wins and what Team loses and insert this data to correspond Hidden fields.

Then you can pass the parameters to another Submission form and use auto-Submit. If I understand correctly, this form updates results for the first team - and you can pass parameters to a Submission form that updates results for the second team.

 

I hope it helps.

[jwilgus]

Thanks Jan ... I'm not sure if this would work or not.  I'll have to think about it.  When using a submission form to enter the final score of a game, I do have the Team-ID of both the home and away team.  The functions I have to perform then against the Team Standings in the Team Table are:

 

- Increment the "Wins" of the Team with the higher score by 1 (could be home or away team)

- Increment the "Losses" of the Team with the lower score by 1 (could be the home or away)

- allow for the prospect of there being a Tie in which case I increment both teams Ties by 1

- Increment both teams "Points For" and "Points Against" totals based on the points entered for the game.

- Similar Process for Keeping a "Division Record" Wins/Losses/Ties But ONLY if the 2 teams involved in the game are from the same league Division.

 

In theory, ALL of these could be accomplished from the information present on the Game Results Submission Form, but it seems like it would be quite hard to manage.

 

Of course, if this were regular SQL DB, each of those updates could be run by using SQL & SUM to aggregate with appropriate where clause conditions.

 

This would also be possible in existing Caspio Architecture to use submission forms to update the game results, then export the Game results and Team Tables to MS ACCESS, run 4 or 5 update scripts, and then re-import the results from MS ACCESS to "update" the data in the application ... more of a batch approach.

 

Will definitely be interested in future release updates that allow for SQL like functions to be performed between 2 Caspio tables.

 

Thoughts? 

[Jan]

Hello jwilgus,

 

As far as I know, there is Caspio Web Services API. 

Maybe, it can help.