Maybe you could try this:
- create a calculation field in your table. You could name it: TopSellingProduct
- Set your calculation in the options dialog and click "Storage options"
- enable "Use global storage"
Now you have a calculation field that performs your calculation. Because of the "Global storage" the value of that field is the same for every record.
- Put that field as a merged field in your footer.
Let me know if that solves your problem.
You might define a summary field as the "maximum" of this field's value and put it in the footer.
Thanks for your quick reponse Phil and DaSaint.
Tried both your suggestions but it didn't work. When I define a summary field as the "maximum", the data displayed still changes if I click on the layout. And Global Storage somehow does not display the correct data.
Could you give us a little more information on the tables you are using and possible erlationships.
Or where your data is coming from?
It's a little hard to try and find the problem when we don't really know what you are trying to do.
If you could give us a striped down version of your file, that would be the best.
But some more info or some screenshots might also help.
I made you a very simple, tiny example file.
It shows what I think you are trying to achieve in a very simple way.
Now I'm sure your file is more complex, but without more info I can't know where the problem might be.
What I made is a very simple product list with products that all have a price. And I made a note on the header that shows the price of the most expensive product.
I did it using a summary field and once using a calc field to show the text.
Please let us know if this is helpful.
Thanks for your example DaSaint.
I have sent you a sample file (http://dl.dropbox.com/u/974264/summary_test_copy1.fp7)
These are the issues:
#1. TOP PRODUCT: <<c_maxProductType>> US$<<c_maxProductSales>> <<c_maxProductQty>> units
This merge field changes when you click on the category summaries (in the purple section). This was my original problem at the start of this message.
This total has started to cgange also! To get it to display the accurate total, I have to go to manage database and double click the calculation. This did not happen before. Sigh!
#3. Also, the summaries are according to Product Types. But I have been trying to get the Product Type summary to appear in the layout, according to Product Sales. I did create a script button to run a sort script but that messed up the cTOTAL_MTH_SALES. I go rid of the script button but I think that is why the cTOTAL_MTH_SALES got messed up (problem #2).
Would really ppreciate some help here....and I apologize if this seems really a basic issue. Thanks in advance from a newbie.
I looked at your demo file and didn't find any problems with it. It as three records with prices of 10, 20 and 30. The summary field correctly returns a maximum price of 30 and clicking on different records or into different fields does not alter the value returned--as expected.
What exactly are you doing when you see this total change?
(Summary fields summarize the records found in the current found set, the value should not change unless you either edit a field in one of the records in the found set or you add/remove records from the found set.)
Hi Phil - I think you were looking at the sample DaSaint sent to me. My sample file name is: summary_test_copy1.fp.
You are correct. I don't see any link to a file of that name in this thread. How does your file differ from the demo?
Clicking on different records should not modify the results displayed by summary fields, though editing them, or modifying the records in your found set will affect the totals they display.
I just started looking at your file and the first thing I noticed is that you don't have a single Id field set up.
And your relations are based on text fields. That might be a first problem.
You are better of using a number field as a Unique ID field with automatic serial numbering turned on.
Also, what is your intention with the "Product List" and "Product Range Data" tables?
They seem almost the same.
Is it the intention to create a list of Products and a list of categories? Because that can be made slightly differently.
Also for your Max Product Type you use the calculation: "Max ( PRODUCT TYPE ; PRODUCT TYPE )"
You don't need to name your field twice. But the calculation is set up to result in a "number" but your Product type field is a Text field.
I'm not done looking at your file and I would like to fix it a little. I'm going to continue on it tonight.
Hi DaSaint - I feel you will find more things that need to be fixed. I only started with databases and fmp in Feb, and there are heaps I'm still figuring out. I really appreciate your help. Look forward to your next post!
Hi mora, I'd love to get you started in the correct direction.
But I'm having a few problems understanding the data.
- A product List
- Product Categories
- Design Data
- A Store list
I would like to fix these first before I dive into the sales data.
Explain to me a litte what you would like to do. I assume you need a list of stores, and every store sells a certain product.
Every product can be in a certain categorie, but not every store sells every category, sometimes they only sell certain product items from a certain categorie.
I don't entirely understand the "Design" part. Is that per product? Or how does that work? Designs appear available in certain stores, but how are they linked to products?
Design Data = a record of the designs created. New designs are added to the list. These are the designs that have been made into fabrics, that will then be made into products.
Yes, not all stores sell everything. Some stores are just kitchen shops sp will stock only kitchen ware.
Product List and Product Range: I started with just Product Range. But i had to create the Prodict List for the conditional drop down list (used for the sales data). We have a huge list of products in each category.
What I need to do is produce monthly reports to show:
1. Best selling store
2. Best selling design
3. Best selling product
Phil, the sample I made is in http://dl.dropbox.com/u/974264/summary_test_copy1.fp7
Hi Mora, I'm about halfway trough your database :)
I started fixing the ID's etc. and now I just started messing around with the reports.
It's getting a little late here, so here is my current version.
I'm going to look a little further at the reports. But I don't know if your version of a report is going to be possible.
I think you'll need to make seperate reports to determine what the top selling store is. What the top selling Product is and what the top selling design is.
(But I'm stil brainstorming on that) :)
I'm having problems with the sorting.
In the mean time, have a look here:
I noticed you made a lot of calculation and summary fields, I guess you must have been experimentng. I hope this makes a little clearer.
I don't know how far along you are in using layouts but I made a few.
I always keep my tables separate from my layouts because layout objects like checkbox sets and dropdown lists can sometimes hide the real data that's in a field.
I'm not yet happy about the sorting, but I'll try and do some more tomorrow.