Total Problem

[kenneth2k1]

There is no quick way to explain this problem, so please bear with me.

I ran into a bit of an odd problem the other day. I have two files- Prospects and Sources. Sources is a db of different advertising publications (like the Reader, monster.com, etc.). Prospects is related to this file because each source has generated a number of prospects.

In the prospect file, there are fields that hold a value of 1 if certain things are true. There are fields in the Sources file that count the amount of records that have 1 s per source. These figures can be seen in a list layout. So far, everything works great.

I have made fields that total the figures in the count fields and put them in the Trailing Grand Summary part. Everything appeared to be ok, until I noticed that the fields were not totaling correctly. I double-checked my work, and everything is working ok except the totals.

And heres something even stranger- I changed the total fields from Total Of to Count Of, and it counted correctly. Then I changed them BACK to Total Of, and everything totaled ok!! What is going on???

Thanks for the help

Ken

[kenneth2k1]

Just to follow up, I installed the 5.5 v2 updater patch, and the problem persists.

Ken

[Razumovsky]

I remeber encountering this problem once as well. It was on a PC with "my first DB" that was one large mess of workarounds, but I was convinced that something odd was happening. The way I got around this was through the "refresh window" script step. It seemed to fix the problem. I suspected it was a screen redraw issue (the calc was producing the right output, but the layout/screen was not redrawing it after I changed data in one of the reference fields), but had no clue as to why it was happening. I did not spend much time proving my theory, as I was in the midst of recreating the whole thing from scratch. Has not happened since.

Sorry that I cant be more helpful, but see if "refreshing" the window (or go to another layout and come back) changes anything.

-Raz

[kenneth2k1]

Thanks for the reply, I appreciate the feedback.

I will certainly try a refresh step and see if that helps. I haven't had a chance to see if the problem happens on Win, I have been working on MacOS 9.2.

Thanks. I'll let you know how it goes.

Ken

[kenneth2k1]

Follow up:

I've tried refreshing the screen, but it doesnt seem to help. Right now I just changed the field definition from Total Of to Count Of and back to Total Of and it is calculating correctly now. But in my experience, this will only fix it temporarily.

So I deduce that the error lies somewhere in how and when FM is calculating. After I jimmy rigged the calcs, I had a thought: how about Flush Cache to Disk? Do you think that might help to recalc since the problem seems to be fixed after re-defining the fields?

Anyways, I would really like someone to take a look at it. But right now it is too large to post here (plus its proprietary). I'll strip it down and hopefully one of you guys will be kind enough to take a look at it.

Ken

[kenneth2k1]

Ok, I feel really stupid. Upon trying to further troubleshoot this problem, I think I might have found the problem.

