Calculation of number certain type of contact per Company

[Chrism 0]

Hi,

My database has a relationship between Company and contacts.

Each contact is a different type, say type1, type2 etc

I want to be able to do a simple calculation so it displays a number of how many type1 contacts each company has.

 

Thanks!

[comment 1,777]

Well, you could do:

ValueCount ( FilterValues ( List ( Contacts::Type ) ; "Type1" ) )

But then you will also want the count the Type2 contacts, and soon you will have a bunch of unstored calculations slowing everything down. Note also that this will break if you rename the type.

So perhaps you should be looking for another way to accomplish your purpose - which you didn't share with us.

 

Edited October 4, 2020 by comment

[Chrism 0]

Thank you, I will only need to probably count type 1 and type 2 contacts for a specific purpose.

Simply put, I want a number field that shows how many type1 contacts a company has. So when I have the companies in List view I can have a column that says 'Type 1 contacts' and it displays how many there are per company.

Thank you

[Chrism 0]

I have tried the Calculation above and it only displays either a 1 (if they have a type1 contact) if 0 if they do not.

I need it to display 5 for example if they have 5 type1's

[comment 1,777]

Sorry, I had the arguments reversed. Try it now with the edited version.

[Chrism 0]

Yep great that works!

If I wanted to edit it slightly, so for it to show the number of Type1 and Type2 contacts combined, so if there were 2 x type1 and 3 x  type2 it showed 5.

Also exclude Contacts where their status was marked Inactive?

Sorry!

Thank you!!

Edited October 4, 2020 by Chrism

[comment 1,777]

The first part is easy: change "Type1" to "Type1¶Type2".

The second part is not. Filemaker has no conditional aggregate functions similar to Excel's SUMIF(), COUNTIF() and AVERAGEIF(). You could make your calculation loop over all related records, but if your intention is to display this in a list view, I would suggest you add another relationship to your graph and count the related records from there.

--
P.S. Please update your profile to reflect your version, OS and skill level so that we know what you can use.

 

[Chrism 0]

Great thank you.

Re the second part, add another relationship to where, a new table?

 

I will update my profile now, thank you

[comment 1,777]

3 hours ago, Chrism said:

add another relationship to where, a new table?

No, to another occurrence of the Contacts table and define it as:

Companies::CompanyID = Contacts 2::CompanyID
AND
Companies::Types = Contacts 2::Type
AND
Companies::Status = Contacts 2::Status

Then make sure the Companies::Types field contains a return-separated list of the types you want to include - either as text in a global field or hard-coded in a calculation field. Likewise the Companies::Status field.

 

 

Edited October 4, 2020 by comment

[Chrism 0]

So??

Copy the contacts table and create new one called Contacts2?

Create a new 'Types' field in the Company table with the list of Contact Types, make it a global field with list of contact types I want, guess that match the 'Types' in the Contact table

Create relationships as you have said above

Am I on the right track

But the new Contacts2 table has no data in it? Does it need too?

Note my company table has a 'Type' field too, so adding. a new Types field is ok

Edited October 4, 2020 by Chrism

[comment 1,777]

2 hours ago, Chrism said:

Copy the contacts table and create new one called Contacts2?

Neither. Place a new occurrence of the existing Contacts table on the relationships graph. 
https://help.claris.com/en/pro-help/index.html#page/FMP_Help/adding-tables.html

Edited October 4, 2020 by comment

[Chrism 0]

Right got it, done! Thanks

What calculation would I now use to achieve goal of excluding Inactive Contacts?

[comment 1,777]

Just filter the relationship using both Type and Status (see my edited answer above).

[Chrism 0]

Thanks, I have adjusted my relationship as suggested.

 

And filter using an edited version of this?

ValueCount ( FilterValues ( List ( Contacts::Type ) ; "Type1" ) )

[comment 1,777]

No, the relationship already does all the necessary filtering*.  All you need to do is count the related records, using either a calculation field defined in the Companies table as:

Count ( Contacts 2::CompanyID )

or a summary field defined in the Contacts table and placed on the layout of Companies.

---
(*) The reason behind this is that relationships use indexed fields to select the related records. Using an unstored calculation field to do the filtering "on-the-fly" would be much slower.

 

[Chrism 0]

Thanks but i'm not understanding how it is filtering out the 'Inactive' contact records?

Or filtering type1 contacts only, i'm not seeing anywhere in the relationship or count sum how it is defining those items?

Edited October 4, 2020 by Chrism

[comment 1,777]

What makes a contact active or Inactive?

Edited October 4, 2020 by comment

[Chrism 0]

The status field of the contact is to Active or Inactive, if they are inactive i do not want them to show in the calculation.

A contact may be defined as Inactive if they have left company for example 

Edited October 4, 2020 by Chrism

[comment 1,777]

Not sure how to explain this any better. See if the attached demo helps.

 

FilteredRelationship.fmp12

[Chrism 0]

Thank you, yes that makes sense. Didn't realise you could filter like that using relationships, very clever

 

 

[Bertie 0]

You could create a new field in your contacts table "TypeCorrected" which is a calc field, Case( your flag field = inactive;"Inactive";Type) and then 

ValueCount ( FilterValues ( List ( Contacts::TypeCorrected ) ; "Type1" ) )