Unique dropdown on Ref Column

[Austin]

I have a list of jobs that I am referencing from an interview scheduling table. We might have more than one opening for the exact same job ie Full Time cashier at store 1. I need my drop down to only show me one result for FT cashier store 1. Since this is a reference column a unique select won’t work since the ID is different for the 2 jobs. My current solution uses a view in database to bring in only the oldest job record where X,Y, and Z column match. There are issues with this so I was hoping to just filter down my dropdown list in appsheet.

[tony1]

@Austin_Lambeth Are you using a ref column just for the dropdown, or do you actually need to maintain a reference from your interview scheduling app? If you just need the dropdown, you could put a formula like this in the column validation: SELECT(Jobs[Job Title], ..., TRUE). See SELECT() and valid_if.

If you actually need to maintain the reference, then what about having separate table for jobs vs. openings? Each job would have many openings. Then you could make your interview scheduling table reference the jobs table.

[Austin]

I need to maintain the reference as I am using that ID value elsewhere as well as using the label to make it easy to read. Changing to a job vs opening would require re-working the whole method of adding jobs. This is for a V2 of our current app so we have some portions in place already.

[Steve · Accepted solution]

The first Jobs row of each distinct Job Title column value:

FILTER(
  "Jobs",
  (
    [_ROWNUMBER]
    = MIN(
      SELECT(
        Jobs[_ROWNUMBER],
        ([_THISROW-1].[Job Title] = [Job Title])
      )
    )
  )
)

[Austin]

Would this work in a slice filter?

[Steve]

The equivalent slice filter would be:

(
  [_ROWNUMBER]
  = MIN(
    SELECT(
      Jobs[_ROWNUMBER],
      ([_THISROW].[Job Title] = [Job Title])
    )
  )
)

[Austin]

Awesome. I kinda understand what that formula is doing but would never have been able to find that on my own.

[Steve]

Does this row’s row number match the lowest row number of those rows with a job title that matches this row’s?

Find all rows with a job title that matches this row’s. Get the row numbers of those rows. Get the lowest of those row numbers. Does this row’s row number match that lowest row number?