How can I count city field ?

[Mustafa55]

 I cant send a file, so I will try to explain my problem.  I have a field named  ''city''. it has so many datas, for example  ''newyork, paris,london,istanbul etc,'' and also there are so many istanbul or newyork records. I can summary theese records. and I can count how many records.and also This is a related database, my problem is that I cant count How man city is, when I count city field with summary , it gives everytime found records. I just want how many city, not how many records.

[IdealData]

A value list will do the trick...

 

1. Make a relationship to match ALL ( the 'x' join method) records. It doesn't matter which fields you use.

2. Make a value list (say 'AllCities'), using the 'city' field as the result. The value list must be based on the relationship.

3. Count the number of entries in the value list using [PatternCount ( "¶" ; ValueListItems ( Get (FileName) ; "AllCities" ) ) + 1]

 

The "¶" (pilcrow) character is the separator between the value list items so +1 is required as the list does not terminate with "¶"

 

Why does it work? Value lists are based on an index and multiple entries that are the same are condensed to only one entry.

 

Incidentally, you would also be better off using the value list as a drop down to reduce the possibility of mis-spelt entries. ('New York' and 'Newyork' are not the same)

 

HTH.

[Brooks]

Use valuecount ( executesql ("select distinct city from contact " ; "" ; "" ) ) I'm not by my computer but I'm sure you can use this as well executesql ("select count ( distinct city ) from contact " ; "" ; "" ) )

[Lee Smith]

Hi Mustafa, and welcome to the Forums,

 I cant send a file, 

 See this "How to add an attachment to a reply" here, Link

 

Please update your profile to reflect your Version of FileMake Pro, here is a quick link for that, My Profile

[LaRetta]

I can summary theese records. and I can count how many records... my problem is that I cant count How man city is, when I count city field with summary , it gives everytime found records. I just want how many city, not how many records.

 

Your report needs a leading part ( select menu item Layouts > Part Setup in layout mode) based upon the City field.  Place your summary field into the part and be sure to sort your report (including it in your Report script) by this City field as well.

 

This should produce different totals in each part associated with its City.

[Mustafa55]

 I couldnt still solve my problem  anyway thank all you.I will try different  ways.  I just would want to see all reports in the same page. I have to see city field count different page. I cant still send a file. I see this error "You aren't permitted to upload this kind of file"

[Brooks]

You have to create a compressed ( zip) file first, then attach the zipped file. You can not attach a fmp file without compressing it.

[Mustafa55]

ok I was doing a rar file before. thank you 

city.zip

[Brooks]

Here you go. It is exactly what I suggested before. It needs to be an un stored Calculation field. I also included a script method that loops and builds a list and then counts the values in the list. The SQL method will always count all records, not the found set. The scripted method will only count the found count. In addition I added a way to do it using 2 Custom Function. I take ZERO credit for the custom functions. They are freely distributed on www.briandunning.com.

city.fmp12.zip

[Mustafa55]

thank you so much I did some mistakes before. thanks all of you. İt is reallly nice to be in this forum