Global Fields and Find Issues

[HollidaySlim]

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

[Fitch]

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.)?

[HollidaySlim]

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

[fabriceN]

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.

[Fitch]

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.

[HollidaySlim]

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

[IdealData]

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

[Fitch]

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)

[HollidaySlim]

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