Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
7 months ago

Count instances of value in table prior to date

Hi everyone,

I'm trying to find a way to create a field that counts the # of times an email address exists within a table based on a date field within that record.

Example: Record is created with an email address and a date of interaction.  Goal - return the # of times that email address already exists in other records on or before the date of that interaction.

My formula query currently is: Size(GetRecords("{145.OBF.'" & [Date of Interaction] & "'}"))

Which DOES work, but it's counting huge numbers, and I don't understand why.  The results are in the thousands, for an email address that if I search for it, only exists 21 times.  

I *think* it's because my field 145 is also a formula query which reads - Trim(If([NEW Contact Email Address]="",[Contacts Table Field - Email Address],[NEW Contact Email Address]))

I'm wondering if it's not counting the results in the field, but query; however, when I switched the field to the field 'NEW Contact Email Address] I returned tens of thousands of records rather than the very few times a staff member manually entered it as a 'NEW' contact rather than looking up the email from our contacts table.

Ideas? and thank you!  

  • try this

    var text QUERY =

    "{99.EX.'" & [my email field] & "'}

    & " AND "

    & "{145.OBF.'" &[Date of Interaction]) & "'}";

    Size(
    GetFieldValues(
    GetRecords($QUERY),3))

     

    // change the 99 to the fid of your email field.

    • JenniferJuhasz's avatar
      JenniferJuhasz
      Qrew Cadet

      Hi Mark,

      Thank you so much for this - it does contain syntax errors that I'm not sure how to resolve.  It is looking for a semi-colon at the end of the first statement, but then when added it says the & in line two is "expecting a number".

      145 = the fid of my email field; not quite sure how to work my way out of this one.

      Thank you though for your help!

  • Just an update - I'm still trying to understand the Count vs. Size functions; if I do 'Count' I'm returning a '1' value for every single record ...  I'm just missing something probably very simple here

  • I've also tried a variation on a query posted by MarkShnier , but I'm not able to get a distinct count - it's only returning zeros:

    var text QUERY = 
      "{145.EX.'" & ("{145.OBF.'" &[Date of Interaction]) & "'}";

    Size(
    GetRecords($QUERY))

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      OK, try this.

      var text QUERY =

      "{145.EX.'" & [my email field] & "'}"

      & " AND "

      & "{145.OBF.'" &[Date of Interaction]) & "'}";

      Size(
      GetFieldValues(
      GetRecords($QUERY),3))

       

      Note that in Formula Queries there is not a COUNT function.  It's called Size.  ie Size means to count the number of records returned by the Query.

      • JenniferJuhasz's avatar
        JenniferJuhasz
        Qrew Cadet

        Hi Mark,

        Thank you so much for this.  It worked without a syntax error after removing the ')' following [Date of Interaction]; however, the results are still nothing - no counts of any kind.  

        When using an OBF function relating to an email address, I would think I would always have a '1' as a result, counting the record that's doing the request here...  If I search for a given email address I will get a range of results from 1 to 10 or 20 depending on the client, but never zero.  I understand the difference between Count and Size, but I still can't get either function to provide an accurate result.

        I wonder if my challenge is to do with that my field with email addresses is actually a formula query combining two different/separate fields (it's an if-then field) as staff will populate a NEW address field if the client is New to us or perform a lookup of our contacts table if it's an existing client.  Ie - if NEW is blank, then use Existing so that there's always something to report with.

        Thoughts?

        Thank you!