Jump to content

how can I count occurrence number of each item in value lists


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

Recommended Posts

I'm using FileMakerPro Adv 13

 

Considering There are records like these below

 

1 Orange

2 Apple

3 Strawberry

4 Apple

5 Lemon

6 Apple

7 Orange

8 Melon

 

I have a value lists called "TypeOfFruits"

then If I execute this function

 

Substitute(Substitute ( ValueListItems ( Get ( FileName ); "TypeOfFruits"); "¶"; "n") ;" ";"")

 

I get this output

 

OrangenApplenStrawberrynLemonnMelon

 

It is shown on a page just like this

 

Orange

Apple

Strawberry

Lemon

Melon

 

If I want to count all of them and want an output like this, how can I change my function above?

(sorted by the number of count)

 

3...Apple

2...Orange

1...Strawberry

1...Lemon

1...Melon

Link to post
Share on other sites

you can always use this function in a script step setting a field. - just change the items in bold your field names and table name.

 

ExecuteSQL ( 
"SELECT typeOfFruits, COUNT(*) FROM sample GROUP BY typeOfFruits ORDER BY 2 DESC"
 ; "" ; "")

or if you want it more in line like you example 

 

3...apple

2...orange

1...strawberry

1...lemon

1...melon

 

ExecuteSQL ( 

"SELECT COUNT(*), typeOfFruits FROM sample GROUP BY typeOfFruits ORDER BY 1 DESC"
 ; "..." ; "")
Link to post
Share on other sites

 

Thanks for introducing the custom function!!!

 

This is what I wanted!!! Thanks so much!!!!

 

you can always use this function in a script step setting a field. - just change the items in bold your field names and table name.

 

ExecuteSQL ( 
"SELECT typeOfFruits, COUNT(*) FROM sample GROUP BY typeOfFruits ORDER BY 2 DESC"
 ; "" ; "")

or if you want it more in line like you example 

 

3...apple

2...orange

1...strawberry

1...lemon

1...melon

 

ExecuteSQL ( 

"SELECT COUNT(*), typeOfFruits FROM sample GROUP BY typeOfFruits ORDER BY 1 DESC"
 ; "..." ; "")

 

This SQL also looks useful!!

 

Thanks for the advanced technique!!

Link to post
Share on other sites

This topic is 2037 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
  • Similar Content

    • By JLanclos
      I would like help designing a drop down value list which filters the list based on what is already type in the input field. See example - admin 1234 - When they click on the field the drop down menu appears and would be filtered by the first letter of what they typed in so the user can correctly match up with the exact name - the field data comes in from a web form and I do not want to change the web form - just the value list - thanks
      ValueLists.fmp12
  • Who Viewed the Topic

    2 members have viewed this topic:
    Kitthawkp40  Daemon 

×
×
  • Create New...

Important Information

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