I have a button that navigates from the prospect file to the Sources File to view the list with totals. In that script was a Go to Related Record command, and I think that was messing up the totals somehow (althought I'm not sure why because all the total fields do is total the numbers in the other fields).

Well anyways, I'll see if this makes the problem go away. Sorry to whaste your guys' time.

Ken

[Ugo DI LUCA]

Hi Ken,

Anyway, as you were to count the number of values that holds a "1" in it, it seems to me logical to use the "Count of" instead of the "Total of".

I know that being a 1, both summary fields would lead with same result, but on a logical basis, forgetting that the values searched were 1, you had 2 options :

- Count Of (summary)

- Count(related)

File Maker Manual definition :

Total of : Calculating the total of values in a field

Count of : Counting the number of records that contains a value in the field.

[Vaughan]

Check that the relationship doesn't have invalid fields specified in the sort order.

Also check that the totaled field is of data type number not text.

I infer that you are using a summary field: have you tried using a simple calculation field with something like Sum(relationship::field) to do the maths? A calc field like this is correct no matter what the found set.

[Ugo DI LUCA]

Hi Vaughan,

Again, as I just said, it seems to me that the logic would be to use a Count(related) instead of a Sum(related). Or am I misunderstanding something here ?

[kenneth2k1]

Hey guys:

Vaughan, I have no sort defined. So I made a sort by Source Name. Also, I went back and verified that all calcs should return a number, and they do. You inference is correct that I am using a summary field. I made a group of Sum() calc fields to replace the summary fields, and I am going to test that.

As it turns out, Im not stupid afterall. The problem was not fixed as previously posted. However, I noticed a couple of other things:

-Flush Cache to Disk doesn't fix it.

-I noticed that if I delete records in the Prospects File and then go to my list in the Sources File, the fields will total correctly. It's just hard to troubleshoot because once the problem is fixed, I have to constantly be adding records and data to get it to screw up again.

Thanks for the pointers.

Ken

[kenneth2k1]

Ugo:

Yes there is a bit of misunderstanding. In a way you are correct: I use a count calc to count the amount of related occurrences for that value. Then I use a summary field to sum those counts. The count works great, it's the summary fields I am having trouble with.

I have been working to strip the solution down so that I can post it here, but I can't quite get it small enough yet. I might just upload it to a website and point you all to that. We'll see.

ken

[Ugo DI LUCA]

Hi Ken,

I finally get confused.

In your first post you said you were playing with "Count of" and "Total of". These are summary fields...

So, in fact you are using :

1. count(related records) based on 1 to 1 relationship

Let say you hav 4 occurences of 2,5,10,15

2. Total of (these related records)

And this last total doesn't lead to 32.

Is that the problem ?

[kenneth2k1]

Hi Ugo:

Thanks for trying to understand. I know its kinda confusing.

1. count(related records) based on 1 to 1 relationship

Let say you hav 4 occurences of 2,5,10,15

2. Total of (these related records)

And this last total doesn't lead to 32.

Just about. Only the four occurences would be 1,1,1,1 and the total is not 4 (well, it starts out ok, but as you add records and data, it becomes unreliable). It is always 1 because the calc in the Prospects returns a 1 if some conditions are true.

Ixnay on the upload. I won't have time because I am going to Europe for two weeks.

So I'll talk to you all after my hiatus

Ken

[Ugo DI LUCA]

Hi Ken,

I see you there instead of Europe. Will you be in Paris ?

Well, don't want to insist on this, but your last answer wasn't so different from what I had understood.

Anyway, as you were to count the number of values that holds a "1" in it, it seems to me logical to use the "Count of" instead of the "Total of".

I know that being a 1, both summary fields would lead with same result, but on a logical basis, forgetting that the values searched were 1, you had 2 options :

- Count Of (summary)

- Count(related)

Sure it's not logical that Sum of 55 values of "1" is different from 55, but it is logical to use Count of values with "1", and sure this will come with 55...

[kenneth2k1]

Hello:

Sorry. You were right, and I explained it wrong. I will explain from the beginning and maybe I can make more sense.

I have 2 files: Prospects and Sources. They are related. The lady at my work wants to be able to track what source generated the prospect (amongst other info). Some of the info they track are:

1) Hits (how many prospects from that source)

2) Appointments Set (how many prospects from that source have interview appointments)

...along with other info

There are various fields that hold this info, one is a checkbox field. I had to put transparent buttons over the list items so that a script would activate when an item is checked. I do this because not only do they want to track totals, they want to track amounts from month-to-month and yearly. So, in order to make that happen, I made a bunch of calc fields that look something like this:

HitsMONTH:

If(Month(RecCreationDt)=Month(Status(CurrentDate), "1", "") - this one will return 1 if the record's creation month is the same as the current month.

-then-

In the Sources File, I have another group of fields that count how many of these related ones there are for each record:

Count_HitsMONTH:

Count(SourceToPros::HitsMONTH).

I display these figures in a list layout. So far, so good.

Here is where the problem starts. I have a group of summary fields that are set to Total Of these count fields. I put them in the Trailing Grand Summary part of the list layout and they seem to total fine. But, as records get added, the count fields will count up right, but the summary fields don't total right. The reason why I cannot use Count Of in the summary fields is because it will count the number of records that contain a value for the field, not total the values.

So there you have it. I hope I explained it well enough. Althernate solutions are welcome.

Yeah, I havent left yet. I leave 3pm Sat. We will be in Rome, Florence, London, and Scotland. We had originally planned to go to Paris instead of Italy, but prior to the war breaking out and all the rhetoric between Americans and French, other members in our group wanted to change from France to Italy! I couldn't beleive it. They thought that we would be mobbed if we went anywhere in France. I tried to explain to them that it's not that bad, but to no avail. They already have their minds made up that they are not going to like French people for now.

How quickly we forget that without the help of France, America would not be here today.

Ken

[Ugo DI LUCA]

Thanks Ken for clarification. I'm quite sure now someone will come up with a solution for this.

Apart for this, I know this is not the place for it, but as an Italian living in France (so very objectively), you guys must know that French still loves and support Americans.

But in our "stupid" world, we just depend on how the "news/medias" wants it to be, using stupid polls, stripping the leader's interviews and choosing any image that will never help all this to calm down.