Help with Counts of a field value

[Mr. Ant]

Hi

I need help with what I think is very simple but haven't used FMP for so long I forget how to do it.

I have create 1 database with 3 tables. 1 table for customer details, one for products and one for sales. I have linked the sales and customer tables together via a unique customer number and the product and sales tables with a product ID.

In the sales table there is a field with a status of values or either A B C D or E for each. On the product database, I want to show for each product how many of the product have value A. How many B etc as a count or number (say 2 of A and 3 of "B"). I have the fields set up and tried using a count calculation but it seemed to only return 0 or 1 rather than say 0 and 3.

How do I get the product table to show the count for that product in the sales database???

Hope you can help.

Many Thanks

Ant

[Matthew F]

If I understand correctly you're trying to do a limited count of a field in a related table. You might consider the following:

1. The Customers table is not relevant to your question so I've ignored it in my suggestion.

2. A solution to your problem might include:

a) A second occurance of the Sales table (called Sales status) in your relationship graph which is joined to the main Sales table using Status as the match field.

A third occurance of the Sales table (called Products status) which is joined to Products with the combination of Product ID and Status.

c) A Status Count calculation field in the Sales table = count(Sales status::Status)

3. As an aside, a one-to-many relationship between Products and Sales may be a simplification of your actual needs. What if a Sale involves multiple Products? You'll probably want an additional table which combines many products in a single sale with a single Transaction ID.

See the attached file as an illustration.

Test.fp7.zip

[Mr. Ant]

Many thanks! I will have a go at this over the next few days and let you know how I went.

I just got the Portal feature working and am very exited about that!

Ant

[Mr. Ant]

Hi Again

I finally had a chance to work on this with mixed results.

Your comment around a one to one relationship between product and customer was great - I used your idea to creat a receipt that lists all the sales for 1 transaction ID (created a new table and linked it by transaction ID).

However getting my stock info working was less sucessful. I will try and explain my database and requirment in a little more detail:

I have 1 table with a field called Item Status. This field has values such as On Order, Sold etc.

This table also has product ID and is linked tot he product table via this.

On the product tabel, for each product I want a field that gives a count for each of the status. So I might have say 4 fields: # Paid =3; Number Sold = 2; Number on Order = 4 etc. So far I have tried a count:

Count (:Sales Table:Item Status)="Sold"

But does not work. I have tried a bunch of things without luck. As a work-around I have a "portal" on the product page so it lists all the status such as:

Sold

Sold

On Order

Paid

Paid

etc

It will get me by for now but I really need a count for each status type.

Hope you can help.

Thanks again!!!

Ant

[Matthew F]

Did you take a look at the .ZIP file that I attached before? I think it solves the problem that you are asking about.

I have now added some data in the status field to make it more obvious how it works. Go to the Product layout and click on the status button to toggle between "Sold", "On Order", etc.

:D

Test.fp7.zip

[Mr. Ant]

Hi

That's OK. I will have another look - I did look at your last example but wasn't sure how to get that to apply in my scenario - I will look at the new one and hopefully that will click.

Thanks again - your last post helped me a lot all the same http://fmforums.com/forum/skins/Professional/icons/smile.gif

Ant