Jump to content
Sign in to follow this  
megalomania

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

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

Share this post


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

Share this post


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!!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • 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

    1 member has viewed this topic:
    grostete1 
×
×
  • Create New...

Important Information

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