Value Lists - Showing Active Records Only

[jbullydawg]

I have a table and layout devoted to 'Courses' which has a Course ID, Course name, room number, etc. Also, courses are given a "Status" as either Active or Inactive.

I then have a 'Registrations' table and layout where I pull courses into via a value list. The value list is comprised only of the Course ID and Course Title...and it works fine.

I'd like the value list in the Registration layout to only display those courses which are Active. I tried doing this:

If (Status = "Active" ; CourseID ; " " )

along with this:

CourseTitle&" "&Section&", "&Program

The idea was to create a value list based on these two field calculations. Admittedly, I am not well versed in scripting. This was a good guess but it didn't work. I, obviously, have to have Course ID in the mix as it is the primary key and is the relationship with registrations.

Any ideas?

Jason

[jbullydawg]

Ok, I have found that one of my problems was trying to use two calculations in the value list. If I just use this

If (Status = "Active" ; CourseID ; " " )

and Course Title I get a full list of courses in my drop down. However, if I test to see whether or not an Inactive course makes the list I find that it does but without the CourseID. Only the Course Title shows up. So I've one part of the battle. Now, what do I put at the end of this statement instead of "" that will keep it from displaying at all?

If (Status = "Active" ; CourseID ; " " )

[mr_vodka]

You can use Case (Status = "Active"; CourseID ), then when defining your value list, reference this field as the primary instead.

[jbullydawg]

Excellent! Tha did the trick.

What I ended up doing was this:

Case ( Status = "Active"; CourseID)&" "&Case ( Status = "Active"; CourseTitle)

This assured that if Course ID and CourseTitle were Inactive then neither would show.

Thanks so much!