Incrementing multiple columns based on Choices

[Joe_Gabriele]

Hello,

In my Pages app, I have an Inline list which opens up to a New Screen for that item.

In the New Screen I have 3 Choice components, each with 3 options of Dislike, Like and Love.

Ex:
Choice #1
Dislike Like Love

Choice #2
Dislike Like Love

Choice #3
Dislike Like Love

Each choice writes to their own USC.

What Id like to do is add 3 news columns which are incremented on based on the choice chosen.

Ex:
If user selects Dislike for Choice 1

Choice 1 - Dislike Choice 1 - Like Choice 1 - Love
1

I can currently do it via Custom Action with a ton of IF AND Combos (I believe I need 27 different combinations due to 3 questions and 3 answers) with various Increment Actions based on the Combo.

However, I am looking to see if there are any more creative options out there as building 27 different branches would take a lot of time!

I already tried using 3 buttons with an Increment Action but 1. The user can just keep pressing the button and 2. The button doesnt “highlight” if chosen so the state looks exactly the same if clicked”, so I dont think a button or Inline list would work for me.

[Darren_Murphy]

@Joe_Gabriele

The user can just keep pressing the button

I would think you could address this with conditional logic.

@Joe_Gabriele

The button doesnt “highlight” if chosen so the state looks exactly the same if clicked

You can conditionally change something about the list item to address this. A different image/colour/emoji/whatever.

I would think that an inline list/collection would be the way to go with this, but I’m struggling a bit to grasp your logic. In particular:

@Joe_Gabriele

What Id like to do is add 3 news columns which are incremented on based on the choice chosen.

Ex:
If user selects Dislike for Choice 1

Choice 1 - Dislike Choice 1 - Like Choice 1 - Love
1

I don’t get the above - can you elaborate on that a bit please?

Also - are you working with Pages or Apps?

[Joe_Gabriele]

Thanks for your response Darren

My Current Choice Components

My Current Custom Actions

My Table

Currently, if a User selects Dislike for the Taste Choice Component, it currently writes to a USC. But, I also have a Button that runs the above Custom Action, which only Increments Taste if Like or Loved is chosen. Same for Value and Quality.

What I’d rather have is if Dislike is chosen, it Increments my Dislike/Taste column. Same goes for if Liked or Loved is chosen, it Increments those respective column. This way, I know how many of each choices are being chosen.

However, I also have other choice components for Value and Quality as well. Therefore, it appears to me that I have to have numerous IF AND conditions in my Custom Action to ensure the correct columns are Incremented based on all the different combinations. Maybe I am overthinking it lol.

1 solution I thought of was to use a Button for each Choice Component to Increment the respective columns (and hiding the rest until the prior Choice is chosen). Only problem is that it now consumes 3 updates vs 1. I was hoping to avoid that.

[Darren_Murphy]

Okay, one thing that immediately jumps out at me is that you have 3 separate increment actions in each of your conditional branches, which is going to count as 3 updates.
Given that they are all operating on the same row in the same table, surely you could consolidate each of those into a single action?
That would significantly reduce the number of updates.

Give me some time to digest and think about the larger problem. I dare say we can help you simplify that quite a bit.

[Darren_Murphy]

A couple of questions:

• Are all users signed in to your App when they interact with these ratings?
• Do you have/need any controls in place to prevent users from submitting multiple ratings for the same dish?
• Once a user has rated a dish, are they allowed to come back later and change their rating?

[Joe_Gabriele]

Hows its currently setup (3 Increment actions), I actually tested this and it only resulted in only 1 update per submission!

This is a Pages project
Users are not signed in
Once they hit submit they cannot go back and update their choice

Once a user hits Submit, it updates a boolean to show “Reviewed” and I have a condition on the Show New Screen to only allow if Reviewed is not checked, preventing them from reviewing it multiple times in a single session.

You can get a feel of the user flow here:

ThisDish

Select the ThisDish Restaurant

