5 Replies Latest reply on Mar 7, 2017 3:09 PM by philmodjunk

    Re-orering Wait List

    MusicCampMinion

      I am hoping to get some help with a wait list I am trying to create.

       

      I have a wait list which tracks students that want to attend a particular camp but their instrument is currently full.  For example, someone wants to attend Jazz Camp on Drums, but Drums is full.  There may be multiple instruments for each camp that are closed but a persons position on the wait list is determined by the number of students who have already been placed on the list but play the same instrument. 

       

      Records on the wait list are created through a portal located on appropriate record the CAMP table.

       

      What I can not figure out is how to do is to create a number that represents the students current position on the list, and have that number re-order when a person is removed from the list.

       

      Any thoughts?

       

      Thank you so much for your help in advance.

        • 1. Re: Re-orering Wait List
          DavidJondreau

          Is the number for display only or do you need to report on it or e-mail it or see it somewhere else?

           

          If just display, you can Insert an "Other Symbol..." layout object of RecordNumber into the portal row.

          • 2. Re: Re-orering Wait List
            philmodjunk

            It's hard to answer this one effectively without more info on how you manage all this currently in your solution.

            • 3. Re: Re-orering Wait List
              siplus

              If the only criteria you use in your waiting list is the timestamp of the student's request, it's easy. When there's a removal in the list you sort students - enrolled or not - in that class on the InscriptionTS and do a replace field contents with a incrementing serial.

              • 4. Re: Re-orering Wait List
                MusicCampMinion

                Thank you for your response (and all of the other responses). 

                 

                The number is for display purposes only.  I'm not sure which Other Symbol might apply here since it is not just a straight count of all portal rows or table records as it is predicated by the camp and the instrument that they play.

                 

                So here is a little more detail - I apologize for the novice language:

                 

                I am on a layout on the CAMP table.

                The portal is to the WAIT LIST TABLE and they are connected by the campID.

                Each record created on the Wait List Table contains a primary wait list ID, a foreign campID, and a foreign instrumentID.

                 

                So a user might be on the CAMP table record for String Orchestra Camp.

                When a new portal row is created via the WAIT LIST portal, the user will enter the students contact information and select the instrument the student plays (from a value list which only shows instruments that apply to that particular camp and that are also closed).  I've attached a screen shot which I hope might help a little.

                 

                Screen Shot 2017-03-07 at 3.38.17 PM.png

                 

                Because we may have multiple instruments closed for each camp at the same time (for instance both violin and cello are closed at String Orchestra Camp), I need a number which indicates the students order on the wait list with each instrument for that camp being numbered separately.  So, there could be students who are #1-#5 on the list for Violin, and also students who are #1-#5 on the list for Cello.   Then when students are called off the wait list, and everyone needs to move up a position, the numbers re-order just for that instrument. 

                 

                So the position number needs to take into account the campID and the instrumentID that the wait list record is created for.  Right now we are doing the list position number manually which is cumbersome when the lists get long.

                 

                Please let me know if there is any other information I can supply to make this clearer.

                 

                Thank you!

                Kerry

                • 5. Re: Re-orering Wait List
                  philmodjunk

                  First, I suggest a "self Join" from the waiting list table that matches to a second occurrence of that same table, by camp ID and instrument. That will match any given record on the waiting list to all other waiting applicants with the same instrument and camp. You may need to add a third match field if you change a value in a field to drop a camper off the waiting list when they are enrolled or cancel.

                   

                  Then, use this script to assign or update these values:

                  1. use Go To Related Records to pull up a found set of all the waiting campers for the same camp and instrument.
                  2. Sort them by the previously suggested Creation Timestamp field.
                  3. Then Use replace field contents with the serial number field to renumber them starting with 1 for the first record in this found set.