Best way to loop through a list on one table to update a list of things on another?

HI all

I am trying to work out the best way to add to a list of things based on the value of a filed in another table.

Example - a bit simplified to what I have, but is what I am trying to do.

“Awards” Type include the following fields:

  • Award_name (text)
  • Points_required (number)

I have a “User_points” Type with the following Fields

  • Awards (list of Awards)
  • Total_points (number)
  • User (User)

So the trigger even can be a page load or button clicked.

What I am trying to do is say on a particular trigger (let’s say page load) is:

Based on the Total_points in the “User_points” table update the Awards list if the award does not exists.

Example:

“Awards” Type records

Award_name: Platinum
Points_required: 50

Award_name: Gold
Points_required: 30

Award_name: Silver
Points_required: 20

Award_name Bronze
Points_required: 10

“User_points” Type

Awards: Bronze,
Total_points: 40

In this use case, the user has already earned the Bronze award, so I need to check the “Awards” Type (Table) to see what else the 40 points is greater than and update the “User_points” Type Awards records with those awards.

In the above example the updated record after the trigger would look like this:

  • Awards: Bronze, Silver, Gold
  • Total_points: 40

So my question is what is the best way to run this with the lowest overhead and performance.

Thank You!

The lowest overhead would be to not do the creation of “awards” like this - and just display them when you need to.

So a repeating group of awards, where points < current points.

Unless you need some extra information on the award-user, then it will be simpler to do it when needed, particularly as then you can re-jig all your awards on the fly and not have to delete and re-create should the points change in the future.

The next option is the update when the points change. You can create a custom workflow to do this if you needed.

Looping through on every page load seems to be quite a lengthy process.

You CAN do it, you would schedule a workflow on a list, that list being the Awards that have < points than the user’s points. And then the workflow would add the award if does not exist.

But it isn’t really the best solution.

Thanks, @NigelG I made the repeating group dynamic based on the rewards point as suggested.

Makes perfect sense and was overthinking it.

Cheers!

This topic was automatically closed after 70 days. New replies are no longer allowed.