Count Unique Values

[HHMacGuffin]

Hi,

 

I have a simple correspondence database with three tables: Contacts (called PEO), Correspodence (called peo_peocoo_COO), and a joint table called peo_PEOCOO. In Contacts, one of the fields is Country. I need to give a single number of how many countries has received correspondence from my team, so I wanted to count number of countries (not the number of times each country has ocurred) in the joint table. I am however having difficulty at using the methods commonly posted in the forums and would really appreciate some assistance.

 

As an example, I have

China

China

Japan

China

US

Japan

UK

UK

The number I would expect from the above is 4 - Four countries have received our correspondence in the past.

 

I've tried using the ExecuteSQL method by creating a calculation field ("SELECT COUNT (DISTINCT Country) FROM peo_PEOCOO" ; "" ; ""). I however ended up with a question mark in every record.

 

I've tried the "sum the reciprocal" method, creating, in peo_PEOCOO, a summary field CountryCount (which resulted in total number of records), a caculation field FractionOfCountryCount (which shows number of times each country has ocurred), and another summary field FinalCountryCount (which resulted in the sum of number of times each country has ocurred). Still couldn't get the number of countries in the peo_PEOCOO table.

 

Finally, I tried "self join" method, creating a second peo_PEOCOO_SelfJoin, creating a calculation field in peo_PEOCOO called Counter, which is Count(peo_PEOCOO_SelfJoin::Country), and a summary field called CounterTotal which is a Total of Counter. It ended up again with the sume of number of times each country has ocurred.

 

I would grateful if the community can help point out what went wrong in all my methods. A great many thanks for your time and help.

 

Cheers,

The MacGuffin

[comment]

I am having problems understanding your request: do you mean you want to count the number of distinct countries for each correspondence?

 

 

so I wanted to count number of countries (not the number of times each country has ocurred) in the joint table

 

Isn't country an attribute of a contact? Why would it be in the join table?

[HHMacGuffin]

Hi Comment,

 

Essentially, I need to be able to say, "my team has compiled X correspondence sent to contacts representing Y countries this year".

 

Country is an attribute of a contact indeed. The join table only retrieves country through a calculation field base on the contact. This is made because each correspondence is essentially a template letter, and each letter is sent to multiple people from different countries - Like a mass email I guess.

 

We are less interested in the total amount of correspondence sent and the number of people received. Instead we're more interested at how many letters my team compiled, and the geographical coverage of the recipients. I can do this by exporting the joint table as Excel, and then do a remove duplicate of the country field and count number of countries there. I just wonder if FileMaker can calculate this number by itself so it can be automated to report this particular number.

 

If my set up is completely wrong, or there are better ways to obtain the numbers, your recommendations are highly appreciated - In the end, all I need is to be able to report how many letters were written and sent to how many countries.

 

Hope this makes a little more sense. Many thanks for your time and support.

 

Cheers,

The MacGuffin

[eos]

See if the attached file helps you.

Correspondence_eos.fmp12.zip

[doughemi]

@eos -- File is locked.

[LaRetta]

The file isn't locked for me.  

 

Can you post a screen shot of your graph showing the portions and tables which relate to this request?

 

I'm unsure why a simple sub-summary report would not work.  Place the Country field into a leading part based upon Country, create a summary field (summary count of table's unique ID).  After you create the report, delete the body so only the Country and the summary field are in the leading part.  Perform a find for the time period you wish to summarise and then be sure to sort by Country.  It will produce a list of unique countries with the total number of items sent to them.

 

If you can use a simple report, it would mean you do not need those extra 6 table occurrences (not to mention the multiple unstored calculations in each table) just to make it work.  It is also possible that your structure should be slightly adjusted ... something feels slightly off about it.

[LaRetta]

Actually, that will not give a unique count of Country but rather the number of correspondence sent to the Country but I still believe a report is your best option.  Check this out:

 

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__st__-20__p__289204#entry289204

[comment]

Essentially, I need to be able to say, "my team has compiled X correspondence sent to contacts representing Y countries this year".

 

If you had a structure like this:

Messages -< MessageRecipients >- Contacts >- Countries

you could perform a find in the Countries table, searching the Message::MessageDate field for the year of interest.

 

 

Another option is to have a calculation field in the Messages tables =

List ( Contacts::Country )

Find the messages for this year, and use the (new in v.13) summary field option of List of to get a list of the above calculation field. De-dupe the list by filtering it against a value list of countries and count the remaining values.

 

 

 

I still believe a report is your best option.

 

True, but from which table? There are conflicting sort requirements here.

[David Jondreau]

SQL is probably your best bet. Skip the Count, just use Select DISTINCT and wrap that in the ValueCount() function. Figure out what your syntax is wrong.

 

The reciprocal method is way overkill.

 

You can also grab all the countries (duplicates too) and wrap that in a custom function, like UniqueValues(). https://www.briandunning.com/cf/596

[Fitch]

I wonder if this would help:

http://filemakerhacks.com/2011/10/18/summary-report-in-a-filtered-portal/

[comment]

The issue with using SQL in situations like this is that you must mimic the find. That's a very wide opening for errors to enter.

[David Jondreau]

What find?

[HHMacGuffin]

Hi all,

Just want to say in the end I applied Comment's suggestion as it is easy enough for my level of skill to implement and it worked very well. A great many thanks to Comment and everyone in the community for the support and help.

Cheers,

The MacGuffin