?
Solved

FM - Using a Calendar Table

Posted on 2011-10-31
9
Medium Priority
?
655 Views
Last Modified: 2012-05-12
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 a Calendar Table (with a date for each record from aboiut 1990 to 2089?  

See attachment for how I did it -- relating the DateYrWeek field from the Portal TO (LeadsbyWeek) to the main table (LEADS), but how do you do the relationships using a Calendar table?  The broader question is how do you use the Calendar table?  Provide an example or two (an explanation vs. a file is fine to start).
--LeadsbyWeek.JPG
0
Comment
Question by:rvfowler2
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37057385
If you can provide a sample file with the tables and columns you would like to use, it would be very helpful.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37072114
Sorry it took so long, been busy.  Tried adding your date table in but it made the file too large.
LeadswChart.fp7
0
 
LVL 12

Accepted Solution

by:
North2Alaska earned 2000 total points
ID: 37076457
Looking at your example, I have made some changes that may work for you.  

1)  Changed the summary calculation to a count of the related records using the relationship.
2)  Added another TO to join to all the records.
3)  Changed the graph to use the new TO as the results.

While this worked, there were some things I didn't like about the graph; it shows a data point for each record.  This is a problem because each record will have the same value as a result of the calculation.  I did an extensive search for a way to identify only one record for each year, but none met the criteria I needed.  So, in my estimation it as a FAIL.

Next, I created a new table, LeadsGraph, with two columns, Year and Count.  As I played with this I liked it more and more.  First it gave the results I was looking for and second, it allowed a lot of flexibility.  Because it was a new table and you have to enter the year, this also meant that if I wanted to remove a year from the graph I could.  It also allowed for other calculations to be added in, like sales for that same time, giving you a comparison chart between number of leads and amount of sales (for example).

Anyway, here is the sample file. LeadswChart.fp7
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:rvfowler2
ID: 37077923
Good idea.  I could have a table for years, one for quarters, one for months, one for weeks, and your table would represent days.  Too bad you can't create a TO relationship off of your main Calendar table so that you didn't have to create separate tables, just separate TOs.
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37078390
Let me work on another idea for a bit.  I may get back to you tomorrow...
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 37125030
My apologies.  Life stepped in this week.  :-)

I have made several attempts but have been unsuccessful in making the joins work.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37140305
Same for me.  We upgraded to Win7 and are having a terrible time with our DOS database working in the Virtual XP mode environment though initial testing was fine.  Get to this by Friday.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 37153527
Thanks, this works, and sorry for taking so long.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

840 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