How can I determine the applications in my realm which have had more than 100 entries in a week?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
For inventory management I need to find the applications which have more than 100 entries by its users in a given week. Can anyone suggest me a way to accomplish this? 
Photo of Raj Helaiya

Raj Helaiya

  • 1,246 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Can you define what an "entry" is? Is it records created, records modified, records deleted, all of the above as a total? And when you say "applications", do you literally mean that or just tables? If you mean applications, which tables in each should be used to determine the entries, or should it be based across all tables within an app totaled together?
Photo of Raj Helaiya

Raj Helaiya

  • 1,246 Points 1k badge 2x thumb
Hello Michael, entries would be records modified. Yes, by applications I mean any record in an application if modified, I want to track that. I need to have the visibility of applications where they are modified  more than 100 times a week. It is not specific to any particular table, just the application in general. 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
I came to QuickBase from the Salesforce world, and they had a very nice, built-in change log feature that would let me see when and by whom a record was created, along with specifying important fields within each record and when and by whom they were modified (and what values they were modified from and to). There isn't too much I miss about SF, but this is something that QB doesn't do natively (and should!).

That said, I have not yet implemented a change log in my QB app, but I have been collecting and studying resources. Here are a couple of good links for you to read:

https://community.quickbase.com/quickbase/topics/is-there-a-way-to-show-how-many-times-a-record-has-...

https://community.quickbase.com/quickbase/topics/creating-a-change-log-using-webhooks

https://community.quickbase.com/quickbase/topics/does-qb-store-a-log-of-changes

https://community.quickbase.com/quickbase/topics/is-it-possible-to-create-a-field-that-will-log-edit...

https://community.quickbase.com/quickbase/topics/change-log-for-a-related-field

In addition, you might go here and search for: log of changes
https://community.quickbase.com/quickbase/topics/search/show

I personally like the Gmail option that was mentioned by Mark in one of these threads. I will probably do that when I get around to it.

Since you need counts within a date range, you will most likely need to create a ChangeLog table and write out records to it using actions or webhooks and store fields for:

date/time
user
application
table
action (add, change, delete)
field changed
field before value
field after value

The pain with doing this is that you literally have to set up this for each table and each field that you want to track. Straightforward to do, but time-consuming. And then you need to keep it up as you change and add apps, tables and fields. It's overhead. Is it worth it?

Perhaps you could take a look at your apps and tables and look for some important indicator tables and fields and just set it up for those to serve as a proxy to get overall totals on usage stats.
 
Photo of Raj Helaiya

Raj Helaiya

  • 1,246 Points 1k badge 2x thumb
Thanks Michael, I have a lot of applications (hundreds) so I don't think creating a change log for each table is worth the time. Also, I need to monitor the applications in my realm which change in anyway so I can track which applications are used more. I think I need to find a better way to do so. 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Why do you have so many separate apps? I think it ends up being much harder to manage that way. I have a single app with 75+ tables. Everything ultimately needs to tie together.
Photo of Raj Helaiya

Raj Helaiya

  • 1,246 Points 1k badge 2x thumb
Well, we have client facing apps for specific purposes.  Thanks for sharing the articles. I'll definitely look into them and see if I can track important apps. 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
You can secure anything within a single, large app so that only a small subset is exposed to clients or any other role. Good luck with your project!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,482 Points 20k badge 2x thumb
I can think of two different ways to do this:

1) Script

You could iterate through all of your applications and tables using API_GrantedDBs and for each perform a query that will return or count those records created in the last week. What query you perform depends on the state of your applications as you may not have a known apptoken to use against all applications. Also this process could generate a lot of API calls which could be expensive if it ran too frequently, take long time or even grind your browser to a halt.

2) Service Worker

You could also implement a Service Worker that silently monitored every network request and counted or logged all requests of a particular type.  This approach could only work for Chrome, Firefox, and Opera browsers as we are waiting for Service Workers to land in Edge and Safari.
Photo of Raj Helaiya

Raj Helaiya

  • 1,246 Points 1k badge 2x thumb
Hello Dan, the service worker option looks good. Can you please elaborate on how to accomplish the task using service worker? 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,482 Points 20k badge 2x thumb
You would need a variant of this example which logs all URLs 

https://community.quickbase.com/quickbase/topics/service-worker-travel-log-day-4

You would have to decide which URLs indicate activity in the database and increment a counter and save the result back to the log table. In the above example all URLs visited were logged in the log table. In your case you would be logging a counter after it was increment in the log table.