Chrism 0 Posted October 3, 2020 Share Posted October 3, 2020 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
comment 1,777 Posted October 3, 2020 Share Posted October 3, 2020 (edited) 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 Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 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
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 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
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 Sorry, I had the arguments reversed. Try it now with the edited version. Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 (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 October 4, 2020 by Chrism Link to post Share on other sites
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 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
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 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
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 (edited) 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 Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 (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 October 4, 2020 by Chrism Link to post Share on other sites
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 (edited) 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 Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 Right got it, done! Thanks What calculation would I now use to achieve goal of excluding Inactive Contacts? Link to post Share on other sites
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 Just filter the relationship using both Type and Status (see my edited answer above). Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 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
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 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
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 (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 October 4, 2020 by Chrism Link to post Share on other sites
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 (edited) What makes a contact active or Inactive? Edited October 4, 2020 by comment Link to post Share on other sites
Chrism 0 Posted October 4, 2020 Author Share Posted October 4, 2020 (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 October 4, 2020 by Chrism Link to post Share on other sites
comment 1,777 Posted October 4, 2020 Share Posted October 4, 2020 Not sure how to explain this any better. See if the attached demo helps. FilteredRelationship.fmp12 Link to post Share on other sites
Chrism 0 Posted October 5, 2020 Author Share Posted October 5, 2020 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
Bertie 0 Posted October 27, 2020 Share Posted October 27, 2020 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" ) ) Link to post Share on other sites
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now