You could use an If then Else column to assign the relevant award to a student based on the number of books in another column. If you bring the list of possible awards into the User table via Single Value you can reference them in the If then Else.
As far as I know, you need to create an action to send the email. Not sure how to auto trigger the email based on a number without an action.
I assume they collect one book at a time, and they collect it by submitting a form. Probably you can use a rollup in the user profiles table to count the number of books they have collected, and an on-submit action on the form to send emails based on the rollup value.
Exactly. They collect it by submitting a form. I can do a roll up to count the number of books - that’s an easy part.
This is the logic when the email is supposed to be send. Every time they get a Title, an email has to be sent. I understand that I can do a conditional action on on-submit action of the form but what should be the condition based on the logic stated below:
When they get 10 books, they get 1st “Readers” title. When they get a total of 15 books, they get 1st “Treasurer” Title. Then the logic is repeated. When they get 10 more books, they get 2nd readers title. At this point they have 25 books: (10 + 5 + 10). Then when they get 5 more books, they get 2nd Treasurer Title.
Basically the emails logic is like: 10 + 5 + 10 + 5 + 10 + 5 something like this. Hope I am making sense
Since it’s not consistently every 5 books, MOD probably wouldn’t work well. Assuming you already have an IF column to determine the Title, I think what I would do is have a similar IF column that only returns ‘true’ whenever those exact counts are met. That ‘true’ value will be the trigger that tells the custom action that an email needs to be sent.
My only concern would be that when the form is submitted, the counts and IF columns wouldn’t recalculate quickly enough before the email gets sent. Not sure though. If that’s the case, then maybe you need an IF column that shows the NEXT available Title, and set the other IF column to true when the count is 9, 14, 24, etc. That way when the 10th book is submitted, ‘true’ will already be set and the next title will already be set, so the custom action will know to send an email and you you will already have the title you want to send in the email.
Hope that makes sense. I kind of do something similar. I have tests. A certain number of tests need to be passed before a student can get a certain classification. So for example, in the image below, this student has completed all individual tests to get their Bronze, Pre-Silver, and Silver classification. Each classification has multiple tests. Now they are working on their Pre-Gold classification, so they have to complete all tests needed to get that Pre-Gold classification. Once the final test for that classification is completed, then I have an action that will add a new Pre-Gold row, and based on counts, all individual tests will be hidden. I’m pretty sure I have it set up so I already know beforehand, that when the final test is completed, that action will trigger the extra Add Row. My setup is a bit more complicated, but seems very similar to what you want.
I had that in my mind. The thing is, this will limit me to a certain number of books. Let’s say if my user reaches 1000th book, how can I make that work? It would take me forever to set up a IF-ELSE column like that.
OK, so is the 10 + 5 + 10 + 5 pattern consistent then? There’s probably a way to do something mathematically. I’ll see if I can come up with something.
Here’s a math formula that should work. Basically any time the result of the math is zero, that means that the number fits your pattern. The if-FitsPattern column is just checking for zero in the mth-Pattern column and returns true if it’s zero.
Not sure if this helps, but I recently needed to do something similar, and the way I approached it was to lay everything out in a table and then dynamically calculate the signed in users level.
