Jump to content

Calculation of number certain type of contact per Company


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!

Link to post
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
Link to post
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

Link to post
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

Link to post
Share on other sites
Posted (edited)

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
Link to post
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.

 

Link to post
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

Link to post
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
Link to post
Share on other sites
Posted (edited)

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
Link to post
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" ) )
Link to post
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.

 

Link to post
Share on other sites
Posted (edited)

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
Link to post
Share on other sites
Posted (edited)

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
Link to post
Share on other sites

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

 

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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