Jump to content

Recommended Posts

I've done a lot of searching on this and can't find a solution. All I need to do is count how many records in one field has the word "clacorp.org" and also the word "sadccf.org". The field only has two allowable entries, which are: "clacorp.org" and "sadccf.org". I would like to get a count of the "clacorp.org" entry and put the number count in one field and get the count of "sadccf.org" and put the number in another field. 

I tried: ValueCount( FilterValues( List( domain_name); "sadccf.org")) and other caulations but can not get the numbers I need. 

I am only using one table. I need to know how many clacorp.org emails and how many sadccf.org emails. Seems simple but I am stumped.  

Edited by Grady Tracy

Share this post

Link to post
Share on other sites

two new calc fields: 

isClacorp = PatternCount ( domain_name ; "clacorp.org" )   

isSadccf  = PatternCount ( domain_name ; "sadccf.org" )

two summary fields:

isClacorpCount = Sum ( isClacorp)

isSadccf = Sum ( isSadccf )

  • Like 1

Share this post

Link to post
Share on other sites

Thank you so much for your help. The solution you gave worked great. I tried the "patterncount" but I did not do the summary field. I went to bed last night when this problem running through my head. I had a hard time sleeping last night, so I was happy to see the response when I woke up this morning. Thank you so much for your help!

Edited by Grady Tracy

Share this post

Link to post
Share on other sites

actually I think I was also over tired when I posted -

the latter two fields would be a Summary field using the Total of the corresponding fields. however that only evaluates correctly to the found set of records as a summary operate on the found set.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By -dp-
      I have a layout that includes two sub-summaries. The topmost includes a summary field called studentGradeCount, which counts the number of students in a given grade.
      The subordinate sub-summary contains applicationStatusCount, which is a summary field providing a count of applicationStatus.
      Adjacent to the status count, I would like to provide a percentage, indicating what percent of the students have applications at each status. Logically, it would be
      applicationStatusCount / studentGradeCount
      But FileMaker doesn't return anything when I attempt this. Any help would be appreciated.
      FMPA 16.0.3
      macOS 10.13.3

    • By sinisa93
      I have two separate questions regarding portals and how they work. Before I start let me describe what I'm doing. I'm constructing DB for local karate club. I have one table/layout with general info about members, I have second table/layout with records about belts (including color of the belt, when & where the belt was obtained, the mentor of the member & sparing partners of the member etc.) and I have 3rd table/layout that shows various activities of each member (there are also several other tables but not important for this topic). All tables are connected via key field (ID number)
      QUESTION 1
      I have no problem creating portals on 3rd layout to show me which belts particular member has with all additional info from the second table that go along with it. BUT I don't know how to create a portal that shows all members of the club that had particular member as sparing partner. Thing is that I have 3 separate fields for sparing partner because each member can have up to 3 sparing partners and I simply don't know how to connect them all so I can see the list of all members that have particular member as one of three possible sparing partners.
      QUESTION 2
      I know how to place count of related records in filtered portal but I'm not sure how to get a Total of this counts.
      Any help is much appreciated!
    • By AGilek
      Hi, everyone!
      In my database of weekly music charts (FMPro14) there are 4 linked tables:
      [Hits] - list of the hit singles
      [Entries] - list of different entries, remixes, reissues
      [Runs] - list of chart placings for every week
      [Dates] - list of dates of the weekly charts
      [Hits] contains:
      <idHits>     index field
      <Title>        text field
      [Entries] contains:
      <idEntries>    index field
      <Title>        text field
      <idHits_fk> number field, a foreign key which links [Hits] table with [Entries] table
      [Runs] contains fields as below:
      <idRuns>       index field
      <Place>         number field, position on the chart
      <LastWeek> number field, last week position 
      <idDates_fk> number field, a foreign key which links [Dates] table with [Runs] table
      <Title>           text field, title of a single
      <Name>        text field, name of the artists
      [Dates] contains:
      <idDates>    index field
      <Date>        date field
      Relations are as follows.
      [Hits] & [Entries] are linked by the relation:
      Hits.<idHits> = Entries.<idHits_fk>
      [Entries] & [Runs] are linked by the relation:
      Entries.<idHits> = Runs.<idEntries_fk>
      [Dates] & [Runs] are linked by the relation:
      Dates.<idDates> = Runs.<idDates_fk>
      Two years ago, on this forum, with a Consultant help, I have defined the self-join as:
      Runs::idEntries_fk = Runs 2::idEntries_fk
      Runs::idDates_fk ≥ Runs 2::idDates_fk
      where Runs 2 is a new occurrence of the Runs table. Then define a calculation field (result is Number) = Count ( Runs 2::idRuns )
      This will return the number of times the entry has appeared in the Runs table before (and including) the current week.
      So this allows counting chart weeks for every record in [Entries]. 
      Now, I’d like to also count TOTAL number of weeks, for [Hits], not [Entries], something like:
      Runs.<Place>; Runs.<LastWeek>; Entries.<Title>,  TotalWeeksCount for main record in [Hits]
      Example. Let’s say there is a hit single titled „Purple Rain”. Looking at its chart history we should get something like:
      Purple Rain - original entry - 10.10.1984 - 10 (0) 1 week
      Purple Rain - original entry - 17.10.1984 - 8 (10) 2 weeks

      Purple Rain - original entry - 14.12.1984 - 43 (33) 10 weeks
      — out of chart —
      Purple Rain - re-entry - 08.07.2008 - 56 (Null) 11 weeks *** 11 weeks, NOT 1st week of this new entry! ***
      Purple Rain - re-entry - 15.07.2008 - 48 (56) 12 weeks 
      How can I get such results? Help needed, thanks!
    • By pctechtv
      While creating a database that has Products, Suppliers and Lines I have come across the need to calculate a count of the suppliers from the context of a Products record. My relationship is like so:

      My question if I want to pull a lot of information about Supplier is should my portal in the Products record be from Suppliers or from Lines? If the best answer is Lines, could I still have a count of related Suppliers by using the primary key from that table in the portal? Is it a bad idea to use fields from a related tables portal other than its own? Thanks
    • By Raybaudi
      Someone asked if it is possible to count the pages of a PDF...
      The attached file contains a tail recursive custom function that can do the work for the most common cases.
  • Create New...

Important Information

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