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.
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.
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!
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 )))
That can be done, but just using GetValue seems the simpler approach.
Substitute ( List ( RelatedTable::Field ) ; ¶ ; "; " )
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.
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.
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.
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.
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.
1 of 1 people found this helpful
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/
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:
The List function will ignore all the null values and simply give you a list of every record marked Y in Employees.
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.
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) ))