Offer a subset of choices as ref

[mercmobily2]

Hello everybody,

I have the following tables:

People: ID | Name

Activities: ID | Name | Bookable

ActivitiesEnrolments: ID | Activity ID (ref to Activities) | Person ID (ref to People)

ActivitiesBookings:  ID | Person ID (ref to People) | Activity ID (ref to Activities)

When I add a new record in ActivitiesBookings, I would like to be able to only select an activity the user is enrolled in, rather than all activities. So, what I really want to see is a list of choices of records in ActivitiesEnrolments filtered by the user ID picked in the "Person ID" select just above.

It's frustrating because I know I've done this before... but it was months ago, and I have no idea how.

[Suvrutt_Gurjar]

If understanding of your requirement is correct, please try below

1. There should be a column called something like [Related ActivitiesEnrolments] in the table "People"

2.  Please create another VC in the table "People" called say [PersonActivities] with an expression something like [Related ActivitiesEnrolments][Activity ID]

3. In the valid_if of the [Activity ID] column in the "ActivitiesBookings" table , please have an expression something like

SPLIT([Person ID]. [PersonActivities],",")

This valid_if should restrict the person related activities for the person selected in [Person ID] in the "ActivitiesEnrolments"

Just as a side note, the main columns of the  "ActivitiesEnrolments" and "ActivitiesBookings" tables appear to be identical, so not exactly sure the difference between them.