Counting unique values from drop down lists

[chrisgv]

Hi everyone, I'm somewhat new to FMP and was hoping I could get some help with my problem. So:

I have a field called "Products". Users can choose a product from a drop down value list. The value list of the Products are:

Pamphlets

Kiosk

Poster

Document

Banner

So, in my report, I'd like to be able to have something to show the amount of each product that was chosen. For example, if I had 27 records, it would look something like this;

Pamphlets: 5

Kiosk: 10

Poster: 2

Document: 4

Banner: 6

I'm not sure the best way to go about doing this...

Any help would be really appreciated!

Thanks

Chris

[Vaughan]

Read up on summary reports. In this case you'd create a summary field to calculate the total of "amount" and use the product name as the break field.

IF you don't have an amount field and each record represents 1 item, the make the summary field "count" the product name field instead.

[chrisgv]

Sorry, I'm gonna need a little more hand-holding than that

Any advice on where to look? I've been searching Google for hours and I'm not finding anything (that I recognize, anyway)

[chrisgv]

Also, I'm not sure if my first post was clear... What I'm trying to do is count the amount of times each product was picked. For example;

Record 01 - Product: Banner

Record 02 - Product: Kiosk

Record 03 - Product: Banner

Record 04 - Product: Banner

Record 05 - Product: Banner

Record 06 - Product: Kiosk

Record 07 - Product: Banner

Record 08 - Product: Poster

Record 09 - Product: Poster

Record 10 - Product: Document

Would return a summary of:

Banner: 5

Kiosk: 2

Poster: 2

Document: 1

Thanks for the help guys!

[Vaughan]

I've been searching Google for hours and I'm not finding anything (that I recognize, anyway)

Try a search for "filemaker summary report". Note that the solution will require some effort on your part.

[chrisgv]

Okay, moving on...so for this same database, I have multiple drop downs where a user can pick up to 6 different products. For example:

Record 01:

Product 01: Banner

Product 02: Kiosk

Product 03: Poster

Product 04: Document

Product 05: Business Card

Product 06: Flyer

So what I want to do is add up all the products from the different records from all 6 product fields. Each record can have up to 6 different products, but doesn't have to have all 6 filled out. Most records will only have "Product 01" filled out.

Clear as mud?

[comment]

I have multiple drop downs where a user can pick up to 6 different products.

That's not a good method. You should use a separate table for the selections with each selection being an individual record, related to the parent "Record 01".

[Vaughan]

I have multiple drop downs where a user can pick up to 6 different products.

Ah, that's not what you implied in your original post. You said: "I have a field called "Products". Users can choose a product from a drop down value list."

As Comment suggested, the multiple-field data structure is not correct and this is what is causing the reporting difficulties.

[chrisgv]

Hmmm... I do have 6 different fields if that makes a difference (product 01, product 02, product 03, etc.)...

[Vaughan]

Yes, it makes a difference... it's practically impossible to do something with the data when it's stored like that.

BTW it's a classic trap for newbie developers. Do yourself a favour and fix it now.

[chrisgv]

What makes it a bad option? Is it because I have multiple fields?

I'm confused as to why this is wrong as I don't see the difference between having two fields called, for example, product and cost and two fields called products 01 and products 02. Each field is asking for individual data. Is it because I want to merge the data afterwards?

I realize I'm a "newbie" at this guys, but I'm trying to learn as I go, so knowing why I'm doing something or why it doesn't work really helps me out.

[Vaughan]

Lets say you have 4 product fields in each record: Product1 to Product4. One record has "Flyer" in it. Find it. Print out the list of all the products selected.

It's a hell of a lot easier if each record only has one product in it.

[chrisgv]

Each record does not only have one product in it though. Each one of our records can have multiple products - that's just the way it is. It may be easier to have just one product, but we need to have multiple products per record.

I think if it like a movie store:

Record1: Movie1: Star Trek, Movie2: Lord of the Rings, Movie3: Goonies

Record2: Movie1: Lord of the Rings, Movie2: Star Wars, Movie3: The Shining

Record3: Movie1: Gladiator, Movie2: Goonies,

Summary:

Star Trek: 1

Lord of the Rings: 2

Goonies: 2

Star Wars: 1

The Shining: 1

Gladiator: 1

A customer can rent multiple movies per transaction (record) or just one if they want, so shouldn't each record be able to have multiple movies? Is something like this not possible?

[Vaughan]

I'm trying to learn as I go

Ok then take the advice that the multiple field structure is a dead end.

The relational structure replaces the multiple fields with multiple records in a related table.

If you have FMP 11 then take a look at the Invoices starter solution.

[comment]

A customer can rent multiple movies per transaction (record) or just one if they want, so shouldn't each record be able to have multiple movies?

Yes, it should - but not as fields. Each transaction should have multiple related records in another table.

[chrisgv]

Okay, I think I'm starting to get it. I looked at the invoices solution and that helped quite a bit. I guess I'll have to use a portal, like they did in the invoices solution to achieve what I want.