1 2 Previous Next 16 Replies Latest reply on Apr 24, 2017 9:05 AM by TVBOTC

    Choose sequentially from list of names


      We have people who coordinate orders that come in.  Right now, their names are hard coded into a calculated field as such:


           Choose(Mod(#Rec_ID;4); "Allen, Glenda"; "Pigott, Kerri"; "Bouquette, Marvin"; "Enzor, Kyanna")


      This works fine, but when someone goes on vacation or leaves or is a new hire, I have to go in and change the field.  I am trying to get it to work using our employee table, which has a field for "CoordinatorWeb" set to "Y" for active coordinators. That way, the manager can check or uncheck employees, or add/remove them as needed. I have a relationship with that field matched to an auto-enter field set to "Y", and a list of them through that relationship shows the correct names.  Next I tried rewriting the calculated field as below, but it does not work.


      Choose(Mod(#Rec_ID; Count ( Employee_Coord::Full_Name_LF )); List ( Employee_Coord::Full_Name_LF ))


      Any assistance would be greatly appreciated!


      Trish V.

        • 1. Re: Choose sequentially from list of names

          The values returned by list aren't in the right format for choose. Try GetValue instead, but note that the indexing starts at 1 instead of zero.

          • 2. Re: Choose sequentially from list of names

            You might set them up with FileMaker usernames as FirstName LastName and then use get(  AccountName ).


            I have been able to adapt Darren Burgess's FileMaker accounts module to use an existing staff table and records so that it will create new records or create a FileMaker user account for staff that have to log in.  If you comfortable with scripting and have Advanced so you can use the debugger this is fairly straight forward.  Follow his instructions and make sure everything works correctly before adding this feature.


            Its a lot more work then just creating 4 accounts but may be helpful.

            • 3. Re: Choose sequentially from list of names

              We use Active Directory for the account names, so I'm not sure if that would work.  The employee table is something we create and maintain for drop-down lists and look-ups. I will look at Darren's script, though, to see if it would work.  Thanks for the guidance!

              • 4. Re: Choose sequentially from list of names

                Funny you should mention the format ... I was trying to figure out a way to convert the list to a colon-delimited field, but couldn't figure that one out. Could you give me an example of how to do that?


                ** I just looked up GetValue and see that the usage format is GetValue(listOfValues;valueNumber).  Would the correct calculation then change in order to:


                GetValue(List ( Order_EmployeesCoord::Full_Name_LF ; Mod(#Rec_ID; Count ( Order_EmployeesCoord::Full_Name_LF )))

                • 5. Re: Choose sequentially from list of names

                  That can be done, but just using GetValue seems the simpler approach.


                  Substitute ( List ( RelatedTable::Field ) ; ¶ ; "; " )

                  • 6. Re: Choose sequentially from list of names

                    Thanks, in looking back over your post, I kind of re-worded my question.  I think I was editing it at the same time you responded.  Wondering about the usage format on GetValue, so that it would go through the rotation.

                    • 7. Re: Choose sequentially from list of names

                      since choose works 0, 1 , 2 ,3 and GetValue uses 1, 2, 3, 4, you'd have to modify the use of Mod to get a slightly different sequence of index values.


                      Mod(#Rec_ID; Count ( Employee_Coord::Full_Name_LF )) + 1


                      Perhaps, though I don't know how #Rec_ID is producing the correct value for this or the original calculation.

                      • 8. Re: Choose sequentially from list of names

                        TBH, Phil, I do not understand that part myself!  Someone wrote that for me a long time ago and it works, but I never quite understood it completely. I have spent the better part of the morning trying to understand how the record id (a.e. serial number) plays into it.

                        • 9. Re: Choose sequentially from list of names

                          Frankly, using a record's serial number doesn't look like a good design, but there isn't really enough detail to know for sure.

                          • 10. Re: Choose sequentially from list of names

                            If there's a better way, I'm all for that ... especially if I can understand what it is doing!  All this is is a field that automatically assigns a coordinator to each order that comes in from the web form.  It goes through the rotation and then starts over when it reaches the total count number. The calculation written for us has worked, but whenever I can ever get away from hard-coding something, especially names, I do it.

                            • 11. Re: Choose sequentially from list of names

                              To those who wish to find Darren Burgess's text on writing modules, some links are broken. You will find it at How To Write a FileMaker Module - MightyData


                              Regarding the account module, it's at http://www.modularfilemaker.org/module/accounts-modular-user-account-management/

                              1 of 1 people found this helpful
                              • 12. Re: Choose sequentially from list of names

                                Have you checked to see if the calculation is even retrieving the list? Put:


                                List ( Employee_Coord::Full_Name_LF )


                                In Data Viewer to see if the list is even pulled through the relationship.


                                Another method I've used to pull in a list like that based on a checkbox is to write a field in the target table named something like Full_Name_LF_Coord, set up as:


                                If ( CoordinatorWeb = "Y" ; Full_Name_LF ; "" )


                                Then just using the normal relationship form the Orders table, use:


                                List (Employee::Full_Name_LF_Coord)


                                The List function will ignore all the null values and simply give you a list of every record marked Y in Employees.

                                • 13. Re: Choose sequentially from list of names

                                  You have the right idea "hard coding" = brittle design--changes easily break it and require developer intervention to fix.


                                  But after thinking about it, I can see how the serial number value works here.


                                  I can provide this info: The Mod function is sometimes called the "remainder function". Do you remember, as a grade school student, toiling over long division problems that produced a two part result, the quotient and the remainder?


                                  Mod returns that same remainder. 20 / 3 =  a quotient of 6 a remainder of 2 (20 - 18 = 2 )


                                  and 20 Mod 3 produces that same remainder of 2. This is useful in this type of looping algorithm because the remainder will always be a value form 0 to 1 less than the divisor (3) in the above example. So if you have 4 people in your list, the index value returned by mod will always be 0, 1 , 2 ,3 --exactly what you need for Choose, but off by one for what you'd need for GetValue.

                                  • 14. Re: Choose sequentially from list of names

                                    This works, BUT adds a null consultant name after the 4th one.


                                    GetValue( List ( Order_EmployeesCoord::Full_Name_LF) ; Mod(#Rec_ID; Count ( Employee_Coord::Full_Name_LF ) +1) ))


                                    SO CLOSE!

                                    1 2 Previous Next