10 Replies Latest reply on Feb 17, 2012 6:37 AM by GunnarStrand

    Need to show when volunteers are available



      Need to show when volunteers are available



      I am making a db for a festival. We have several concerts and lots of volunteers. I have created a layout where I can fill in all the information about each concert.

      I have a portal where I can choose (from a valuelist in the first portal field) the volunteers from the "volunteers" table and the result is stored in a joining table ("WorkConcert") between "volunteers" and "concert". This works fine. But I would like to be able to filter out the volunteers that are not available on the date of the concert. I now have a available field on the volunteers table with a checkbox field based on the concert dates. I have tried Portal filtering using PatternCount ( Concert::Date ; Volunteers::Available ) but this only works if a vlounteer is only avialble one day. And the volunteers still show up in the valuelist but won't show in the portal if they are unavailable, very confusing and obviously not the way to do it. 

      The perfect solution (i think) would be if the valuelist it self only showed the volunteers actually available on the concert date. 

      Second question is there a way to avoid that a volunteer can be selected twice in the portal?

      I really appreciate all help, thank you!

        • 1. Re: Need to show when volunteers are available

          I don't quite follow how you are tracking volunteer availability. This can be tricky as you are comparing the date range of the concert event to the date range of the volunteer's availability unless you are scheduling volunteers one day at a time and if that's the case you can simplify this by checking whether this single date falls in the date range of the concert event. So if you can describe what you have done in more detail, it may help.

          Also, do you manage multiple, overlapping concert events or just deal with one concert event at a time?

          What you are describing is, in general terms, called a conditional value list. Do you know how to do that?

          And avoiding the selection of the same volunteer twice can be done with validation rules, dwindling value lists that drop out available volunteers as they are selected and a selection portal with a "checkbox like" format that precludes selecting the same person twice because clicking them a second time removes them from the list.


          • 2. Re: Need to show when volunteers are available

            Thank you so much for your response. I will look into the file you attached.

            The availabillity is tracked (or tried to track) through a set of checkboxes from a valuelist (populated with the concert dates) in the available field in the voulunteers table. But I am sure there is another and proper way to do this. 

            And yes there are multiple, overlapping concerts and volunteers may have to work more than one concert on the same day. Around 30 concerts in 9 days.

            Volunteers sign up on a day to day basis and may have different tasks form day to day. I would like to make the picking process as simple and foolproof as possible for my colleagues.

            • 3. Re: Need to show when volunteers are available

              So the volunteers select the dates for which they are available. We can work with that, though I suggest a related table with a date field to record each date available as this will be needed to set up your conditional value list of available dates. A portal to such a set of related records can even be set up to look and function like the above set of check boxes, but let's the values be treated as actual dates instead of text.

              I need to clarify why I asked about multiple overlapping concerts. I'm checking to see if assigning a volunteer to a concert event then needs to show them as unavailable for other concert events. I don't think that's the case here if "volunteers may have to work more than on concert on the same day..." Either that or we also need to consider the Time of day they are available as well as the date--a major complication here.

              I was picturing a series of "concert" events where one band performs at one location at a given date and time. This sounds like you have a festival event with multiple concerts. If so, do you manage more than one festival event that takes place at the same time? (I hope not as that sounds pretty much impossible to do just in human terms.)

              • 4. Re: Need to show when volunteers are available

                All the complications you mention are true, I guess. There may be (the schedule is not done yet) more than one concert at the same time and one volunteer may work more than one concert in a day and yes if a volunteer is assigned to one concert they are unavailable for the duration of that concert. But most of the time it is not like that so I can figure out all that the old fashion way when it occurs. 

                I will be more than happy with being able see who has signed up to work on any given day and have that reflect on the list that I can choose from in the concert / activities layout where most of the information is entered. 

                I will start by setting up the related table as you suggested.

                Thank you so much for helping, I really appreciate it! 

                • 5. Re: Need to show when volunteers are available

                  Single date checking to see if a given volunteer is or is not available based solely on the records in the related table of dates available:

                  Volunteers::VolunteerID = AvailableDates::VolunteerID

                  If you then establish this relationship:

                  SomeOtherTable::SelectedDate = AvailableDates::DateAvailable

                  You have a relationship that you can use to get a conditional value list of available Volunteers by their volunteer ID.

                  You can list AvailableDates::VolunteerID in field 1, Volunteers::FullName in field two

                  and specify Include Only related values starting from "SomeOtherTable"

                  and you'll have a value list limited only to those available on the date you specify in SelectedDate--which can be an event date if you set up your events table as "someOthertable" or it can even be a global field with a pop up calendar for selecting a date to check on volunteer availability.

                  Let's get that working before we look into a dwindling value list--if you still want that.

                  • 6. Re: Need to show when volunteers are available

                    Thank you so much! I got it working.

                    A dwindeling list would be perfect, but it needs to be spesific to the event as well and not just the date, as volunteers may work more than one concert during the day.

                    Is that possible?


                    By the way, I got the final draft of the festival program and there are no overlapping concerts, so thats nice. 



                    • 7. Re: Need to show when volunteers are available

                      It's possible, but I don't have a clear enough picture of your tables and relationships to spell out the details. Relationships can include more than one pair of fields so by adding more pairs of fields we can create a more complex relationship such as one that drops out volunteers that have already been selected for a given date or a given event that takes place on a given date. The trick is a calculation field that produces a list of values of all emplayees already selected, then you include this field in your relationship with the  ≠ operator to filter out selected values.

                      • 8. Re: Need to show when volunteers are available

                        Thank you so much for all your help.

                        In fear of exploiting you kindness I have included a picture of my relationships incase you have time to guide me a little further and there is a way to create what we talked about based on what I have.

                        • 9. Re: Need to show when volunteers are available

                          The demo file for which I posted a download link demonstrates a dwindling value list. It matches contacts to events much like you match volunteers (Frivillige) to Concerts And other Activities (Aktivtet). As you attempt to implement this, you may want to download, open and examine that file to see how it was done there.

                          Let's confrim we are at the same starting point.

                          We have a layout to Aktivitet with a portal to jobbekonsert where we create new records to link the current record in Aktivitet to a record in Frivillige.

                          You want to do this by selecting from a dwindling value list of volunteers inside this portal.

                          Or do you want to see a portal listing volunteers where clicking a portal row selects that volunteer?

                          To get a list of available volunteers--either in that portal or in the conditional value list would seem to require this relationship:

                          Aktivitet::Dato = Tilgjenglig 2::TilgjengligDato   (I am assuming that Dato is a field of type Date.)

                          Tilgjenglig 2::FrivilligID = Frivillige 2::__pkFrivillige

                          If we are both on the same page here, the task is to modify the first relationship that I've posted here to drop out volunteers already selected for this activity. I'd try dropping them off the list if they were selected for this date, but that keeps you from assigning a volunteer to more than one activity on the same day.

                          • 10. Re: Need to show when volunteers are available

                            Hi, again and thank you again. 

                            I got it to dwindle and I got it to sort, but not to sort and dwindle.

                            And allthough I would love to learn how to do that, maybe it is over complicating this project. Since there are no concerts at the same time, I only need to know who are available on the day.

                            I implementet the duplicate check form the first example in the db you sent, so that it is impossible to select a voulunteer twice for the same concert. I also made a conditional valuelist of jobs that the volunteers have signed up for that shows up in the portal, so that it is easy to assign jobs based on individual preferance and abillity. 

                            I'll use the conditional valuelist for picking volunteers and the dwindeling for picking artists, just for variation and fun.

                            I really appreciate all your help and wish you a great weekend.