How to filter a list of values to remove duplicates?

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 !

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

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

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.

1 Like

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 ?

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

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…

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

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

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 … ?

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

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

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.

If any Solution to the issue in subject i.e

Extracting Unique Values from a Column using SLICE etc …

Try something like:

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

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

thank you…

1 Like