Multi-Level Relational Database Question

I am trying to build a online relational database for my local non-profit, a community theatre.

A list of productions          Edit a Production         product dates for parent production
         |                           |                                  | 
         V                           V                                  V
ProductionsList--one to many---->Production----one to many---->ProductionDatesList---- 
        ^                             ^                                 ^             |
   ChronoConnection                Chronoform                    ChronoConnection     |
                                                                                      |    
                                                             ProductionDate Edit <----
                                                                         ^
                                                                         | 
                                                              Edit Production Date Data
                                                                   Chronoform

I want the user to move smoothly though the forms by drilling down.



I am using both Chronoforms and ChronoConnectivity. I have registered them both and I am using version CC V2_RC3 and CF V3.2.0. My problem is the move from the Production Edit to the list of dates for that production. I have struggled unsuccessfully to get this to work with the multipage plugin (might be me). I am currently awaiting delivery of my copy of ChronoForms 3.1 for Joomla! site Cookbook.

So my questions are:

1. Can I do this with the two products or is it too complicated?(I hope not, I really like working with it)

2. If the way to accomplish this is in the book I will gladly wait, no use giving away what you can sell.

3. If that is not the case can you give me a push in the right direction?

Hi fredm,

I don't think I'm following this correctly :-(

My reaction is to suggest that you use two connections in a row, the first for the Production and the second for date. Or, if there are only a few dates for each production you could use a single CC Listing for both with some filters to help find the results (you'd need the modified version of CC from the forums here to handle the query though).

Bob

First let me thank you for your quick response. Man, you are fast. As I slept I have refined my question a bit. Ideally, from the production list ChronoConnection I would make a selection which would take the user to an editable chronoform based on the id passed to it in the top half (maybe in the header) and a Chronoconnection in the bottom half (in the body). I think (I have not tried, just got up) it sounds like there would be a way to make it work, I just can't make the mental leap to figure out how to do it. In lieu of that I could have the Production Chronoform call the ProductionDates with a link or button of some type?

Just some more problem definition info. There are two tables Productions and ProductionDates. There is a one-to-many relationship (their is a foriegn key in ProductionDates containing the cf_id of the parent Production), each production can have 0 - many ProductionDates. I want the ProductionDates Connection to be accessible from the ProductionEdit Chronoform. Put as simply as I can think, Can a Chronoform pass a cf_id to a ChronoConnection for use in it's query where statement. I hope I am making it a bit clearer. Thanks again for your help.

Hi fredm,

Sorry, I can't visualise what this would oook like - or what it would let the user do?

What is the purpose of the listing + form in the same page (That might be possble but I'm not sure).

The image below is of a form with a list of dates below the submit button - is this the kind of thing you have in mind?

Bob

Yes something very much like that with the top half containing fields from a record in the production table and the list records from the ProductionDates table where all of the ProductionDates.fkProduction fields = Production.cf_id field.

Its not really necessary that the form contain the conneection, I just need to go from Production connection to Production editform then if the user decides to go from to ProductionDates Connection. Any Ideas? I am trying a link button the form to call the same link the menu does for the connection but thats not working.