Jump to content
  • 0

Counting records submitted today


Go to solution Solved by sandy159,

Question

Hello -- I want to be able to show our patrons how many curbside requests were submitted today as of that moment, and I have been unable to do so. I had no problem getting a running grand total; you will see two different numbers (today and a grand total) in my example images. I was copying my grand total DataPage to try to tweak it for a 'today' total, so that is why they are both there. I intend them to be two separate DataPages, so you can just ignore the fact that you see me grabbing a grand total despite trying use filtering criteria that should (but does not) exclude the grand total from being accurate.

I have looked extensively for an answer online in general, as well as in these forums, so I hope I didn't miss an answer already sitting out there. 

I began by setting my initial filter criteria to require the timestamp to be equal to today only so far as the date (I thought this would ignore the time portion of the timestamp). I assumed that when I chose to display the results it would only count records as defined by that filter; however, it doesn't seem to be using the criteria I set in the way I expected. In one instance I just asked it to count the number of RequestIDs, b/c every request is given one, and I thought the criteria filter would limit it to just today. It did not, and my total for the day equaled my grand total (z_criteriaNotEnoughCountToday.png).

 

z_criteriaNotEnoughCountToday.thumb.png.bb1fea96f7e84291d312d8352fd2079a.png

 

I then tried two different things asking it to once again look at the timestamp and try to compare it to today. In each case it returned ZERO b/c, I am assuming, even though I asked it to only consider the date in both the criteria and in what I wrote, it was comparing down to the second with the time, so nothing, of course, matched for 'today' and I got zero results (z_codeNotWorkingCountToday.png).

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE [@field:Timestamp] = CURRENT_TIMESTAMP

OR

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE [@field:Timestamp] = CAST( GETDATE() AS Date )

z_codeNotWorkingCountToday.thumb.png.bb9a3f946160dcf089b2086b9ee372c7.png

The code I was writing in my second attachment was all from Googling around -- this is new to me -- so I expect I'm not doing/understanding it right and was hoping this might be a super-easy question for someone out there. Thanks so much. I hope I provided all the necessary info. 

Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0
  • Solution

Hello @SaraK,

Thank you for the detailed explanation. You have made a big research on this as well and were so close. 

Please try to use the following formula in the Calculated field to get the number of IDs that were Submitted Today:

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE DateDiff(day, [@field:Timestamp], SysDateTime())=0

Hope this helps! 

Please let me know if it works for your case.

Regards

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.

Guest
Answer this question...

×   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.

Loading...
×
×
  • Create New...