How to filter a list of values to remove duplicates?

[MteaM_Eilat]

Hi everybody,
Im stuck on this for about half a day already…
Not sure what is the best way to achieve my goal…
Any attemt for help is appreciated !

I have ORDERS table with the [ LOCATION NAME ] column which contains duplicates.

What is the best way to filter the duplicates away ?

Slice ?
Additional table for Locations ?
Virtual Column ?

Thanks in advance !

[Steve]

What do you want to happen to the rows with duplicate LOCATION NAME values?

[MteaM_Eilat]

Hi Steve,
Thanks for replying.

I want to get a view of all unique Location Names;
The specific view should IGNORE the duplicates for the specific view…

Hope i understood you

[Steve]

To get a list of the distinct values in the LOCATIONS column using an expression:

SELECT(ORDERS[LOCATION NAME], TRUE, TRUE)

The first TRUE matches all rows in the table; the second TRUE removes duplicates. See also SELECT().

An expression like the above would be particularly well suited for use as a column’s Suggested values expression.

[MteaM_Eilat]

Thanks Steve

It looks promising… Though Im not yet there…
In which column’s Suggested Values this expression should go ?
To a Virtual column in the Orders Table, or should I create LOCATONS table ?

[Steve]

That depends entirely on what you want to do with the information. What are you trying to accomplish with this list?

[MteaM_Eilat]

To have count of the locations, split them in to groups (order’s TYPE as an example an OFFICE or an APARTMENT or a GYM etc…),
See prices difference between the locations.
To be able to see Location’s Related Orders.
Related Customers and contacts…

[MteaM_Eilat]

I want to have a TABLE VIEW of the locations ,
like i have a TABLE VIEW of the Orders.

[Steve]

To get a table view of locations, you’ll need to create a locations worksheet in your spreadsheet and connect that worksheet to your app. Attempting to get a table view of locations with a slice of orders is likely to cause endless headaches.

[MteaM_Eilat]

Alright !
And after I create the Locations worksheet ,
How will I be able to move the relevant data from the ORDERS table (the Unique Location Names ) to the new worksheet ? and make it update as NEW locations is added ?
If it is even possible … ?

[Andrew_Smith1]

I’d like to do exactly this! I read @MteaM_Eilat 's comments with interest and also @Steve 's responses. But the last question remained unanswered.

I guess that ideally this new table would be being constructed in the background with an SQL type query within the database that updated a list of unique locations and put them in a locations worksheet. Is there a neat way to enable Appsheet to do this? Or are we pushing the technology beyond its comfort zone

[Steve]

MteaM_Eilat:

How will I be able to move the relevant data from the ORDERS table (the Unique Location Names ) to the new worksheet ?

This would be done outside the app, perhaps manually by someone editing the spreadsheet directly.

MteaM_Eilat:

and make it update as NEW locations is added ?

I envision this being a workflow rule that looks at adds and updates, compares the locations it find in those to known locations, and adds locations that aren’t already known.

[Rajesh_Sharma]

If any Solution to the issue in subject i.e

Extracting Unique Values from a Column using SLICE etc …

[Steve · Accepted solution]

Try something like:

([_THISROW] = MAXROW("Table", "_ROWNUMBER", ([_THISROW].[Column] = [Column])))

[Rajesh_Sharma]

Work like a magic and had a very sound sleep after that .

thank you…

[Tetsuia_Kohime]

I have a google sheet as database and use a google form to populate the sheet. People can send more form if they allow, In Appsheet I work this data changing status and (when i understand how) sending mail everytime status change. I don’t wont to prevent people to duplicate data in googlesheet, I want simply to create in menu view, using a slice a list with all duplicated line to check and work. I tried to use your example but it answer me it is not useful. what I need is to apply in slice a formula tha list all duplicated line. Any suggestion? I’m not a software developer, I’m working in appsheet only from 2 weeks. thanks