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.
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 your app. Attempting to get a table view of locations 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
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.
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])))
Work like a magic and had a very sound sleep after that .
thank you…
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