View only data based on a unique column value

Roger_Wareham · 06-25-2020 12:03 PM

OK lets try to explain this one.

I want to have a view only showing unique value base on one column.

The database has lots of location numbers but when the user submits a new review of that location the old review would then not show.

I need this happening so I can have historic data on the location and don’t want to just update the existing review.

Is this possible to do in a view?

Thanks

Steve

So only show the newest review for each location?

Roger_Wareham

Yes correct

Steve

Each review will need to have a unique ID, and that ID column must be the key column for the table containing the reviews. The review ID and the location ID must not be the same.

If you have a separate table for locations, you can add a virtual column to that table that will keep track of the latest review for each location, using an App formula expression like this:

ANY(
  ORDERBY(
    FILTER(
      "Reviews",
      ([_THISROW] = [Location])
    ),
    [Review Added],
      TRUE
  )
)

replacing Reviews with the name of the table containing the reviews, Location with the name of the column in the Reviews table that identifies the location being reviewed, and Review Added with the name of the column in the Reviews table that records the date (& hopefully time) the review was originally added. All together, ANY(ORDERBY(FILTER(...))) gets the latest review ow for the location.

Then, create a slice atop the reviews table. For the slice row filter expression:

([_THISROW] = [Location].[Latest Review])

replacing Location as in the ANY(ORDERBY(FILTER(...))) expression above, and Latest Review with the name of the virtual column added above. This expression will ensure the slice only includes the reviews that are the latest for their respective locations.

Finally, attach the slice to a view to present to the user.

See also:

Roger_Wareham

Steve,
I have only a date only format no time.

My main review in in the table called Technical Preparedness Review
The location table is called Location
The store location is called Shore Number
Latest Review is called Review Date ( only date format, no time)

I created a virtual column in the location table called Current Location
Type Ref
and formula as this
ANY(
ORDERBY(
FILTER(
“Technical Preparedness Review”,
([_THISROW] = [Shore Number])
),
[Review Date],
TRUE
)
)

Then created a slice named Current TPR Reviews
Source Table Technical Preparedness Review
Row filer condition ([_THISROW] = [Shore Number],[Current Locations])

Then Point a UX view to this slice.

The results it shows all reviews.

Can you help?

Thanks

Steve

Please post screenshots of the expression, the virtual column configuration, the slice configuration, and the view configuration.

Roger_Wareham

Thank you Steve.

I want to add a addition to this to also only show my reviews

Roger_Wareham

Here’s the Slice view

Here’s the virtual in the location table

Here’s the virtual which the app created in the Technical Preparedness Review table

Here’s the ux

Steve

Hmm! That all looks good to me! I suggest you engage support@appsheet.com so that someone can look at you app config to see what’s going on.

Roger_Wareham

Ok I will send them this tread so they can see what I am trying to do.
If you come back with a fix, I will update the topic.
Thanks