6 Replies Latest reply on Oct 21, 2010 3:23 PM by Nerico_1

    "fudging" a filtered value list

    Nerico_1

      Title

      "fudging" a filtered value list

      Post

      I have a database that is used to manage the interview process for med school applicants. I have an INTERVIEWS table that is related to the APPLICANTS table via the applicant's ID field. 

      In order to assign an applicant to a particular interview, I use a value list using the applicant's ID field and also displaying their full name in the second field.  I started out using the APPLICANTS table as the source for the value list but as the list of applicants grew it became harder to tell which applicants were available to assign to the interviews on any particular day.  My shortcut was to create a "temp" table where I just store 2 fields, the applicant ID and the name.  I manually clear the records after each interview date and re load with the IDs and names of the applicants for the next interview date.  I would like to do this via a script but I'm not comfortable enough with the FM scripting syntax to know how to do this.

      The basic algorithm would be to open the temp table, clear all records, then look at the applicants table and if an applicant is schedule for the specific date, create a record with their ID (the name field populates thru a calculation) and then find the next applicant until the list is done.

      I would really appreciate it if someone got me started with some sample code?

      My other solution had been to add a field to the temp table to keep track of the date that the applicant has been invited for interviews.  Since my main layout is based on the Interview date (with the interview slots shown thru a portal) I thought the value list might also get filtered by the date of the selected record in view just like the portal records, but either this is not the case or I went about it the wrong way.  So my other question is, did I mess up?  Meaning this should have worked.  Or is the temp table my best bet?

      Thanks in advance.

        • 1. Re: "fudging" a filtered value list
          philmodjunk

          Adding the date field will allow you to filter your list of names by date, but you'd need to update your relationship to include this new date field in the relationship.

          You might be able to add a "next interview" date field to your applicants table and draw your values directly from this table (if you set up a relationship using that date field) instead of trying to manage a temp table for this.

          • 2. Re: "fudging" a filtered value list
            Nerico_1

            It's still not working.  Let me explain the whole setup to see if you can tell what I'm doing wrong.

            There is a 3rd table called DATES which has all the information related to the interview dates.  The main layout is related to the DATES table.

            The DATES table is related to the INTERVIEWS table via the inteview date field.

            The DATES table is related to the APPLICANTS table via the interview date field.

            The APPLICANTS table is related to the INTERVIEWS table via the applicant ID.

            On the DATES layout I have several tabs.  One tab has a portal showing records from the APPLICANTS table and another has records from the INTERVIEWS table.  Both portals correctly show the records only for that date.  In order to assign an applicant to a particular interview, I use a value list to add the applicant ID to the Interviews table.  The value list was originally created to use the records on the APPLICANTS table, using the ID field as primary and the full name as secondary.  However, the names on the value list did not get filtered by the DATES record like the portal records did, so eventually the value list became too long and it was harder to find valid applicants to assign.  So I changed the value list to use a temp table where I manually add just the ID for the current applicant group and a second field which calculates their full name.  This new temp table was only related to the APPLICANTS table in order to calculate the name field but not to any other table since it was only needed for inputing IDs into the INTERVIEWS table.

            After your reply, I added a dates field to the temp table and related to the DATES table but when I use the dropdown value list to assign the applicants to the interviews, I'm still seeing the full list and not just the applicants for that date.  

            Am I doing something wrong or is this just the way value lists work?

            • 3. Re: "fudging" a filtered value list
              philmodjunk

              I can't tell from your post exactly what you have done, only in general. Some detail isn't right, but can't see that from here.

              "Dates table is related to applicants via the interview date field." That seems to describe:

              Dates::InterviewDate = Applicants::InterviewDate

              This would support a portal listing your applicants by Interview Date like you describe on your tab control.

              This exact same relationship can be used to provide your value list of applicants so you can assign applicant ID's to your Interviews table. No temp table would be needed.

              • 4. Re: "fudging" a filtered value list
                Nerico_1

                "Dates::InterviewDate = Applicants::InterviewDate" is correct

                The problem in that when I use that relationship, all 136 applicants (and growing) we've had so far pop up, not just the 14 to 16 for the particular date.  So in the temp table I delete all existing records and just add the new ones while I'm setting up the upcoming date. 

                BTW. applicants don't get assigned to records in the DATES table, but to records in the INTERVIEWS table. Could the issue be that right now the only relationship there is between them is Interviews::ApplicantID = Applicants::ApplicantID".  Do I need to add a Interviews::InterviewDate = Applicants::InterviewDate relationship as well?

                • 5. Re: "fudging" a filtered value list
                  philmodjunk

                  If you use that relationship correctly, it should only list those applicants with that interview date.

                  However, it seems like the snake is eating his own tail here. You are using your drop down list to assign applicants to interviews and I wouldn't think that an InterviewDate field in applicants would have the correct date and thus make this relationship valid until after you've assigned them to an interview--but maybe that's just my ignorance of what your are doing here.

                  If there is a date in Applicants::InterViewDate that matches a record in Dates, then you should be able to set up this value list:

                  Specify the ID number from Applicants for column 1. Specify the FullName field from Applicants for column 2.

                  Select Show only related values starting from Dates at the bottom of this dialog.

                  The "table names" you select in "Starting from" and the drop downs for each column must refer exactly to the names of the boxes on your relationship graph where you defined this relationship.

                  • 6. Re: "fudging" a filtered value list
                    Nerico_1

                    Thanks Phil! 

                    Yes there is "a date in Applicants::InterViewDate that matches a record in Dates" 

                    So I will give your directions a try and report back.

                    I know it may seem confusing to have a DATES table and an INTERVIEWS table.  The records in the interviews table are the particular time slots that each faculty member has committed to interview an applicant.  Each faculty may interview anywhere from 1 to 4 applicants for one half hour each on any of the dates.  The DATES table has a record for each day on which we hold interviews.  These are usually every Monday and Friday from August to February but not all potential dates are used.  Each applicant gets invited for a particular date but we don't assign the interview slots until a day or two before the actual date.  But there are other tasks that we use the database for which require us to assign applicants to a particular date and those tasks could be performed long before the interview slots are assigned.  That's why my main layout is based on the DATES table.  The first tab contains a kind of date-at-a-glance dashboard used to plan out the day.

                    I now realize I must have furthered confused matters when I said that "applicants don't get assigned to records in the DATES table, but to records in the INTERVIEWS table."  I misspoke.  They do get "assigned" to a particular date via a batch import of a spreadsheet from another program, since that spreadsheet contains the date field.  But that is different from the individual assignment of each interview slot which has to be done "manually" via the value list dropdown.