Solved

List

Posted on 2011-09-25
5
248 Views
Last Modified: 2012-05-12
Hi
I need to get dates from a Holiday List Table so that the system will show which dates match the range of dates in the week of production.
I have a Production Table where the intened  Week of Production (WeekOfFiscalYear) is set by the planner.
eg Week 19 :Starts on Monday(WeekStartDate) 02/05/2011 and ends on Friday 06/05/2011
Monday Tuesday  Wednesday Thursday Friday are separate date  fields  which are based on the Week Start date   + one day for Tuesday ,+ 2 for Weds etc
I have another related  table HOLIDAYS which lists all holidays for the year.
If for example Tuesday 03/05/2011 is a Holiday I want the Tuesday field to be show "Holiday".
The problem is that I cannot seem to get the calculation to do this for a range of days.
Once the week of production has been set I want the system to go to the Holiday list , check if there are any Days/dates in the week of production that are equal to any of the week days within that week of production and then to alert.
Hope I have mader it clear.
Thanks in advance
0
Comment
Question by:Krisraychris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:North2Alaska
ID: 36596062
When the Week of Production is entered, calculate two columns Week Start Date and Week End Date.  Using these two columns, create a relationship to the Holidays table where Week Start date is less than the date of the holiday and Week End Date is Greater than the date of the holiday.  This should return a list of holidays between the start and end dates.  Now you have this list, you can create a third column that does the alert calculation, like IF(count ( holidays ) > 0 then "Hey there's a holiday in there somewhere." ; "" )  
0
 

Author Comment

by:Krisraychris
ID: 36600157
Thank you... the first part was spot on and the rellationship is giving me the date range and finding the public holiday within that Week Of Production.
I need to match the date of this  holiday with any of the 5 Production working Days Monday Tuesday etc.
If there is a match then I need to know which day of the week it matches with.
 I would like  some way of showing the planner  which day of the  Production Week day Mon to Fri is the holiday.As in the example above 03 May 2011 is a holiday and no production should be planned for that day.
I was thinking the simplest way is to create a separate fields for each day of the week and the holiday or Holidays which show up in any of the five fields.
But the most important question is how ot get the Holiday to show the  match with  the Production week day and show it as a date.
I would appreciate your ideas on this.
 
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 36600502
How about this.  Create a table with two columns, Production Week and Day Date.  Now join the production week from the main table to the new table, this would give you a portal with a list of the dates in the production week.  You could then join the new table to the holiday table.  This would give you two options; 1)   using the join between the new table and the Holidays table, create the indicator above or 2) remove the holiday dates from the portal all together, only displaying days that production can take place.

0
 

Author Comment

by:Krisraychris
ID: 36717727
If I do what you have suggested then I am not getting the date range I need as the Production Start Date and End are two fixed dates and it does not list the Monday to Friday Range.
I tried a rellationshipe between the Holiday table and Main Table.
List(Production date Monday;Production Date Tuesday;Production Datye Wednesday etc...to Friday)
and To Holiday Table :Holiday Date.I viewed the result in the main table in a portal and it shows the holiday date correctly but only one day and not where there are two holidays days in the week. I dont know if this is workable but where there is one day in the week I set condional Formating to Formula Is-Holiday Table:Date to highlight the Production Day Field.This worked but only when the day is a Monday.
I need a way to link the Main Table with the Production Week Table to the Holiday Table so that The portal in the Production Table will will show identify the Holiday/s in the week.
Hope you can give soem direction on this one.
Thanks
0
 
LVL 12

Accepted Solution

by:
North2Alaska earned 500 total points
ID: 36718345
Here is a little sample of what I was thinking.  Things to focus on are the three tables, their data and their relationships.  Also, look at the conditional formatting of the Production Dates portal.

There is lots to do still, like are weekend part of your production schedule, how to populate the Work Week table (I did it with a script, check it out) and what is the values that will be entered by the user.  But this may give you an idea of what I was thinking.
Holiday.fp7
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question