I am attempting to create a Database for people to pick the week of vacation they want by the week number. I would like them to be able to see the actually dates associated with that Week number so they understand the dates they are requesting. I am stumped as to how to do this.

I am not sure what you mean by "the week number". There are many ways to number weeks (Filemaker alone provides 8 different ones). I am guessing you want to have a table of Weeks, where each record is a week, with fields for WeekStart, WeekEnd and a unique WeekNumber. Such table should be easy to generate in advance for the foreseeable future

Then let users pick a week from a card window or from a portal, and record the WeekNumber of the selected week in the current record of ... (you haven't told us this part).

More details could be useful here.

 

Thank you for the reply…let try to clarify

Week number

I am referring to the first week of the year (American system …the week starts on Sunday) is week 1, the second week of the year is week 2, etc.

the table will include start date and end date of each week.

a separate table will be where the employee picks the four weeks they want vacation for that year. It will pull the start date and end date from Week Number table. The weeks picked will be stored with that employee’s record. I want to be able to prevent them from selecting the same week the following year, thus storing previous vacation picks is important.

I will have the administrator of vacations have the ability to approve or deny the employee’s vacation requests.

4 hours ago, KGAS said:

I am referring to the first week of the year (American system …the week starts on Sunday) is week 1, the second week of the year is week 2, etc.

I am afraid that's still ambiguous: what exactly is  "the first week of the year"? It could be the week that contains January 1 of that year (even if it is a Saturday) or it could be the week that contains four or more days of that year. At least those are the two most common methods, which are also supported by Filemaker's WeekOfYear() and WeekOfYearFiscal() functions respectively.

Anyway, it looks like you want a table of 54 or 53 (depending on the definition above) permanent records, with a global Year field, from which they can choose their 4 weeks (after selecting the year). Such table is not very difficult to construct. The picking and storing mechanism will be more challenging, IMHO. Do they need to pick a contiguous block of 4 weeks? Or can they make 4 independent selections?

 

I am defining for our group the first week is the one that contains January 1st.

the staff must pick 4 individual weeks

the picking and storing will be difficult to implement 

41 minutes ago, KGAS said:

the first week is the one that contains January 1st

Okay, that's clear then. Just be aware that in this method the 1st week of a year can also be represented as the 53rd (or, in some cases, the 54th) week of the previous year.

43 minutes ago, KGAS said:

the picking and storing will be difficult to implement 

If you wanted to keep this very simple, you could ask your users to pick a date in the selected week, using the native drop-down calendar tool. Then store the year and the week number of the selected date. This would be very easy to implement, at least the picking part. I am not sure what to suggest WRT the storing part because I don't fully understand how you intend to use the stored data.

 

The purpose of storing each staffs vacation pick based on the year is so that I can prevent them from picking the same vacation week two years in a row. So in the second year of implementing the database If staff member 1 attempts to pick the same week they picked in the first year, the database will automatically deny it. They will be able to pick the same week in the Third year of implementing the database

 

Although I agree it would be simpler to program if they pick a Date in the selected week, after having work with this group I know it will just confuse them. It will be much less confusing to show them the start and end date of the week they are picking. Also since the actual start date and end Date of each week will vary from year to year, I want them to be able to see the exact dates they picked from the prior year

14 minutes ago, KGAS said:

It will be much less confusing to show them the start and end date of the week they are picking.

The attached demo shows a relatively simple way you could do this. Although personally I find it much more confusing when dates are shown out of the month context.

 

20 minutes ago, KGAS said:

If staff member 1 attempts to pick the same week they picked in the first year, the database will automatically deny it

You may want to define a relationship to the previous year, same week number. Then if a related record exists, stop the process. In any case, this is scripted so you have plenty of other options.

 

SelectWeekFromPortal.fmp12

Thank you so much, this really helped. To prevent someone from picking a week that has already been selected, I will just have the "select" script check to see if the week number already exists. 

again thank you