Calculation of number certain type of contact per Company

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

Recommended Posts

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!

Share on other sites

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 by comment
Share on other sites

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

Share on other sites

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

Share on other sites

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

Share on other sites

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 by Chrism
Share on other sites

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.

Share on other sites

Great thank you.

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

I will update my profile now, thank you

Share on other sites

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 by comment
Share on other sites

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 by Chrism
Share on other sites

Right got it, done! Thanks

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

Share on other sites

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

Share on other sites

Thanks, I have adjusted my relationship as suggested.

And filter using an edited version of this?

`ValueCount ( FilterValues ( List ( Contacts::Type ) ; "Type1" ) )`
Share on other sites

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.

Share on other sites

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 by Chrism
Share on other sites

What makes a contact active or Inactive?

Edited by comment
Share on other sites

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 by Chrism
Share on other sites

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

Share on other sites

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

Share on other sites

• 4 weeks later...

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" ) )`
Share on other sites

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

Create an account

Register a new account