Jump to content

Recommended Posts

Posted (edited)

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
Posted (edited)

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

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


  • Similar Content

    • By sinisa93
      Hi!
      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
      AND
      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


      View File pdf Pages Count
      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.
      Submitter Raybaudi Submitted 09/27/2015 Category Samples FM Version FM Version: 4
    • 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.
×

Important Information

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