Newbies Mr. Ant Posted March 17, 2006 Newbies Share Posted March 17, 2006 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 Link to comment Share on other sites More sharing options...
Matthew F Posted March 17, 2006 Share Posted March 17, 2006 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 Link to comment Share on other sites More sharing options...
Newbies Mr. Ant Posted March 20, 2006 Author Newbies Share Posted March 20, 2006 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 Link to comment Share on other sites More sharing options...
Newbies Mr. Ant Posted March 28, 2006 Author Newbies Share Posted March 28, 2006 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 Link to comment Share on other sites More sharing options...
Matthew F Posted April 6, 2006 Share Posted April 6, 2006 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 Link to comment Share on other sites More sharing options...
Newbies Mr. Ant Posted April 6, 2006 Author Newbies Share Posted April 6, 2006 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6587 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now