Solved

button to clear a month filter

Posted on 2011-09-26
13
398 Views
Last Modified: 2012-05-12
I have created a DB which filters by month so teachers can see bills on for the months they choose through a pull down list.  This is working fine although now I've go to find a way to put another button or so other way to make it so they can, if they choose, see all months in the portal listing.  So when they choose Aug in the filter they get only Aug bills, but not Sept, Nov, and so forth.  I need someway for them to see Aug, Sept, Nov and all other bills entered in the portal.

Please advise.

Thanks
0
Comment
Question by:SFSDIT
  • 7
  • 6
13 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 36598654
OK, so now you need to go a step further to allow the option of selecting "All" rather than just one month. In order to do that you'll need to 1) change your "Month" value list to include an "All" option, 2) create an additional calculation field in each of the utility tables, and 3) change the relationship from Staff to each of the utility tables to use the calculation instead of the Month field.

1. Under File -> Manage -> Value Lists, change the Month value list to include the word "All". If you want to have a separator line appear between the word "All" and the rest of the Months, just put a hyphen "-" (not underscore) on a line by itself. For example

All
-
January
February
March

2. In the utility table, create a calculation field called "BillingMonth_All" (or whatever you want) and make the calculation equal to the value in the BillingMonth field plus the word "All" with a paragraph return separating them. This can look like either of the following:

BillingMonth & "¶All"
List( BillingMonth ; "All" )

Note that the List() function automatically separates all values with a carriage return. Also, make sure the "Storage" options for this new calculation field are set so the "Indexing" is set to "All", otherwise it will not work properly. The result of this calculation should show you whatever value is in the BillingMonth field on one line and the word "All" on  the next, e.g.  

September
All

3. Change the relationship between your Global Month selector field in Staff and the Month field in the utility table, to go from Staff to the new calculation field in the utility table instead.

The way this works is that the new BillingMonth_All calculation field contains both the Month and the word "All". When the user selects a specific "Month" in Staff, they see just that one month's related records, but if they select the word "All", then all months will be displayed because every month also has the word "All" as an alternate/additional value on which the relationship match can be made.
0
 

Author Comment

by:SFSDIT
ID: 36714015
Kind of get it but a couple of things not working.  Sorry for the repetition.

1.  I previously made a Global Table with a field Month_Select which does just what I want it to do. Teachers choose the month from as drop down list on a layout and it shows only that months bills.  It also works on my other 5 utility tables I related their Months to the Global, but may I'm missing a step from your instructions somewhere above.  

Doesn't seem to be pulling in the All amounts.  My Value list is named Charge Months and I believe I've changed the relationships as you mentioned.  Is the understand that everything will function under one pull down list?

Thanks,

Jeff
OtherChargesUtilityTable.jpg
calculation.jpg
relationship.jpg
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 36714142
The thing I can see that might cause a problem is that your BillinMonth_All field is a Text field with a calculated auto-enter. That works fine for any new entries but doesn't help with older ones. Change it to a Calculation field rather than a Text field and make sure the Storage Option is set to "All".
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:SFSDIT
ID: 36818928
I have one more problem
When I select "All" from the month filter, it now shows all data(All.jpg) but when I choose the special month (Aug.jpg. For example, Aug and look at the picture), it does not show anything.

All.jpg
Aug.jpg
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 36819005
Since I can't see the Layout mode, I can't tell which Month field is being displayed in your portal, but make sure it's the Standard "Month" (is it "OT_Month"?) field and not the BillingMonth_All field.

Second, check again to see that the BillingMonth_All is NOT a Text field (with a calculated auto-enter) but a Calculation field and is set in the lower left corner so that "Calculation result is Text". The default result type when you create a new calculation is Number and this can often mess up relationships such as yours where a text result is needed or there are multiple values separated by a carriage return.

Also make sure the "Do not evaluate if all referenced fields are empty" checkbox is UNchecked. This will ensure that a record displays under "All" even if you forget to put a month value in.

Calculation Result
And, check again to make sure the Month selection in the relationship goes from the
0
 

Author Comment

by:SFSDIT
ID: 36819169
Thanks but still have one more to ask.
When I choose "All" from the Month selection, it shows all (both of Aug and Sep) as you see from "All.jpg"
However, when I choose "Aug" from the Month Selection, it still does not show anything.
(see the "Aug.jpg")
And I also set BillMonth is calculation field (Calculation result is "Text and I also unchecked "Do not evaluate if all referenced fields are empty"(see Calc-Month.jpg)
What else do I need to do to show data when choosing "Aug" from the Month Selection?
All.jpg
Aug.jpg
Calc-Month.jpg
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 36822957
It seems like it has to be something in the relationship. Can you post a screen shot of the relationship dialog between STAFF and the utility table, showing the predicates for both SchoolYear filter to SchoolYear and MonthFilter to BillingMonth?
0
 

Author Comment

by:SFSDIT
ID: 36829655
I have attached screenshot of Relationship as you said
Do you see any problem?
Relationship1.jpg
Relationship2.jpg
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 36832602
I can't see anything wrong here, but here's the test. Add the BillingMonth field to a layout for the Utilities table (or temporarily to your portal) and ensure that Billing Month contains BOTH the Month value and the work "All" on separate lines. The fact that it works when you put in "All" for your month filter, but not when you select a month would seem to indicate that the BillingMonth calculation includes "All" but not the Month value.
0
 

Author Comment

by:SFSDIT
ID: 36843926
I added "Bill Month" Field to the utility table as you see from the picture
(Look at the "BillMonth-Utility.jpg")
There is nothing in the BillMonth Field in the Utility Table.
(Look at the "Utilitytable.jpg")
BillMonth-Utility.jpg
Utilitytable.jpg
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 250 total points
ID: 36848297
What it's is showing actually is a black line followed by 8 repetitions of the word "All". The first line is blank and this where you would expect to see the actual Month value. This points to a problem with your BillMonth calculation field. Bill Month should be a Calculation field with 1 repetition with the calculation equal to:

Month & "¶All"

Or

List( Month ; "All" )

with Month being the text field containing the three letter month value in each utility table. You'll need to see what is not right with this field and keep checking until the values show correctly. You should see:

Aug
All

I would expand the field large enough so you can see more than one line and also get rid of the drop-down list since you don't need it here.
0
 

Author Comment

by:SFSDIT
ID: 36878172
I changed List function from List(Ot_Month ; "All") to List( Month ; "All" ) and it works
Thank you so much!!!
0
 

Author Closing Comment

by:SFSDIT
ID: 36878416
Thank you so much for your help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
FM Exporting Images and ask for destination folder 1 182
FileMaker PDF Page Size 4 123
Filemaker Pro 14 - Summary Fields 2 64
printing table occurances in filemaker pro 10 8 43
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…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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 …

807 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