Dynamic Roll up: Count the number of times a specific linked record has occurred

[Oli_Reynoldson]

Hi there,

I have a base with the following tables

1. Issue Log (All incoming issues raised)
2. Issue type (All issues and how many times the can occur before an action is taken)
3. Users (All the users linked to a specific issue)

I am trying to do the following.

a) User fills out the form logging a specific type of issue for a specific user.
b) I am looking at the number of times the specific type of issue can be logged before an action needs to be taken.

Where I am having trouble is the following

I want to know for that user how many times the specific issue has been linked to them to determine wether to take an action.

Ideally I’d like to do thing using something like a COUNTIFS based on the user and the number of times the specific issue being logged has occurred for that user.

The two fields I want to conduct the rollup for

Rather than having the total issues related for a specific shopper I want the number of times that specific issue has occurred for a shopper.

I can create a roll up on the shopper table for each type of issue, however, I’m then not sure how to define the column to look for based on the issue logged.

[TheTimeSavingCo · Accepted solution]

Hi Oli, I’m not sure if my understanding’s right, but I made something here for you to check out

It runs on the assumption that the issue types won’t change much though, as you’ll have to make a single column per issue type. Let me know if your issue types change a lot and I’ll see what I can do

[Oli_Reynoldson]

Hi Adam,

That gave me a nudge in the right direction.

What I will have to do is use your approach and create a rollup for each issue type and then use a large nested IF statement to determine which column to calculate on based on the issue type selected.

I’ve created a first version just using the issue type priority for now as theres only 3 priority levels but, probably over 30 individual issue types which will be quite a large formula.

IF(
  AND(
    {Incident Priority (from Query)} = "High",
    {Shopper High Priority Issues (From Shoppers)} >= {Incidents Before Follow Up (from Issue Type)}
  ),
      "Follow Up Required - High",
IF(
  AND(
    {Incident Priority (from Query)} = "Medium",
    {Shopper Medium Priority Issues (From Shoppers)} >= {Incidents Before Follow Up (from Issue Type)}
  ),
      "Follow Up Required - Medium",
IF(
  AND(
    {Incident Priority (from Query)} = "Low",
    {Shopper Low Priority Issues (From Shoppers)} >= {Incidents Before Follow Up (from Issue Type)}
  ),
      "Follow Up Required - Low",
IF(
      {Incident Priority (from Query)} = "None"
  ,
     "No Follow Up - No Priority",
"No Follow Up"
)
)
)
)

[TheTimeSavingCo]

Hmm…that seems like a lot of work. If you’re still interested to refine this further could you send me a screenshot of the example data and how you’d want the example data to be displayed?

[Oli_Reynoldson]

Hi Adam,

I’d still be keen to try and find a better work around for this if its possible.

Just to recap the flow I’m trying to create.

a) All new issues are logged via a form and enter into the issues log as individual records.

b) The different issues logged have different criteria which trigger an action. In the case of medium priority issues such as “poor substitution” if there are 3 incidents for a specific shopper for this issue type an action would need to be taken.

c) In the issue log what I’d like to do is have the following logic. When a specific issue is logged, for a specific shopper pull through the number of times the shopper has been flagged for the issue type and compare it to the threshold for the number of times it can happen before an action is taken.

I’ve been able to partly do this using a roll up on the priority of the issue and keeping all the criteria the same. It seems the only way this could be done is with if statements for each of the individual issue types.

Appreciate the help to take a look at it!

[TheTimeSavingCo]

Hmm, okay, so apologies in advance if I’m understanding this wrong

I’ve updated the original base to what I…think you might be looking for? I think this might be slightly cleaner than your set up, but the outcome’s going to be exactly the same, really

First we’ll compile the number of each issue type for each Shopper in the Shoppers table with a conditional rollup:

Then we set the # Incidents before follow up value in the Issue Type table via a SWITCH() formula

We then pull all of the values mentioned above into the Issue Log

And then we use a formula field with SWITCH() to isolate the number of incidents of that issue type priority that the linked shopper has committed (The # offences by shopper of this priority field)

And finally we use the Follow up needed? field to determine whether follow up is needed by comparing the Max Allowed value for that Issue Type priority to the Shopper’s specific count of offences of that priority

Happy to answer any questions