showing calc result in diff table

[irene41]

Hi everyone. I have another problem (surprise, surprise).

I want to count the number of records that fit a certain criteria and show the results in a different table. For example: If Table 1::Field 1 = 3, then I want it to count all the records that have Field 1 = 3 and put it in Table 2.

So far, I have: If ( Table 1::Field 1 = "3" ; Count (Table 1::Serial Number) ; 0 ).

The problem is is that FMP only looks at the first record of Table 1 and not the rest. I am obviously missing something but I can't figure out what it is.

Can anyone help me out?

[sbg2]

Well how is this mysterious number 3 defined in Table 2? You need to relate the two tables, Table2::MysteriousField = Table1::Field1 then get the count through that relationship.

[irene41]

I tried your suggestion but I still can't get it to work. I made Table 2::Answer Filed = Table 1::Field 1 and left the equation the same. I didn't get the results I wanted. Then I changed the equation to : If("3" ; Count (Table 1::Serial Number) ; 0 ). This worked until I manipulated records in Table 1 to see if the equation would accomodate these changes. Then the count went back to zero. Maybe I am not understanding the "relationship" part very well...

[sbg2]

If("3" ; Count (Table 1::Serial Number) ; 0 )

change to:

Count(Table 1::Serial Number)

When the Answer Field in Table 2 contains "3" then the Count Calculation should return the number of records in Table 1 where Field 1 = 3. If Answer Field contains "2" it will return the number of records in Table 1 where Field 1 = 2, etc. etc.

If you are still having probelms make a simplified version of your tables and post it here so that we can better help you through example.

[irene41]

I have attached a sample file of my database. I think my problem is in my "Usage" calculation in the second table. I believe it's not specific enough?

Or maybe I'm making this too complicated and there is a simpler way to count this?

[sbg2]

I cant download the attachment, not sure if its a problem only I am experiencing.

[-Queue-]

Please either Zip or Stuff your fp7 file, then Edit your post and put the compressed file in place of the fp7 attachment.

[irene41]

Sorry about that. And since I don't know how to edit, I just made a new post. I hope this attachment works...

Test_Sample.zip

[Lee Smith]

See those Icons and the word Edit in the body of "Your" POSTS.

Click on "Edit", it is a button that will allow you to do the things that Queue has been telling you to do.

Lee

:cool:

[irene41]

Ahh. I also looked under the "Help" tab of these forums and I seem to understand that the "Edit" button is timed? I see it now but I did not see it in the post with my first failed attachment. I wasn't quick enough to change it. Thank you guys for helping me out!

[-Queue-]

Table1::Field1 refers only to the first related record's Field1. I am uncertain how you expect to get totals based on related records, when each related record can have a different Field1 value. How is Table2 supposed to know which related record should be considered?

[irene41]

I undid the relationship between Table1::Field1 and Table2:AnswerField so that no longer exists.

What I wanted it to do was to distinguish between the different values in Table 1 and show the counted results in Table 2 by using "If" functions.

If FMP only recognizes the first record of Table 1, that would explain why one of my scripts only works on the first found record and doesn't follow through on the rest of the records.

So I guess I need to rework my thinking process...

[-Queue-]

Do you mean you want a count in Table2 of how many '3' records there are in Table1 for each product available in Table2?

[irene41]

Yes. ;)

[-Queue-]

Ah, well why didn't you say so?

See attached. Note that I have simplified (booleanized) some of your field definitions also. "If( test; 1; 0 )" is equivalent to "test".

Test_Samplev2.zip

[irene41]

That totally worked! You are brilliant! Can you tell me why I needed to have a related calculation field in both tables?

Now, I'm trying to use the same principle in the same tables but for a different field with slightly different criteria (instead of 3, I want it to be 2). I've noticed it only counts the files that have already met the criteria of equaling 3. Is there any way to make these two calculations completely separate?