How do I add unique values only?

[Jennifer_Cook]

I currently have two tables that I am working with. In one table, I have the option to add them to the mailing list. When the option is checked, they are automatically copied to the second table(mailing list table). The problem I am running into, is that I only want to copy unique addresses. So, If I have two members that live at the same address, they are only sent one letter. There are too many members in the dataset to just make sure to only check one name per household, the user cannot keep track of it. Is there any sort of filter or data action I can add that will create the mailing list sheet with only unique addresses?

[Craig_QREW]

Yes, you can use a select function to either get a slice or a virtual column. (Slice is my reccomendation) and the optional third parameter asks if you would like unique values only so you can set that to true. An example could be SELECT(mailing list[address],TRUE,TRUE) The expression format is SELECT(table[column], condition, unique values only?)

This should resolve the problem of repeating addresses. Hope that helps, please let me know if not and I can further look into this. 

Craig

QREW Technologies 

[Jennifer_Cook]

Hi there, 

First off, thank you so much for taking the time to reply! I tried to create the slice and in the expression I used what you gave above, Select(mailing list[address], true, true)  , but I am receiving an error that "The expression is valid but its result type 'List' is not one of the expected types: Yes/No"

[SkrOYC]

Take a look at UNIQUE()
It takes just the unique values from a certain list

https://help.appsheet.com/en/articles/3416532-unique

UNIQUE(LIST(1, 1, 2, 1, 3)) : 1, 2, 3
UNIQUE(LIST("Mary", "David", "Joe", "David")) : Mary, David, Joe

[Jennifer_Cook]

Hi,

 Thank you for taking the time to reply. I tried using UNIQUE() and I am getting an error. "UNIQUE has invalid input". My function is  UNIQUE([ADDRESS])=TRUE

[SkrOYC]

You are mixing lot of things to be fair.

UNIQUE() takes a list and removes the duplicates to return a list that has unique values. So if the column [ADDRESS] is not a list (it seems it shouldn't), it doesn't make sense to use UNIQUE() with it. Also I get why you though that the expression that you made could make sense to you but I strongly suggest you to read the documentation.

Get the essentials right first.

Now, to the problem you are having, if you describe where the expression is and what you need we might tell you what to do on a better way.

Where (on your app) are you generating the "mailing list" from the Addresses the users have added? Also, how are you "automatically" coping the address to your table "Mailing list"?

[Jennifer_Cook]

Thank you for taking the time to explain why it wasn't working.

I have a bot that is copying a few columns from my members table when a user selects the mailing option. Sometimes, we have multiple members from one household and we only want to send that household one letter. 

[SkrOYC · Accepted solution]

I think that you should add a condition to that bot to only add the address if it's not already on the table.

Something like:

NOT(
  IN(
    [ADDRESS],
    MAILINGLIST[ADDRESS]
  )
)

You need to adapt it to your actual table and column names. Also, if the list is made once a week, month, etc, you also need to change the expression accordingly

[Jennifer_Cook]

Thank you for your help! This worked perfectly. 

[DroidTeo]

@Jennifer_Cook I think it is better for all of us and I think is nicer for @SkrOYC if you accept his answer as solution.. (if it works as you expect of course)
Other people can find the solution quicker 🙂 

[Craig_QREW]

Hi, you need to make sure that the addresses are in the type list. The easiest way to do that is to do the following. 

UNIQUE(tablename[Address]) 

When you write table[column] AppSheet automatically sees that as a list. So you have a list as your parameter, it will give you a list as the output and you should not need the "=TRUE" portion of this. I hope that fixes your issue. Let us know if we can be of more help. 

Craig

QREW Technologies