3 Replies Latest reply on Mar 1, 2014 10:50 AM by dwshore

    How to cycle through assignments



      How to cycle through assignments


           I'm putting together a system meant to assign "events" to people, based on what they are allowed to cover. The simplest piece of this, is for every week of the year, there is one "supervisor" assigned. There are a total of 5 supervisors that cycle through the assignments. Right now I have the following tables (based on the relevance to this part of the larger project);


           Each record represents an employee, with fields for each assignment they are eligible for; Supervisor, GB, PSI, etc. (For the purposes of this question, I want to focus on the Supervisors and will deal with the other assignments later) and their unique ID Number.

           Work Week:

           Each record represents 1 week in the year, with fields for Week, Year, WeekYear (because this will be used for multiple years), and Supervisor


           I have an idea of how to script a simple loop series to insert different text into each Work Week record with a combination of variables. The script starts by doing a search for all the employees that have a 1/Yes for Supervisor. (Because this list may change on occasion). Ideally I want to take the results of this search, put them in a list somewhere that I can extract 1 at a time from. If I can script the entry of a new name to a repetition of a field somewhere, I can work out the rest from there. But right now I don't know how to get my 5 ID Numbers into a repeating field without exporting to another file and reworking things from there. 

           This is just the first stage of a much larger, more complicated assignment system I'm putting together that will take other variables into account, so if there's a way to do this without exporting, that would be ideal. 



        • 1. Re: How to cycle through assignments

               I wouldn't use a repeating field for this. A variable with repetitions or a return separated list of ID's in a variable would seem to be all that you need to have your list of ID's. If you are using fileMaker 13, there's a new summary field that can easily produce this return separated list of ID's from a found set of the same.

               But I can't parse all the details of exactly what you are trying to accomplish with your script and how often you might run this script (since personnel changes can happen at any time...)

          • 2. Re: How to cycle through assignments

                 Thanks for this idea! This script would be run basically once a year, or at most every 3 months. I've luckily built a field already that has return separated the list of employees per duty. So now how do I get the entire list into a variable, and then tell the variable which line to choose from? So far it only enters the first line. (That's why I was thinking of working with repetitions in a field, rather than just lines) I've included a screen shot of what I have so far. This is before integrating your suggestion, which I'll need to look into today.



            • 3. Re: How to cycle through assignments

                   Actually, I just figured out a slightly more elegant way of doing this, rather than using variables, lists, etc. Here's a screenshot of it. 

                   Basically, I told it to first filter the records on my list of employees by who was assigned as a supervisor. Then to GetNthRecord (EmployeeNumber ; $Stage)  based on what stage of the cycle it's on, and repeat. There's also a line in there to help it recognize that the first 'N' records aren't necessarily the records I'm looking for, so it should skip to the next one that is. I've run it a couple times now, and it works beautifully. 

                   Thanks Phil for the idea to look at something other than repeating fields for the answer!