[Darren_Murphy]

@Joe_Gabriele here is what I came up with.

Goal was to minimise the number of updates, whilst at the same time trying to keep the logic not overly complicated. At first I thought I might be able to achieve that with some clever math logic, however that didn’t work out so I ended up resorting to JavaScript.

The Rating Submit action uses a single Set Column Values action:

And each of the Taste/Value/Quality groups requires 3 additional columns:

Here’s a walkthrough of how I set it up:

[Joe_Gabriele]

This looks amazing and much simpler! Thank you!!

Let me try it out today and see if I get the desired results.

Do you mind posting the Javascript code to copy?

[Darren_Murphy]

Sure.

This is how it looks for Taste/Likes:

if (p1 === undefined) { p1 = 0 }
let inc = p2 === 1 ? 1 : 0;
return p1 + inc;

The 2nd line in the above needs to be modified for each of Loves & Dislikes:

for loves:

let inc = p2 === 2 ? 1 : 0;

and for Dislikes:

let inc = p2 === 3 ? 1 : 0;

And obviously the p1 and p2 values need to be adjusted for each column.

Let me know how it works out.

Side note to @Jeff_Hager
I initially thought I could use a Math column instead of the JavaScript column, but I couldn’t get my brain to function properly. The challenge is basically to return either a 1 or 0 based on the input value, which can be either 1, 2, or 3. And we need 3 columns:

• Column 1 returns 1 if input is 1, otherwise 0
• Column 2 returns 1 if input is 2, otherwise 0
• Column 3 returns 1 if input is 3, otherwise 0

I played around a bit with various combinations of mod(), ceiling(), trunc(), etc, but wasn’t able to land on something that actually worked. But I feel it should be doable. What do you think?

oh, I should add. The reason an if-then-else column isn’t suitable here is because we want to take the return value and add that to the pre-existing value (in the same formula). So whilst it would be simple to first do an if-then-else and follow that with a math column, the challenge is to come up with a single column solution

[Jeff_Hager]

Well…I think this works, but it returns negative zero instead of zero, which should still work though. Not exactly simple. There’s probably a better way, but I’d have to play some more.

Column 1

(ceiling((mod(1,input)+mod(input,1))
/
(1+mod(1,input)+mod(input,1)))
-1)
* -1

Column 2

(ceiling((mod(2,input)+mod(input,2))
/
(1+mod(2,input)+mod(input,2)))
-1)
* -1

Column 3

(ceiling((mod(3,input)+mod(input,3))
/
(1+mod(3,input)+mod(input,3)))
-1)
* -1

[Joe_Gabriele]

So I implemented it, but I get some inconsistent results when I review the same dish over and over again, almost like if it sees a value in the Taste/Likes column it doesnt add +1 to it.

Whats the return p1+ inc do? actually I see, it takes the existing value in Taste/Likes and Adds a 0 or 1 to it based on the if formula

Also, does this work if I clear the USC where 1,2,3 is recorded, after the survey is completed?

[Darren_Murphy]

@Joe_Gabriele

So I implemented it, but I get some inconsistent results when I review the same dish over and over again, almost like if it sees a value in the Taste/Likes column it doesnt add +1 to it.

Interesting, I wouldn’t expect that.
Were you testing over and over again as the same anonymous user?
And were you testing in the builder, or published App?
The most realistic test I think would be with the published App, just reloading the page should be enough to clear the user specific values (I believe), so you could test over and over again that way.
Sometimes with these sorts of things the behaviour in the builder can be a bit flakey, but it works fine in the published App.

@Joe_Gabriele

Whats the return p1+ inc do? actually I see, it takes the existing value in Taste/Likes and Adds a 0 or 1 to it based on the if formula

Yes, correct.

@Joe_Gabriele

Also, does this work if I clear the USC where 1,2,3 is recorded, after the survey is completed?

