Solved

Month Filtering in Filemaker

Posted on 2011-09-16
5
768 Views
Last Modified: 2012-05-12
I have a HR database built for our school.  Each month our teachers are billed for a certain number of utilities since a number of our teachers live on campus.  I have set up separate tables for Electricity, Phone, Postage, Vehicle, Facility, and Other Charges.  I also have a table for Staff and another for Salary and Other benefits.  The person who set this up also put a School Year filter in so a drop down changes all information from year to year.  It works really slick.  

Now for my question.  I want to create a layout that pulls in teachers monthly utility charges each month which allows them to see only that months bills.  Each of the utility tables has the School Year field and a Month field.  The school year works great as I said, but I can't figure out how to filter so a drop down will display the utility sheet for staff to bring up only the records entered based on the Month.  So in the Electricity table I have amounts for Aug and September, but the portal it is displaying records from the table so when I change the month it also changes the month back in the layout where the record was created.  

this is driving me crazy.  Please advise.
0
Comment
Question by:SFSDIT
[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 25

Expert Comment

by:Will Loving
ID: 36549380
You'll need to check the relationship graph between the Staff and each of the charge tables to see what fields are being used. I'm guessing that what you will find are two fields, the Staff members's ID and the School Year. The school year field is probably a "global" field - meaning it's the same on all Staff records - so you can change it from year to year.

Assuming that that is the case - and if I'm wrong it would be helpful for you to upload either an example file or some screen shots of the relationship graph and one of the Staff-Utility relationships - in order to additionally filter by month, you will need to add another global field to the Staff table called, perhaps, "Month_Select". If you modify the relationship between a Staff Person and one of the Utilities to include "Month_Select" = "Month" in the utility table, that should cause the portal to filter to show only those entries for that Staff person, School Year and month.

Of course, you will need to put the Month_Select global field somewhere where you can access it. It should be formatted as a "popup menu" with a list of months as the value list.

If you don't understand what I've written or my understanding of what you have is incorrect, please post back and include an example file or screen shots to help clarify.
0
 

Author Comment

by:SFSDIT
ID: 36558039
I've attached an image of my relationship graph for your viewing.  I believe you are correct in the the School year is a global field although I don't know for sure.  I'm assuming the way it acts.  How do I work the Month_Select relation?
relationshipgraph.jpg
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 36558083
Looking at this, and without seeing how these Table Occurrences are used in your filter, I would guess that your portal to each type of utility is being filtered by the "filter__SchoolYear" field. If you go into the "STAFF" table, you should be able to tell if that is a global field. I think the other global fields listed off to the right are something else or perhaps something that someone started but then didn't use.

You can also check my theory by going into Layout mode on one of the layouts that includes a portal to a utility table. See if, for instance, the relationship used by the portal for electricity is "staff_ELECTRICITY_SchoolYearApt"

In order to include the month in that as well, you would need to do the following:

Create another global field in the STAFF Table. You can call it "filter__Month" if you wish.
Mark the field as "Global" under the storage options.
Add the new global Month field on a layout with the portals and apply the Month value list as a Popup menu so you can select and change the month.
Add the new global field as an additional "Predicate" it to each of the relationships, going from the global field in STAFF to the Month field in the Utility table. However, I suggest you start with just one to test it with one relationships first to make sure you have it working right.
.

I also strongly suggest that you make sure you have a backup copy of your file so if things get really messed up, you can start over again.
0
 

Author Comment

by:SFSDIT
ID: 36558110
It worked now so my layout is showing the month totals for the month I pull down from the Global month field.  Thanks a bunch.
0
 

Author Closing Comment

by:SFSDIT
ID: 36558111
Thanks so much for the support
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

690 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