When the HR manager login in with his account, he want to be gived a list window with people whose birthday is in this week.
So how can I get the solution to it ?
Here is my solution to your problem:
In Utility or Preference or Dashboard table (the startup table) create the following fields:
In Staff or Employee table you need:
In Relationship Graph:
In your Startup script, create a Subscript (Perform Script) Refresh Employee Birthday as follows:
Now Create a Portal on your Utility or Preference or Dashboard layout to display Birthday_EMPLOYEE
As you can see, you can even add an icon or a checkbox when an email has been sent. In my solution if you click on a portal row and the email has not been sent then the system creates a Happy Birthday email and then come back to set Email sent = 1 so the icon his displayed, so email won't be sent twice and all users can see it.
The number 5 on the upper left shows you how many birthdays for the current week displayed above the portal.
To do the count, just have a FoundCount Summary Field in your Employee table = Count of id_Employee_pk, duplicate the above portal, set it with only one row, put the Birthday_EMPLOYEE::FoundCount in it and finally reduce the size of the portal to surround the field by only one or two pixels.
Comment: In this solution, every user will see the birthday portal, so you might wan, in your Startup script, after the Refresh subscript has been executed, to check if user has credentials to see the birthday layout, I propose to either add a specific layout or put the portal in a slide or tab only accessible to approved users. In script, that would be:
If [ Get (AccountName) = "HR Manager Account Name" or Get (AccountName) = "HR Assistant Manager Account Name"
Go To Layout "Birthday this Week" // or Set Variable [$$HR; Value: 1] that will be used to hide portal and other layout objects
Yikes! That's a LOT of work for something so simple.
The way I'd approach this requirement, as I understood it from your posting, is to simply:
1. Determine the starting date and ending date in the week for a given date, and
2. Use those dates in a regular Find command.
I wrote a micro-service method in a few minutes that does 1. above (the "heavy lifting" part). Part 2. above is just using a FMP find command.
You pass the a date, like a birthday, and any number of days you consider for the start and end date (not necessarily M-F) to the micro-service and it will return the start date and end date for the range you specified (flexible coding!). So, from FileMaker, this is ONE LINE OF CODE to get those dates, and no relationships need be created.
Assume the person's birthday is today, 2018-07-05 and you want M-F for the days of the week (again, the micro-service method is FLEXIBLE so it doesn't "assume" M-F).
All you pass from FileMaker in an INSERT FROM URL command to get a RETURN-DELIMITED list with the two dates for your FIND command is:
The service returns in the variable you set for the INSERT FROM URL Target:
(Note, you would get these same two dates for any birthday date in that week)
Then you can use GetValue() to get the two dates. Then, just fill in the Find Command with the two dates for the range and you're done!
Simple. Minimal coding. Could use the same micro-service date logic with any HTTP-enabled application (not "just" FMP). Free. You control the code, etc.
For example, using POSTMAN:
If you do a lot of coding, at some point, I'm almost positive that at some point you'll appreciate having a "global function repository" that is independent of FileMaker. This method I created uses the extremely powerful Java 8 Data API. Currently, AFAIK, there are no powerful date library APIs via plug-ins for FileMaker.
For more information about micro-services, see my tutorials at:
Create Micro-Services Using Java and the Spark Java Framework
The Simplest Micro-Service! (Python and Flask)
"Yikes" maybe but my product is a finished product. You never have to do a find, it is always there. Also you are not really explaining what to find. In order for a find to work, you need not only the DOB field but more importantly the Birthday field. Once Birthday exist, yes you can do a find.
I don't see where the OP has replied here, so this message is mainly for you.
I really did like your solution so I didn't mean to imply it wasn't a great approach.
Below is what I did. I had a different take on the OP's brief stated requirement.
Assumption: Birthday Date = "DOB"
Given this data:
The stated goal, as I understand it is to display the list of birthdays for "this week" -- but we can enter any date in the picker at the top.
Since we have the employee's birthdays (DOB) in our table, all we need to do is do a Find on the birthdays that are on the same month and day for the current week's month and day. So if your birthday is 7/2/1990, then your birthday is every July 2 -- every year.
Step 1: Get the range of dates for this week, assuming M-F week.
The micro-service method I wrote does this in one line of code from FMP. It returns a RETURN-DELIMITED list with two dates for any week date you pass it (example below assumes the current week):
I had to update my micro-service method to handle FMP's non-standard dates (sigh) as input and return the same non-standard date format as output. The micro-service by default (passing "false" below) expects standard ISO formats. No big deal, just shouldn't be necessary to do all these non-standard date gymnastics, IMHO (use standard dates, FMI ..... please!!!).
The call above sends the date as UrlEncoded since FMP's non-standard dates have "/" characters which would confuse the micro-services' GET method. I also pass the days to consider for the week start and ending dates (sub-week ranges supported). Finally, I pass a "true" to tell the micro-service that I'm passing a non-standard FMP date format instead of a standard ISO date format.
Step 2: Do a Find using these dates.
This step took me a little time since I needed to do a forum posting myself. The problem was that the expected way I wanted to wildcard the date range wasn't correct. (Thanks again to user19752 for helping me get this search criteria correct). The script portion to extract the various pieces of the dates is full of "middle" and "position" functions and calculations to handle one or two days and one or two months correctly.
Step 3: Run the script on startup:
Running the script on start up automatically shows the list of birthdays for this week from the parameters I sent the micro-service in step 1 (I passed "1,2,3,4,5" so the micro-service returns dates for M-F).
From the limited data above, let's enter October 10th as another example:
(only one day falls into that range.)
All automated. Flexible.
The FMP script portion would have been simpler if FMP supported a basic string function where I could "split" on "/" into an array and parse the dates that way. A split function would be much less ugly than all the Position, Middle, and Left functions.
In any case, FWIW, just another way to go about this task.
Hi fmpdude, I like your solution too, just a different approach. When I replied "Yikes maybe", I wasn't bitter at all, you have made enough contribution on this community for me to know that it was merely a comment on the length of the implementation, which is work I agree but that's my way of working, I cannot really change it.
Have a great weekend.
It was a good exercise. I learned something extra about wild-carding FMP dates so it's all good.
Appreciate your reply.
What isn't clear from the OP is:
is it a list of birthdays in the current week I am in,
is it a list of birthdays for the next 7 days,
is it a list of birthdays for the remainder of the week.
And I am going to venture that the learning curve on setting up a micro service to solve a problem which can be easily solved using native techniques (when you might have learned something but not actually shared any code - as this almost certainly can be done with Scriptmaster to the same end_)
but it does hinge on a clearer understanding of the actual solution envisaged
ExecuteSQL, Virtual list and a card window would mean a script of say, 6 lines??
And could be run from a button bar to give ALL the scenarios above, with the addition of next week on demand too.
OnOpen script run this last, then you can be
>> gived a list window with people whose birthday is in this week
And of course being FileMaker there at least 5 other ways to do this
Right, exact requirements not clear from the OP's posting.
If all he means is a pop-up window, then ExecuteSQL can do that easily in a small script. It sounded to me like he wanted an FMP list layout display.
Lots of ways to do this. Including yours, now three possible solutions, pending more detailed OP requirements.
Retrieving data ...