Random Selection

[Jed69]

I have two tables one of customers and one of staff.

I wish to create a script (run by a button) that assigns a member of staff to a customer. The criteria for this would be to assign a member of staff who have the Type "User" (Type is a field in the staff table) , the member of staff should have the lowest number of customers (No_Customers is a field in the Staff table and shows how many customers are assigned to that member of staff) and then if there were a number of staff who had this criteria (ie they were "User" types and had the same number of customers assigned to them which was the lowest number amongst all of the staff) it would randomly pick a member of staff.

This feels very complicated and I can feel my brain dribbling out of my ear just thinking about it. Can anyone point me in the right direction.

[comment]

Start by finding staff members of type "User". Sort them by number of customers, ascending. Go to the first record and pick the staff member's ID into a variable.

If there are several staff members with the same lowest number of customers, this will pick the first one in the order of record creation. I don't see why you need to bother with random picking, since it will eventually balance itself - but if you want, you can create an unstored calculation field = Random, and include it in the sort order after No_Customers.

[CobaltSky]

Here you go:

#Randomly Select User with Lowest Customer Count:

Go to Layout [ “Staff” ]

#Find Users and sort by No_Customers

Perform Find [ Restore; Specified Find Requests: Find Records; Criteria: Staff::Type: “User” ]

Sort Records [ Restore; No dialog; Specified Sort Order: Staff::No_Customers; ascending ]

#Grab lowest customer count

Go to Record/Request/Page [ First ]

Set Variable [ $NoOfCustomers; Value:Staff::No_Customers ]

#Find all users with lowest customer count

Enter Find Mode [ Restore; Specified Find Requests: Find Records; Criteria: Staff::Type: “User” ]

Set Field [ Staff::No_Customers; $NoOfCustomers ]

Perform Find [ ]

If [ Get(FoundCount) > 1 ]

[color:#ffffff]....#Randomly select from among users with lowest customer count

[color:#ffffff]....Go to Record/Request/Page [ No dialog; 1 + Int(Random * Get(FoundCount)) ]

[color:#ffffff]....Set Variable [ $ChosenOne; Value:Staff::StaffID ]

[color:#ffffff]....Enter Find Mode [ ]

[color:#ffffff]....Set Field [ Staff::StaffID; $ChosenOne ]

[color:#ffffff]....Perform Find [ ]

End If

##

:wink2:

[Jed69]

Thank you both very much. I will try these later tonight.

[CobaltSky]

I don't see why you need to bother with random picking, since it will eventually balance itself...

Without random picking, the oldest record (first entered) among users with the lowest customer count will always be allocated the next customer.

Maybe users don't want to be "penalized" on the basis of seniority. :smirk:

[comment]

Penalized? Maybe users should be PROMOTED to higher responsibility (and who knows, maybe more benefits) on seniority basis?

[Jed69]

CobaltSky I have tried your code and it works like a dream and does exactly what I wanted. Thank you so much. I understand it all except for calculation in the "Go to record" line which does the random calculation "1 + Int(Random * Get(FoundCount)). If you have the time I would be grateful if you could explain it, but please do not worry if you have not got the time, I am just pleased it works. :

[CobaltSky]

...except for calculation in the "Go to record" line which does the random calculation "1 + Int(Random * Get(FoundCount)). If you have the time I would be grateful if you could explain it...

Hi Jed,

The Random function in FileMaker returns a number between zero and one - such as 0.12345678987654321. But it's randomly generated (within the limits of randomness supported by the technology - but that's a different discussion...).

When you multiply that by the result of Get(FoundCount), you get a decimal fraction somewhere between zero and the number of records in the found set.

The Int( ) function truncates a decimal fraction, returning only the integer portion of it. So when you wrap Int( ) around Random * Get(FoundCount), you get a number between zero and the number that's one less than the number of records in the found set.

When you add 1 to the Int( ) result, you get a number corresponding at random to one of the records in the found set and, if you supply that number as the input to the Go to Record/Request/Page command, you're instructing FileMaker to navigate to the record that corresponds to the number resulting from the calculation expression.

So, when it's all put together, Go to Record/Request/Page [ No dialog; 1 + Int(Random * Get(FoundCount)) ] is an efficient way of randomly choosing one of the found records.

HTH.

[Jed69]

That's brilliant thank you for taking the time to explain it.