Insert a record in another table after ticking checkbox

[CraigSZ]

I have 4 tables which are Players, Teams, Games & Availability. Players can belong to more than 1 Team and a Team can have multiple Games. I also have a "link" table for Teams/Players to record which Players are part of which Teams. I've attached a diagram with basic tables and the relationships.

I would like the Player (user) to see a list of ALL games for the that Teams they belong to and then be able to mark if they are available for the game by ticking a checkbox. Clicking "yes" would insert a record into the Availability table (if it doesn't already exist).  I've attached a diagram of the view I'm trying to create.

I've tried a few things but haven't been able to achieve what I need...

• I cannot use a view as there are no records in the Availability so you cannot edit the data (shows text false/true and not checkbox).
• A single submission form does the right thing (i.e. inserts a record into availability table) but doesn't work for the user as dropdown lists only show a single field so the user is unable to see other information about the date, time, location etc of the Game
• A tabular report, with detail, would not work as the Detail is in a different table. It would also mean that the user has to click on the detail button, select "yes" to update the single Availability and then return to the report view which is not a good user experience.
• I don't want to insert a record into the availability table in advance as that means having multiple triggered actions on each of the Players, Teams & Games tables which would cause maintenance issues and would bloat the database by having obsolete records.
• I could do this via a "dummy" datapage that receives a value of "yes" and creates the Availability record but that means using up a licenced datapage and also seems to be a workaround.

Is there perhaps a way to do this via a Report and Virtual Fields? If the user checked "yes" for multiple games then they would all need to be updated but I don't think Caspio handles multiple submissions so perhaps each checkbox needs to "fire" independently but unsure if this would take a few seconds to run or refresh the webpage which results in a poor user experience.

I may be missing something or maybe I don't understand the options/functionality in Caspio but I would appreciate any suggestions on how to achieve the above.

TIA

Craig

[NiceDuck]

Do you have a predefined data for this tables? 

I mean, Do you have an already existing data for this and you only want to display them or you also have a problem on how are you going to collect the data and save them in that pattern?

[CraigSZ]

31 minutes ago, NiceDuck said:

Do you have a predefined data for this tables? 

I mean, Do you have an already existing data for this and you only want to display them or you also have a problem on how are you going to collect the data and save them in that pattern?

Hi NiceDuck,

I don't have the data yet. I have an existing table and I need to insert/create the data in the table and then display it in that view/pattern so that the user can set the checkbox to yes/no.

My screenshots were from MS-Access and you don't need to (i) create the data records and (ii) display them for edit as Access automatically creates the data records  as you use the checkbox in the query/view.

I was Caspio would do something similar but can't find a way to do it.

I think I am going to need a Triggered Action to create data entries in the table when certain events occur e.g. if a new Player joins a Team then they must get a record created in the table for each Game. I can then create a report/pattern where the user can set the checkbox to yes/no.

Would appreciate any ideas/suggestions.

Thanks

[CraigSZ]

All,

I have now done the following to resolve the issue:

1. When a Player is added to a Team then a triggered action now fires to create an Availability record for each existing Game in the team

2. When a Player is removed from a Team then a triggered action now fires to delete the Availability records for every Game in the team

If both triggered actions are not used then you have problems if the same player rejoins the team at a later stage which may happen.

I will also need to create additional triggered actions to fire when...

a. a new Game is added so that an availability record is created for all Players in the Team where the Game was added

b. an existing Game is deleted so that the existing availability records for the Game are deleted for all Players

c. a Team is deleted so that all availability records for all Games and Players in the Team are deleted

I would still like to know if there is a better way of doing this in Caspio as I will land up with 5 triggered actions, on 3 different tables, which need to be documented & maintained.

TIA 

[NiceDuck]

It appears to be doable but we have to make sure that your tables are normalized.

for the 1 and 2, you can make a trigger like this to add or delete records from one table to another table.

 

I am still checking on the other conditions you have when I have free time.

 

I really think it is doable, you just really need to make sure that your tables are normalized
 

[CraigSZ]

Thanks for your response @NiceDuck

I have managed to resolve the issue by creating triggered actions. I was trying to see if there were any other ways to do it without triggered actions (e.g. MS-Access automatically creates the records from the query) as I needed to create a number of them depending on the circumstances.

Regards,

Craig