On 1/19/2011 11:58 AM, Bill Steele wrote:
I'm with Harry, in that I don't think it is at all overkill to add a
lookup table with month names and numbers...or use a case() statement,
as Harry also pointed to. You could even create a custom function
called something like MonthNumber() that uses a case statement to return
the correct month number from the month name. All that said, I'm not
clear on what you'll do with the month number as far as getting a list
of user types.

To get a list of user types in "February", you could create a
relationship based on a global field or calc field, set the global/calc
to "February" and then have it pull up the related records from which
you can get those user types.

Another option is this custom function that can sort of emulate a SQL
query in that it allows you to specify filter info right in the function
call. The function is called CustomList():

http://www.briandunning.com/cf/868

Your function call would then look something like this:

CustomList ( 1 ; Count(Users::UserID);
"Case ( getnthrecord(Users::MonthUsed, [n] ) = \"February\" ;
getnthrecord(Users:Type, [n] ))"
)

The above function has three parameters: starting record number, ending
record number, and then your statement. And the statement essentially
says that if a record's MonthUsed is February, then add that records
user type to the results list.

The result of something (more or less) like the above would be a list of
user types for all users during February. You could then use something
like the UniqueValues() function <http://www.briandunning.com/cf/596> to
whittle the list down to just the unique user types.

I realize the above may be a lot of work on your part, but it is
sometimes worthwhile for the developer to do a lot of background work so
that the users have a carefree existence. The most difficult part of
setting up these custom functions is tweaking the case() statement test
in your function call.

Even if you decide not to go in this direction, I hope it opens your
mind a little as to what FileMaker can do with a little programming and
creativity.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker Certified Developer
Associate Member, FileMaker Business Alliance