Update one form from another.

[focault]

In the site I am making I have a list/form called courses. Admins can add a new course and the amount of places available on that course.
Users have a form called Subscriptions which using a database join allows the user to select a course and also it has a join for the amount of places left.
If there is space I want the user to be able to sign up for the course and also for the places available on the courses list/form to then be updated with -1 place.

I would also like to change the users group when then have subscribed, I am using Community Builder for user management.
Can you point me in the right direction for this?
In mysites you can find all the login info - the lists are called Courses and Subscriptions.

Thanks in advance.

 

[troester]

You can add a form php plugin (onAfterSave) to modify values in other DB tables.
See http://fabrikar.com/forums/index.php?wiki/common-php-tasks/
for how to look up and update.

 

[focault]

Thanks troester I got a bit lost on this but cheesegrits is helping me with this one on Skype

 

[cheesegrits]

I seem to recall we got as far as deciding that the best approach is to use a MySQL view, to maintain a "course_places" view. Trying to maintain running totals via submission scripts, for instance by decrementing counts when a booking form is submitted, is a LOT harder than it sounds. Far too many gotchas. From something as simple as "what to do if you delete a row in the bookings table" (so you need to bump the available places count back up), to the more esoteric "edit an existing booking form, and change the number of places required" (which means working out what the difference is between the number of places before and after the form was loaded and submitted).

Much easier to have a view which uses a query that dynamically counts the total number of places reserved for each course in the bookings table, and subtracts those totals from the total places available field in the courses table. the SELECT query to do that kind of thing can get a little hairy, but still a lot easier than trying to do running totals any other way.

The only other alternative is to use MySQL Triggers, but those have their own pros and cons.

-- hugh