"This is needed in the form layout, not as a report."
When determining functionality, it is important to know how the result will be presented. Letting us know that it needs to be on a form and not a report is one important piece. But the other important piece is ... where will this 'list' be presented? IOW, is it on your Products layout where, as you scroll products and if you are viewing ANY Sony product, it displays a list of all Sony Products (in order of Category) and then the quantity available? Or is this one list that displays on a Main Menu somewhere or on an orders table or?
This summary will be on the brand's form layout. So basically I need some kind of calculation to get the result. It's not a list, but just individual fields.
So i need to check if the status is not "sold," and then count the number of product pk's in each separate category from the value list.
What tables and relationships have you set up? It sounds like you have a table of "Brands" that is related to a table of Products. Do you have this structure for your invoicing?
Invoices----<LineItems>------Products>------Brands (---< means 'one to many')
I have Brand -> Products (and there the category dropdown) -> Invoices
How do you list the purchase of different items on the same invoice? (that's the purpose of the table you do not have.)
Each invoice is for only 1 product.. So I don't have many-to-many relationship there.
First, the simplest approach. It may not be what you need in the final run, but it gives us a starting point.
Create a new layout based on Products.
Add two sub summary layout parts, the first will be "when sorted by" your Brand field.
The second will be "when sorted by" your category field (TV, DVD, etc.)
Define a summary field that counts your primary key field in Products.
Put this summary field in the category sub summary part along with the category field.
Delete the body part of this layout.
In browse mode, perform a find for all records in Products that have a status of "available".
Sort this found set, first by Brand, then by category to group your records in this fashion.
(Note, this find and sort can be done by a script that is performed automatically each time you select this layout.)
You'll now see a list of brands and categories with a count of the available products in each category.
Thanks for the suggestion! But I need to come up with something without involving reports or separate layouts (if that's possible).
Sounds like a recursive script or looping script to me for what you want
Go to First record, match certain criteria and set a $$COUNT
Reiterate on 2nd pass to $$COUNT + 1
GETS YOUR TOTAL COUNT.
Can describe a little bit more how to do that?
Sounds like that might be the solution.
Count() function ( built in fm function)
Scripts - Filemaker script reference link: http://www.filemaker.com/downloads/documentation/fm8_script_steps_reference.pdf
Again, need more information to help , but what you are asking is quite easy if you just write a simple script that "COUNTS" the records in the field(s) you are
hope that helps.
Yeah, that's what I need.
I tried different functions with count and value list.. Nothing worked.
So it's smth like this: if status=available, then count valuelist="type" and count value 1 for related _pkbrand.
valuecount( ValueListItems ( get( fileName) ; "vl") )
tried this and i get the count.
valuelistitems(filename; nameofValueList )
I get the count too, but not of what I need. value count gives me 4 at all times and I already know how many values are in the dropdown.. I need to count the records with specific value in some way. Any other suggestions?
Thanks for all the help.