Newbie question: Would the following be difficult to code?

[keuler]

All- I'm a prospective Alpha 5 user. I've got a simple question. Plz refer me to the appropriate place in the Help that describes how I'd do this.....

Consider a club, say, the YMCA. (Members can partake in events. Many members can attend an event. Each member can attend many events. Hence, many to many.)

After entering a member record in the member's table (or locating it in a table search), I'd like to fill in a form associated with the member's record that indicates all events that the member is entitled to join, with a check box next to each event. As the member pays for an event, the data entry person opens the users record and puts a check in a checkbox next to the event FOR THAT MEMBER. As more events are arranged that the member could sign up for, they appear with unchecked boxes when the member's record is next called up.

Does the above functionality require coding to implement? In general, I'm interested to know is how difficult it is to call up a record in a table (say members) and easily add multiple records in related tables for that record. Ideal would be to display multiple forms from related tables. IE, when a member record is entered or found, forms for supporting tables appear adjacent, such as event signups, payment history, etc. The events table in my sample above is a bit special, in that it is dynamic: Whenever a new event is added, it must present the option for that member to the in the associated events table for that user. I think what needs to happen is that whenever a new event is added in the events table, a record needs to be added to a third "signup" table for FOR EACH MEMBER, defaulting to unchecked (false). Similarly, when a new member is added, an associated record needs to be to the signup table for each available event.

How much coding is involved in the above?

Thanks!

-Kurt Euler

[Tom Cone Jr]

Re: Newbie question: Would the following be difficult to code?

Kurt, I've done this sort of thing and take a different approach.

a) I use 3 tables. Members, Regs, Events. Members holds contact information about the member. Events contains one record for each "event" describing the event name, date, price, location, etc. Regs holds one record for each member who signs up to attend an event. The Regs record structure includes both the member name and id which are populated using a table lookup field rule using the Members table as source. No coding required. The Regs record structure also includes the event name and id, and the charge to the member, if any. Again these fields are populated by a table lookup field rule, but this time the source is the Events table. No coding required. The structure also includes an amt_paid field to track amounts paid by the member for each event they've signed up for.

b) A member - Regs set (one to many) can be used to produce reports showing all the events each member signed up for, and whether any unpaid balance remains.

c) A Regs - Member set (one to one) can be used to produce reports showing all the names of folks who are coming to each event, and whether they've prepaid or not.

I do not think it's a good idea to create event registration records for all members when an event is planned, and then check-off whether particular members sign up or not. I prefer to record only members who have signed up to attend.

All of what I describe can be done without coding as long as you are running the system manually from the control panel. However, if you are building this for others to run you will need to do some coding to tie the various sequences together. Much of this can be done using action scripting. The extent of coding will be proportional to your user interface design requirements. The data entry sequences should require no special coding, assuming you use the built in table lookup field rules.

[keuler]

Re: Newbie question: Would the following be difficult to code?

Thanks Tom! A couple follow-ups:

1) In your approach, how would you display, for a selected member, both the events the member signed up for AND available events that haven't been signed up for? If it only displays, by default, events that are signed-up for, can the user add an event for the member by selecting among a list of events that are not signed up for?

2) Is it correct that in your approach, the regs table's lookup fields for member name and event name don't actually contain text, but rather looks up the names based on the ID numbers? So that if a name changes in the member or event tables, the name that appears in the reg table changes too?

Thanks again!

-Kurt

[Tom Cone Jr]

Re: Newbie question: Would the following be difficult to code?

1) In your approach, how would you display, for a selected member, both the events the member signed up for AND available events that haven't been signed up for?

I'd base my form on the set, displaying the registered events for the member in a browse object. This would NOT show the list of other available events. The table lookup field rule would popup a list of all available events when a new event registration record is entered in the browse. User picks the desired event from the popup list. Suggest you spend some time working with the Alpha Sports Sample database. Watch how items are added to an invoice. Study the field rules in the Invoice_items table.

2) Is it correct that in your approach, the regs table's lookup fields for member name and event name don't actually contain text, but rather looks up the names based on the ID numbers?

No. The table lookup field rule would insert the actual member name and id. If the member name or id changes you'd have to run a separate process to update the related registration records. While names change, I'd design my database so that the member id never changes. The id is what the set linkages would be based on, not the name. The id is what the grouping in my reports would be based on. In like manner, I'd design the app so that the event number (id) will not ever be changed either.

-- tom

[keuler]

Re: Newbie question: Would the following be difficult to code?

Thanks Tom.

Re the 2nd question, why would you put the names of the events and members in the regs table? Is this to facilitate reporting? couldn't a JOIN be used to bring in the names in a in a view or update screen?

-Kurt

[Tom Cone Jr]

Re: Newbie question: Would the following be difficult to code?

Kurt, the member id is necessary. The member name is not, and in fact violates normalization rules designed to prevent the storage of redundant data in multiple tables. You could omit the member name, and use a calc field in your table based reports and on your table data entry forms to "lookup" the corresponding member name automatically. This would be an improvement over my previous practice, eliminating the need to "update" a member name if it should change. Ditto for the event names. Optionally, I could base my report on a set containing the "name" corresponding to the "id", and then drop the table field into my layouts.

Your questions suggest you have worked with databases before. Maybe now it's time to use the trial version of Alpha Five to see how you like it? It's my experience that asking hypothetical questions doesn't take me as far as trying to build something for myself.

[keuler]

Re: Newbie question: Would the following be difficult to code?

Tom- Good advice. Thanks!