Count instances of value in table prior to date

Question

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.&nbsp; 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.'" &amp; [Date of Interaction] &amp; "'}"))Which DOES work, but it's counting huge numbers, and I don't understand why.&nbsp; The results are in the thousands, for an email address that if I search for it, only exists 21 times.&nbsp;&nbsp;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!&nbsp;&nbsp;

markshnier__you · Answer

try thisvar text QUERY ="{99.EX.'" &amp; [my email field] &amp; "'}&amp; " AND "&amp; "{145.OBF.'" &amp;[Date of Interaction]) &amp; "'}";Size(GetFieldValues(GetRecords($QUERY),3))&nbsp;// change the 99 to the fid of your email field.

markshnier__you · Answer

OK, try this.var text QUERY ="{145.EX.'" &amp; [my email field] &amp; "'}"&amp; " AND "&amp; "{145.OBF.'" &amp;[Date of Interaction]) &amp; "'}";Size(GetFieldValues(GetRecords($QUERY),3))&nbsp;Note that in Formula Queries there is not a COUNT function.&nbsp; It's called Size.&nbsp; ie Size means to count the number of records returned by the Query.

jenniferjuhasz · Answer

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 ...&nbsp; I'm just missing something probably very simple here

jenniferjuhasz · Answer

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))

jenniferjuhasz · Answer

Hi Mark,Thank you so much for this - it does contain syntax errors that I'm not sure how to resolve.&nbsp; It is looking for a semi-colon at the end of the first statement, but then when added it says the &amp; 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!

Forum Discussion

Count instances of value in table prior to date

6 Replies