I have a People table with a Roles field with "staff", "faculty" and "students" in it using checkboxes.

I can create value lists off of this table filtered by role. No problem. But I also have the field Status denoting Active Faculty.

When a constrain the relationship by two fields, it breaks.

Is it possible to do a value list filtered by both fields? Should I just write a script to do a find, then copy the table, and base the value list on that table?

Thanks!

-Jeff

So, a person can have many roles? Can they be active for one role, and inactive for another?

Edited July 24, 200817 yr by Guest

So, a person can have many roles? Can they be active for one role, and inactive for another?

No... they can have a role, and they can be active or not.

The problem is, once I add the second constraint on the relationship, it breaks.

I'm thinking the way around this is that if someone is inactive, they simply have NO role.

But still, I figure there should be a way around this.

-Jeff

That's much easier. I just cobbled thru a demo where people can have many roles and have separate statuses (?) for each.

Let me change it to your much more simple reqs. Hang on..

Take a look at this. I just did Faculty.

Roles2.zip

Take a look at this. I just did Faculty.

Thanks so much! I will examine it in detail and get back to you.

Well dang! What you did works!

I had the tables backwards (I had an occurrence of the global table for each value list combo) and I've fixed that but things still aren't working. I still get "Index missing". I'm still looking for things I've missed.

But... in Table A, why do you have these non-global fields:

_kP_TableA_ID

_kF_Faculty_ID

_kF_Student_ID

_KF_Staff_ID

They don't seem to do anything, but I'm wondering if I'm missing something here.

Thanks again!

-Jeff

Well, what I was missing is what are you doing with these values lists? I assume you have another table that your are assigning these people IDs. I made up a table, "TableA".

In TableA, your ultimate goal is to store the personID for each of the roles. That's what the foreign keys "_kF..." are.

Maybe if you describe the tables that you have, we can stop working in the hypothetical.

As for , that could be an indication that you are trying to include an unstored calc field in a value list. Since an unstored calc cannot be indexed, it cannot be used in a value list.

Why not complete the demo file? Try to create the value list for Staff.