Jump to content
Sign in to follow this  
HollidaySlim

Global Fields and Find Issues

Recommended Posts

I have a database that I use global fields to calculate and count certain fields. When I first set it up I did not know that global fields did not filter during a find.

I have a number of calculation fields I am trying to filter by salesmen (field in my DB) and date. But when i filter the criteria my calculations are off because the global fields are still calculating for all records. Is there any way to accomplish what I want with out redoing everything?

Thanks,

Matt

***Attached is my database

Leads_V1.2_Copy042209.zip

Share this post


Link to post
Share on other sites

It's not clear from looking at your file what result you're getting and what result you expect. Can you give an example (what layout are you on, what button or menu did you select, which fields we're talking about etc.)?

Share this post


Link to post
Share on other sites

I would be looking at the leads layout. Basically i want to perform a find by sales person and then click on the weekly recap button (this takes me to the weekly recap layout). The weekly recap shows the percentages of my sales persons confirmed appts., demos, one legs, sales, etc.

Right now to count the number of confirmed appts, demos, and such I have a global field ( and relationship to certain tables for that field )that I enter what I want to count and it gets displayed on the weekly recap. But since I did not know that global fields cannot be filtered in a search it is throwing off all my percentages and such when I perform the find. I hope this clears up what I want to achieve.

Again thanks for any and all help on the matter!

Matt

Share this post


Link to post
Share on other sites

Globals don't work in a find. It's dangerous because the result is not always a "no match". Be extremely cautious with the results of your finds, especially since FileMaker 9 if you have globals or multi predicates of which one may be empty.

Share this post


Link to post
Share on other sites

The fields that are related via your global relationship are not affected by the current found set, as you've discovered.

One possibility is to modify your relationship to include the sales person ID. That way it will only show counts for the selected sales person.

Share this post


Link to post
Share on other sites

If i could sort by the sales person that would be awesome. My question is how would i achieve that? Just add my rep. field to the existing relationship? Also would there be any easier / cleaner why to achieve what I want? I am open to suggestions.

Thanks again! Matt

Share this post


Link to post
Share on other sites

A technique I use for filtering related data is to hold a calculation field in the related table that contains all the fields I want to filter by. I also add "All" to the calculation.

FilterField = "All" & ¶ & NameField

The pilcrow (¶) is important as it provides a return character to delimit the text entries. So an example field might be:

All

John

Now target your global at FilterField.

You can also use the relationship to provide a drop down list, so you will ensure that the selected text will always match something in the related table.

HTH

Share this post


Link to post
Share on other sites

If the calculations you want to see are based on summarizing the found set of records, then why not just use summary fields? (easier / cleaner / suggestions)

Share this post


Link to post
Share on other sites

I tried a summary field originally but could not get them to work. It would count every entry in the field and throw off my calculations, I could not tell it to summarize certain entries into the field, rather I could not figured out how to make it only count certain text entries.

Ie. in my "confirmed result" field on the leads layout, I could not create a summary field (on the "weekly recap" layout) that would only count the number of times "confirmed" was entered. It would count all entries instead, that in turn threw off my weekly recap calculations. Is there a way to use a summary field that only counts certain test entries?

Thanks for all the help guys! I really appreciate it!

Matt

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.