[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

FM - How to Create a Dates Table

Posted on 2011-10-27
19
Medium Priority
?
670 Views
Last Modified: 2012-05-12
Beginning to realize that I am having to create too many table relationships between dates, so what is the best practice for creating a "Dates Table"?  Would you create a table for years and then a subtable for months and a subtable for days in order to normalize?
0
Comment
Question by:rvfowler2
  • 9
  • 6
  • 4
19 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 37039475
Need some more info about what you are trying to do. I've created tables for "excluded" dates, to be referenced so that events are scheduled on known holidays or other days a business is closed, but I'm not sure what your trying to achieve with a Dates table. Perhaps your existing relationships could be handled in a more efficient way.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37039726
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37039748
The fields that end with "formatted" are formatted for a weekly email.  Boss doesn't want a report or pdf.


--DatesTable3.JPG
0
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!

 
LVL 12

Expert Comment

by:North2Alaska
ID: 37040336
I use a table called Calendar that breaks down each day with the many values needed for dates.  As you will see in the list below that there are many parts to a date and can be used in many ways.  The FY_XXXX  columns are for Fiscal Year calculations.    For example, if I want to find the dates (or days of the week) for a given week of the year, I can look at WEEK_KEY (the data is something like 201112 for the 12 week of 2011).  Add in the WEEK_DAY_FLAG (Y or N) and now I can see what the monday thru friday dates are.  There are many ways to use a table like this and there are many ways to populate it.  My current table goes from 1989 to 2090 and would be a trivial thing to add more years.

This is a list of columns in my table:
DATE_KEY
DATE_DATE                                                                  
DAY_NAME                                                                  
DAY_IN_WEEK_NBR                                                                  
DAY_IN_MONTH_NBR                                                                  
DAY_IN_QUARTER_NBR                                                                  
DAY_IN_YEAR_NBR                                                                  
DAY_BEGIN_DATE                                                                  
DAY_END_DATE                                                                  
WEEK_DAY_FLAG                                                                  
WEEK_KEY                                                                  
WEEK_IN_MONTH_NBR                                                                  
WEEK_IN_QUARTER_NBR                                                                  
WEEK_IN_YEAR_NBR                                                                  
WEEK_BEGIN_DATE                                                                  
WEEK_END_DATE                                                                  
MONTH_KEY                                                                  
MONTH_NAME                                                                  
MONTH_IN_QUARTER_NBR                                                                  
MONTH_IN_YEAR_NBR                                                                  
MONTH_DURATION                                                                  
MONTH_BEGIN_DATE                                                                  
MONTH_END_DATE                                                                  
QUARTER_KEY                                                                  
QUARTER_IN_YEAR_NBR                                                                  
QUARTER_DURATION                                                                  
QUARTER_BEGIN_DATE                                                                  
QUARTER_END_DATE                                                                  
YEAR_KEY                                                                  
YEAR_DURATION                                                                  
YEAR_BEGIN_DATE                                                                  
YEAR_END_DATE                                                                  
FY_WEEK_KEY                                                                  
FY_WEEK_IN_MONTH_NBR                                                                  
FY_WEEK_IN_QUARTER_NBR                                                                  
FY_WEEK_IN_YEAR_NBR                                                                  
FY_WEEK_DURATION                                                                  
FY_WEEK_BEGIN_DATE                                                                  
FY_WEEK_END_DATE                                                                  
FY_MONTH_KEY                                                                  
FY_MONTH_IN_QUARTER_NBR                                                                  
FY_MONTH_IN_YEAR_NBR                                                                  
FY_MONTH_DURATION                                                                  
FY_MONTH_BEGIN_DATE                                                                  
FY_MONTH_END_DATE                                                                  
FY_QUARTER_KEY                                                                  
FY_QUARTER_IN_YEAR_NBR                                                                  
FY_QUARTER_DURATION                                                                  
FY_QUARTER_BEGIN_DATE                                                                  
FY_QUARTER_END_DATE                                                                  
FY_YEAR_KEY                                                                  
FY_YEAR_DURATION                                                                  
FY_YEAR_BEGIN_DATE                                                                  
FY_YEAR_END_DATE      
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 37040630
Nicely done N2A.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37040735
By columns, I'm assuming you also mean they are fields.  However, using your system, did you create a record for every date (day) from 1989 to 2090?
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37041460
Yes, column and field are the same in FM.  I built the table with a SQL Script.  It took some deep thought... :-D
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37041479
But my question was do you have a separate record for every date from 1989 to 2090?  If so, why didn't you use subtables for months and then days?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37041546
Oh, and since the data field is never the same, do you use that as your primary key or do you just create the normal PK numbered 1 on up?
0
 
LVL 12

Accepted Solution

by:
North2Alaska earned 2000 total points
ID: 37041754
Yes, a record for every date.  The reason we chose not to normalize the data was for performance reasons.  To join multiple tables was a hit.  In the end you had to always join the tables and at one point we just had a view (on the SQL database)  that did the join.  But we determined that a single table was easier to move around, easier to do the calculations and faster.  So, as single table.

There is a Primary key; DATE_KEY that is a string like 20110525, but we also have a couple of logical keys like DATE_DATE which is an actual date or DAY_BEGIN_DATE and DAY_END_DATE.

What I find I use the most is joining the transactional data (whatever that might mean to you, sale date, invoice date, activity date or whatever...) where the transactional date is between begin and end.  This way the date can have a time component and the join will still work.  This means the begin date is like 12/31/2011 12:00:00 and end date is 12/31/2011 23:59:59.

Doing a query the other direction is nice to.  Say I want to find all the transactions that occurred in the 3rd Quarter.  It is simple by doing a find on the QUARTER_KEY or FY_QUARTER_KEY.  The Key looks like 20112 or FY20114.  Then using the join all the transactions in that date range are return.  It is really fast too.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37041799
Looks like exactly what I want though I'll have to splash around a bit to figure it out.  Do you name your QUARTER_KEY 1, 2, 3, or 4 and repeat that for every record it applies to, or do you give it a unique number based on year and quarter?
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 37041830
Thanks, don't know SQL, but could do it in Excel with calcs and then drag down about 3,000 rows.
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37041861
Quarter key includes the year, so it might look like 20104 or 19991.

Do you have a need for a fiscal year as well?  I could send you output from our table, but we have a fiscal year that starts in Oct.  That may not work for you.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 37041900
Should be easy enough to run a few replace calculations to convert the Quarters to calendar years. Also, the Quarter fields can simply be stored calculations that you can modify as required.
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37041955
Avoid calculations if you can.  Remember we want to have the fastest query as we can.  Calcs require processing, etc...

Here's my Calendar table less the FY_XXX columns and includes the data.

 calendar.fp7
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 37042068
Calcs only require processing if they are unstored. My understanding is that a stored/indexed calc would be negligibly slower than a static field. Something as simple as

Month_Name = MonthName( Date )

Shouldn't be measurably slower than storing the value directly. Maybe I'm wrong about this but I've never seen anything FM test to indicate otherwise. Similarly for virtually all the other fields....
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37044593
Yes, I believe stored fields only calc under certain situations.  I guess you use them if you have fairly static data.  Also, we have 3 different fiscal years for taxes based on the county.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37047545
N2A, the file is completely awesome (can you tell I'm from So Calif originally?).  However,  I must be having a dense moment because I can't figure out how to use it.  I want to total all Lead records and chart them for each week.  However, all I know how to do is make a copy of my Leads TO and join it to itself by the calc field that has Year ( DateCreation ) & WeekofYear (DateCreation).  But how would you doing it using your table?
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37047782
Can you provide a sample database?  and a report description?  I'll put something together.

As the points are moderately important, a new question might be in order...  :-)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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