Distinct values from reference table & second reference based on first selection

AppSheet AppSheet Q&A
, ,
1

Hello everyone,

in my App, I am using three tables.

Table name Key
Cases case_id
Rentals rental_id
Vehicles vehicle_id

Table structure with columns

Cases

Column name case_id rental_id vehicle_id
Description Reference to Rentals Reference to Rentals

Rentals

Column name rental_id vehicle_id
Description Reference to Rentals
Can be multiple vehicles for each rental_id

Vehicles

Column name vehicle_id
Description

Note:
Each rental_id can have multiple lines with different vehicle_id and each vehicle_id can be used in multiple rental_id.

Questions

  1. In the “Cases” form, I want to see a distinct list of “rental_id”, even if there is the same “rental_id” in multiple lines. (column = rental_id)
    When I use type=Ref but create a Valid_id, I get distinct “rental_id”, but there is a warning sign after every individual “rental_id”.
    How can I do that?
  2. In the “Cases” form, I want to see a list of “vehicle_id”, which are available for the “rental_id” chosen before (in question 1). (column = vehicle_id)
    How can I do that?

I want to keep the columns type Ref if possible.

Thanks for your help in advance!

2

I’m confused by which of these columns are Virtual list columns and which are Ref columns. As you mention a many to many relationship. If they are Ref’s are they ENUM Refs?

3

@1minManager Thanks for your reply.

None of these columns are virtual lists. The columns are rental_id and vehicle_id are “Ref” in table “Cases” so they appear as a dropdown, when I create a new row in “Cases” table.

I hope that answers the question.