Solved

button to clear a month filter

Posted on 2011-09-26
13
392 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 24

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 24

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
 

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 24

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 24

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 24

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 24

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now