Avoid duplicate records on insert

[mercmobily2]

I have a People table with just the fields: ID | Name 

I also have an ActivitiesEnrolments table with the fields: ID | Activity ID | Person ID

Finally, I have Activities with: ID | Name

Note that People has a column called "Related ActivitiesEnrolments" with the usual REF_ROWS("ActivitiesEnrolments", "Person ID")

All I want to do is make sure that ActivitiesEnrolments is NOT added the same Activity. So, when adding a new enrolment, I want the select box for the activity NOT to display the activities the user is already enrolled in.

My idea was to edit the column Activity ID in ActivityEnrolments, and have this in Valid If:

ISBLANK(
  SELECT(
    [Person ID].[Related ActivitiesEnrolments][_ROWNUMBER],
    [Activity ID] = [_THISROW].[Activity ID]
  )
)

However, I am not having much luck. I want to keep performance right. So, I want to select the ActivitiesEnrolments for that user, and check if the activity is already attached to that user.

But... what am I doing wrong?

ISBLANK(
SELECT(
[Person ID].[Related ActivitiesEnrolments][_rownumber],
[Activity ID] = [_THISROW].[Activity ID]
)
)

[Marc_Dillon]

I want the select box for the activity NOT to display the activities the user is already enrolled in

---

Valid_if can be a Yes/No value, or a List value, these are 2 different types of functionality here. An ISBLANK expression returns Yes/No, What you want instead is a List, specifically a List of activities that haven't been selected yet. I recommend using "List Subtraction", taking the full list, an subtracting the list of already-selected items.

https://help.appsheet.com/en/articles/4575802-list-subtraction

So, something like: table[col] - SELECT( table[col] , xxx )

-------------------

SELECT(
    [Person ID].[Related ActivitiesEnrolments][_ROWNUMBER],

---

Additionally, you have 2 mistakes here. First, the 1st parameter of SELECT can only be in 2 possible formats: table[col] or [Related...][col]. Second, you also can't chain a single dereference into a list dereference like that, anywhere in the app.

[mercmobily2]

Alright I tried this, but it's not filtering anything out...

Activities[ID] - SELECT(
ActivitiesEnrolments[Activity ID],
AND(
[_THISROW].[Person ID] = [Person ID],
[_THISROW].[Activity ID] = [Activity ID]
)
)

My thinking: the full list is the full list of Activities[ID] minus the list of Activity IDs in the ActiveEnrolments table (hence the ActivitiesEnrolments[Activity ID]) where the Person Id and the Activity Id match.

Not quite?

(thanks for your help!)

Merc.

[Marc_Dillon]

Remove the [Activity ID] equality portion. Otherwise, yes that looks correct.

[mercmobily2]

Oh this?

Activities[ID] - SELECT(
ActivitiesEnrolments[Activity ID],
[_THISROW].[Person ID] = [Person ID]
)

It doesn't seem to filter anything out...

[mercmobily2]

Wait wait something odd... the "forbidden" items are in the select, BUT they if I pick one of them, the select stays empty. However, if I pick a "non forbidden" one, it works. So it's "kind of" working...?

[mercmobily2]

Update: I marked the fields as "required" and they now seem to be filtered!

[Marc_Dillon]

Do you have anything in Suggested Values? Or do you have the "allow other values" and/or "auto-complete other values" options selected?

I don't see why making the column required would fix any of this.