Jump to content

Counting unique values from drop down lists


This topic is 4310 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4310 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.