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

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

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

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?

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!

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