Count how many conversations users have logged with other employees

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
We have an app that users in our offices are asked to log when they have conversations with their co-workers about monthly subjects. What we are seeing is that various users in a single office are accidentally recording that they are having a conversation with a co-worker that another user has already recorded having. We only want to log 1 conversation with each co-worker per month. I already have a field that populates "yes" or "no" whether the current conversation took place during the current month time period, and have a connected table that contains a list of every co-worker creating a "related co-worker" field. So I would like to have the form count how many times already during the current month time-period has there already been a conversation with the selected related-co-worker.


If we already have records like
Current Month?    Related Co-Worker
Yes                       Bob Smith
Yes                       Jack Jones
Yes                      Greg Doe
No                        Bob Smith
No                        Sam Garcia

If I were to add a new record for Bob Smith this month, I would want the form to indicate that there has already been a conversation with Bob Smith with this month, giving a conversation count of 1. In addition, if I add a new record for Sam Garcia I would expect the form to know that Sam Garcia does not have a logged conversation for current month and show 0. Any and all help is greatly appreciated.
Photo of Alexander

Alexander

  • 30 Points

Posted 8 months ago

  • 0
  • 1
Photo of Alexander

Alexander

  • 30 Points
I am trying the following field formula with no luck...
Count([Related Co-Worker],[Related Co-Worker],If([Current Month?],"Yes",1,0),false)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Without some custom script, it is not possible to evaluate other records that are conditional on items being entered in a form.  Especially with so many variables.  (Dates, resource 1, resource 2) and if those variables are in different orders.

If you are looking to keep it as 'native QB' as possible, I'd recommend handling this with well designed embedded reports that show existing logs, and connections.  Thus allowing the user easy visibility to the previous logs.


Matthew Neil - Product Specialist
(Edited)