Slices across tables with refs - cannot get them to work

[kacoroski]

I have two tables - a table of cafes with name, address, etc. and a table of reviews that with the name of the cafe and metrics on the cafe. So far so good. One metric is what the cafe is best for which is a EnumList of {Hangout, Group Study, Solo Study, All}. What I want is a slice that contains all the Cafes with metric [Best For] = Hangout. Normally I would do a select distinct on the reviews table, but for the life of me I cannot figure out how to do it in appsheet. I have tried select, contains, etc. and the best I can get is all the reviews for the cafes that have [Best For] = Hangout. I do not want all the reviews, I just want the cafe name so I can put it into a form called Hangouts that will list all the cafes that are good for Hangout. Once I get this figured out I can do the other Best For values the same way.

Thanks in advance

ski

[shahappsheet]

It seems to me LOOKUP should work in this type of scenario as only one Field is expected. Another might be of ANY together with SELECT. It also return one field.

So far I could understand quickly.

[shahappsheet]

When I checked your requirement thoroughly, it seems to me, it is very simple. Create a slice filtering with [Best For] = Hangout. You can even make it dynamic/dependant. Then Just use the expresion <SliceName[Cafe]> of Valid If event of the targeted filed of the new form. There will be no repeated Cafes, only distinct ones are appeared. This is one of the solution even you dont want to use the Refs. It must work, so far I understand your problem.

You can even directly use the Reviews Table in the Valid If and concurrently use filter and order by. No need slice. On that case the Valid If expression should be as follows-

ORDERBY(FILTER(“Review”,([Best For]=“Hangout”)),[Cafe])

OR will be more dynamic, if you use the Best For Enum list Field name as follows-

ORDERBY(FILTER(“Review”,([Best For]=[_THISROW].[Best For])),[Cafe])

So many ways can be done. Yet I did not use the Refs.

Best of luck.

[Steve]

The approach I would use is to look for the first occurrence of a review for the cafe that indicates that cafe is good for the metric:

ISBLANK(
  FILTER(
    "Reviews",
    AND(
      ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER]),
      ([Name] = [_THISROW].[Name]),
      IN("Hangout", [Best For])
    )
  )
)