"fudging" a filtered value list
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.