11 Replies Latest reply on Jan 3, 2012 10:25 AM by philmodjunk

    Problem with a class database and registrations.



      Problem with a class database and registrations.


      I am trying to improve my database a little. Right now my database is based off the registrations template. You can click on a button and it brings up a registration list that has all of the people listed in it. WHen you click on the icon to the left it runs a script that adds the person to the class. The problem is that when you do it closed the window so you can't add multiple people. I fixed that so the window stays open, but it resets to the top of the list. 

      If I had 200 people, you could see how it would be time consuming. Is it posisble to have check boxes that I could check to "register" people for a certain class. 

      I would like to also create an Add All button.

      I am not sure what more information you need, but I can submit whatever you need. THanks in advanced for the assistance.

        • 1. Re: Problem with a class database and registrations.

          In this starter solution, you have a many to many relationship that matches many contacts to many events.

          Here's a demo file I set up that includes such a "check box" type interface for making selections. They aren't really check boxes and you are actually clicking buttons with conditional formatting used simulate "check boxes".


          Feel free to ask questions if there's any part of this that doesn't make sense to you.

          • 2. Re: Problem with a class database and registrations.

            I have tried to implement this, but I am all confused as far as which table instance to base it off of. My relationships work, but they are based off the Registrations template and Im not sure that I need some of them. I have attached my relationships graph if you could help clarify a little.

            The Personnel portal that shows all the classes is based on Registrations Contact ID

            The Classes Portal that shows all the personnel for the class is based on Registrations Event ID

            • 3. Re: Problem with a class database and registrations.

              First the basics:

              You can set up a layout based on Classes Event ID or Personnel Contact ID. To base a layout on a specific "box" in manage | database | relationships, you select that box's name in "Show Records From" in layout setup. (This screen appears automatically when you create a new layout.)

              Let's say you want to list all personnel registered for a specific Class. Base your layotu on Classes Event ID. Now use the portal to add a portal to Registration--not Personnel. Put the Registrations::Contact ID field in this portal. You can select any other fields from Registration and Personnel Contact ID that you need. You would format Registrations::contact ID as a drop down list or pop up menu of contact ID's and Personnel names. This get's you to what is called the "basic setup" in my demo file. (Make sure that "Allow Creation of Records via this Relationship" is enabled for Registration in the classes Event ID to Registration relationship.)

              Can you get to that point and get the layout to function correctly?

              We need to make sure the basic set up works before trying the more sophisticated approach with the portal of "check box" like selection controls.

              Two more things:

              1) I see three fields in Personnel table:

              Contact ID

              what is the purpose of these three fields? How do they differ from one another? By the naming conventions used, Conatct ID and _kp_Personnel would appear to be used for identical purpoases and you can remove one and just use the other. _kf_Personnle_ID, on the other hand would appear to be defined in the wrong table.

              2) You may find the following link helpful in understanding the set up of the original starter solution file: Tutorial: What are Table Occurrences?

              • 4. Re: Problem with a class database and registrations.

                Oh yes everything works very well actually. I can register people for classes and it fills in appropriately. I just wanted to switch over to the checkboxes instead of the "blue arrows" to select a person.


                These are old and i deleted them. Not using them. Sorry for the confusion.

                Do I need to get rid of some of the relationships here? It does seem like there is a lot that I probably don't need.

                I am attaching a picture of my layouts and what they are based on.

                • 5. Re: Problem with a class database and registrations.

                  Here is my portal. This is the classes layout and it is based as show above. right now this portal can no be edited. It fills in what you select on the Student selection form.

                  The one for personnel is the same, but it shows the classes.

                  • 6. Re: Problem with a class database and registrations.

                    OK. So far, I've just tried to confirm that the basics were in place. As I understand it, you want to see a list of Personnel so that you can click one to add register the selected person for the current class record that you have up on your screen. That correct?

                    Please note that this is a much more sophisticated approach than the basic set up so it takes some explaining if you can't figure out how it works on your own. Hope you understand what Table Occurrences are. If not, you should click the link in my last post and read as this solution relies on multiple table occurrences of the same three tables in order to work. The only table occurrences needed in this demo file for the checkboxes layout is the group of boxes located second from the bottom.

                    Look at the Check Boxes example layout in the demo file.

                    You'll see two portals. The portal on the right is essentially the portal you show in your last post. It's not needed on this layout, but is present here so you can see more of what happens when you click a "checkBox" button in the other portal.

                    The second portal looks like a list of check boxes. If a contact is registered for an event, you see an X in the box. If not, the box is empty. This portal is based on a cartesian join relationship in order to list all contacts:

                    EventsCheckBoxes::EventID x EchbAllContacts::ContactID

                    (With a cartesian join (x operator), it does not matter what fields you specify in the relationship, you can select any pair of fields you care to select.)

                    There's an extra table occurrence box here, EchbAllContContact_Event that we can ignore for now. It's only needed in the example so that the "paid" check box in the same portal can function correctly.

                    There's a key expression that makes the "check box's" conditional formatting and the script associated with it work correctly:

                     FilterValues ( List ( EchbContact_Event::ContactID) ; EchbAllContacts::ContactID ) )

                    In the conditional format expression, if this is empty, the expression set's the letter X to a size of 500 to make it disappear. In the script, if this expression is empty, the contact is not registered and it then creates the needed record in the join table. If it is not empty, it finds and deletes the record in the join table to "unregister" that contact.

                    Note that EventsCheckBoxes is the table occurrence specified for this layout so all portals, conditional formats and scripts operate from the context of this table occurrence.

                    Due to this relationship:

                    EventsCheckBoxes::ContactID = EchbContact_Event::ContactID

                    List ( EchbContact_Event::ContactID ) returns a list of all registered contacts for the current event. Filter values then compares the current contact ID in EchbAllContacts to see if it is a member of that list.

                    Trace all of that through in the demo file. EventsCheckBoxes is the same as your table of classes. Is the same as your "Registration" table. And EchbAllContacts is the match to your personnel table.

                    With me so far?

                    • 7. Re: Problem with a class database and registrations.

                      Yes, have the form pulling up the contacts, but on the script Add/Remove selected contact what is the "EvDmVIContact_Event" Layout? What should the layout be that I refer to. I am attaching a cleaned up Relationships picture as well as an updated layout list.

                      • 8. Re: Problem with a class database and registrations.

                        If you open Manage | Layouts, you'll find that EvDMVLContact_Event is a layout based on the Contact_Event registration. Any layout based on a table occurrence that refers to the join table will work.

                        You can name your tables and table occurrences whatever makes sense to you, but if you were to follow the pattern in the demo file in the above relationship graph, the lower join table would be named: EchbClasses_Personnel and the Personnel table occurrence would be named EchbPersonnel.

                        Note that this is just a naming convention that I was playing with when I created the demo file. I used an abbreviation at the front to identify the Layout's table occurrence (always on the left with TableName1_TableName2 used to show the two tables being linked in a Join table.

                        Come to think of it, I would have used ClChb or some such to stand for "Class Checkboxes". (Giving all table occurrences in a group the same leading set of characters groups them in dialog boxes and drop downs of table occurrence names.)

                        • 9. Re: Problem with a class database and registrations.

                          Thank You very much. I am sorry that it took so long for me to respond, but I have been working on it for the last week or so. Everything is workin just fine. I even went one step further as to replace the checkboxes and set it up to highlite the name when it is selected. Thank you for your help!!!

                          • 10. Re: Problem with a class database and registrations.

                            One more question. How do you keep the portal from resetting back to the top when you click on a box?

                            • 11. Re: Problem with a class database and registrations.

                              It depends on what that script does. If the script that mouse click performs changes layouts, the portal will reset no matter what options you specify in Portal Setup. If the script does not change layouts, make sure that the Reset Scroll Bar when exiting record option is NOT selected.

                              If your script leaves the current layout, you can save the current portal row number in a variable and rescroll the portal back, using go to portal row after the script returns to the layout. This may not leave the portal completely unchanged--the current portal row may appear at the very top or bottom of the portal--but at least the clicked record is still visible.

                              Set Variable [$Row ; Get ( ActivePortalRowNumber ) ]

                              #Rest of script goes here.

                              #Use the Name box in the top of the inspector's position tab to give your portal an object name
                              Go To Object ["PortalObjectNameGoesHere"]
                              Go To Portal Row [No dialog ; $Row ]