CountUnique() and Unique() formulas

MultiTech
Participant V

I’ve been asked to find a way to duplicate the CountUnique() function from Gsheets into a form in AppSheet… and it’s proving to be more difficult than I was hoping.


Consider this example:

I’ve got a form with 10 questions, each a number entry where people can enter any number they wish. At the end of the form, I need a count of each unique number entered by users; if they enter 10 unique numbers, I need a number result of 10, if they enter 4 unique, but the rest are duplicates - I need a number result of 4.

When you try and think about a formula to make this happen, it leads me to roads like this:

list(
[Column1],
if([Column2] = [Column1], “”, [Column2]),
if(in([Column3], list([Column1], [Column2])), “”, [Column3]),

etc… you see where this is going, for each column I have to create this conditional statement that looks at everything that has come before; this way each column only adds itself to the list if it’s value is not already present.

This could easily become a monster of a formula.

And this doesn’t take into consideration that now my list will have a blank (which I can remove), but in this example the column is a number - so I’m not sure if the blank would work, it might require a number in which case I’d have to come up with some Place_Holder number and hope no one enters that number.


Unless I’m overthinking things (happens a lot ), I can’t think of a more efficient way of accomplishing this - unless there was a way to reduce the list to unique values.

As always, thanks for considering!!

(And sorry if this is a duplicate, I tried looking but couldn’t find anything about this; only stuff about uniqueID)

PPS: Upon further thinking, the CountUnique could be removed from this request as I could just wrap a COUNT() around the unique() of my list.

Status Not Planned
2 23 2,430
23 Comments
Steve
Participant V

This?

COUNT(
  LIST(
    [Column1],
    [Column2],
    [Column3],
    ...
  )
  - LIST()
)

Any list subtraction has the side-effect of removing duplicates.

MultiTech
Participant V

You would think right!? lol

But it seems that sometimes a 3 isn’t the same as another 3 when done this way.

Steve
Participant V

HUH?!?!

MultiTech
Participant V

testing. I tried this before and ended up with duplicate items in the list.

This prompted me to look for an actual formula (like unique()) but found it’s not supported by appsheet, which led me to this post. lol

I’m trying it again now to see if things work.

Steve
Participant V

I agree that a UNIQUE() function would be nice.

MultiTech
Participant V

Epic. I just love it when things that didn’t work before, now do.

So I ran a test and sure enough it pulls out the unique values.

Thanks Steve!!!

MultiTech
Participant V

Hmmm… seems we have mixed results.

When testing, it works; when in practice it doesn’t. I’ve come accross a few formulas like this, where they work in the editor but in the app they don’t; Praveen talked about it’s because the two systems are actually that - two different systems (one the app editor, the other the app).

Made a quick video going over things:

2X_3_3a287f6dbf29ecf88fd487f75ce79dee9f818f36.png

Hmm… who to tag…

Steve
Participant V
1minManager
Participant V

Is there a reason why you can’t do the formula in Google Sheets and just have the user sync the App to get the result? Or have a workflow email it automatically?

1minManager
Participant V

Not sure if this is what you’re after:
2X_9_9eaae6a9242093e25d352614ee103277f9103ddc.png

MultiTech
Participant V

Thanks for the formula suggestion Simon, but no there’s two options here:

  1. I go with the list subtraction method proposed by Steve (which works, its just not evaluated correctly by the app at the moment so I’m stuck waiting for a bug fix); or
  2. I go with the brute force method where each successive column checks to see that it’s value is not already present in the previous ones.

And for why I can’t have this in the google sheet… well any spreadsheet formula is like sticking gum in the gears of your app. It might work when you’ve got 4 records, maybe even when you’ve got 40 - but once you get to 400 or 4,000 things start to really slow down.

If you’re building an app for longevity, then when possible I would advise anyone to migrate any spreadsheet formula into appsheet.

MultiTech
Participant V

I encountered the situation where a 3 isn’t the same as another 3. lol

Here’s the setup:

  • I’ve got 17 columns, each with a formula controlling the value (so there’s always a number there, with the default being 0)

I setup a formula where each column is contained inside it’s own LIST() expression and I’m adding all the columns together; this should have the result of discarding duplicates because if I have a 2 in the list already and I add another one, it’s already there.

But this isn’t working, it seems that the 2 from column1 is different from the 2 in column4.

Crazy.

— here’s the formula:
count(
(
list([hvlt01.1s]) + list([hvlt01.2s]) + list([hvlt01.3s]) + list([hvlt01.4s]) + list([hvlt01.5s]) + list([hvlt01.6s]) + list([hvlt01.7s]) + list([hvlt01.8s]) + list([hvlt01.9s]) + list([hvlt01.10s]) + list([hvlt01.11s]) + list([hvlt01.12s]) + list([hvlt01.13s]) + list([hvlt01.14s]) + list([hvlt01.15s]) + list([hvlt01.16s]) + list([hvlt01.17s])
)
- list(0, 99)
)

I should note that the list subtraction is working, but not the list addition (at least in the sense of discarding duplicates… but it IS adding each element to the list, so I GUESS it’s technically correct).

1minManager
Participant V

I’m not clear why option 2 is appropriate. Why do you need to check the status of past but not future answers?

MultiTech
Participant V

If I always have the answers looking backwards, then I can confidently know that any future answer won’t be a duplicate while also knowing I will always get one answer in the list.
2X_8_81b7b3a55608cc204b2eda86cd409bca6a52e393.png

Here’s the formula translation:

list(
[Column1],
if([Column2] = [Column1], “”, [Column2]),
if(in([Column3], list([Column1], [Column2])), “”, [Column3]),
if(in([Column4], list([Column1], [Column2], [Column3])), “”, [Column4]),
etc....) - list("")
1minManager
Participant V

So for future answers are you removing the previous duplicates from the list of allowable choices?