It should do, have you tried it?
But why bother with that if you’re only expecting a single set of ratings from each user? It’s an extra update that you don’t need to do.
Also, keep in mind that the default state for all users will be null values in those columns, so if that doesn’t work then the solution is broken

[Joe_Gabriele]

Ok, I tried it on the published App and looks like its working!

Thanks again for your assistance.

[Joe_Gabriele]

My last issue that I am trying to solve is this:

I record a date stamp in a USC each time a survey is submitted. What I’d like to do is increment a column so I can see how many surveys are submitted within a given month.

I could theoretically have a column for each Month and only increment that month if it matches the Survey’s Submission Date.

I have a feeling that your above formula or Jeff’s could be modified to solve this issue as well.

[Darren_Murphy]

hehe, I can think of a clever solution that involves the use of a bit more JavaScript and a single column containing a JSON object

I’m out at the moment, but I’ll have a fiddle with it later when I get home.

[Darren_Murphy]

@Joe_Gabriele

I could theoretically have a column for each Month and only increment that month if it matches the Survey’s Submission Date.

A better approach here is a row for each month in a separate table. That will make the collected data much easier to work with. You could use it in a collection, or display in a chart, etc.

The general idea would be a table with one row per year/month, build a single relation to that table using the current year/month, and then do an increment through that relation.

Here is how that could look:

• The first 3 columns use the Lookup + Single Value method to generate an auto-incrementing RowIndex.
• SeedDate : that should be a date somewhere in the middle of the first month that you want start collecting data. The exact date doesn’t matter, just somewhere around the middle of the month is fine. I used the Text to Date plugin column for that one.
• Date: this uses both the RowIndex and SeedDate to calculate a date for each row, incrementing by one month at a time. The formula for this one is a bit of a doozy, and comes from Jeff, not from me:

((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date)
-
MOD(
(DAY(((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
-
DAY((Date-DAY(Date)+15)+TRUNC(Months/12*365.25))
+
DAY(Date))-DAY(Date))
,DAY(Date))

• YearMonth: this uses a math formula to convert each date to an integer representing the year and month in YYYYMM format. This one is used to create the relation.

Year(Date) * 100
+ Month(Date)

• And then finally, Reviews is just a plain number type column for storing the number of reviews for each month.

In your Dishes table, two additional columns are required:

• A math column to calculate the YYYYMM value of the current month (using “Now”)
• A single relation that matches the above column with the same column in the Summary table

And then finally, you need an extra Increment action on submit that increments the Reviews total by 1 through that relation.

[Joe_Gabriele]

I guess I should have prefaced that these counts would be across multiple restaurants and not just 1. Would that impact the above solution?

[Darren_Murphy]

Well, that depends.

Do you want to break down the count of reviews by Restaurant, or do you just need a total count across all Restaurants per month? (or do you need both?)

If you need to breakdown by Restaurant, then yeah - that solution wont work at all.
We’d need to look at something else - probably the initial idea I had when I mentioned a “clever JSON/JavaScript solution”. This basically involves defining a JSON structure to hold the monthly review counts per Restaurant, storing that in a single column in your Restaurants table, and using JavaScript to manipulate it. Not a simple solution, but one that that works, and one that I have deployed in multiple Apps over the past 6 months or so.

Whilst it’s complicated, it’s nice because it doesn’t cost you any extra rows.

Anyway, before we head down that path, you need to answer the question above

[Joe_Gabriele]

Yes I would just need a rollup for each individual restaurant on how many reviews they received each month. Given that each review costs updates, I’m interested in putting caps on the # they can receive in a given month.

Also throwing around the idea of giving them unlimited surveys and just pricing the plan around the “average” if the above cant be implemented or costs too many new rows.

I initially had a dedicated survey table where all of this would be super easy, but Id chew through my 25k rows quite quickly. This way I really have zero row consumption, just updates that are scalable

[Darren_Murphy]

Okay - would you like to pursue the JSON/JavaScript option that I described?
If yes, let me know and I’ll provide some more detailed instructions for setting it up.