I am developing a Professional Development database for our school district. I have several tables with one being our STAFF (our existing staff names, positions, etc). Another being COURSES begin offered for staff to take.

 

The COURSES table has a STATUS field than is either Open or Closed. If Open, staff can sign up.

 

I would like a Portal to display the Courses Open within the Staff table, but because the Status is a calculation table, the Portal will not work.

 

So I was hoping to create a Script using ExecuteSQL to build a Virtual List to display the results in the Portal.

 

Problem is, I am new to ExecuteSQL and Virtual Lists, so I am in need of some assistance. Been looking at the web for some learning techniques, but haven't found anything yet.

I am partially following you. You should be able to use portal filtering to only display records with an open status. I dont think you need to use ExecuteSQL to achieve what you are doing based on what you have stated.

 

I am going to assume that the calculation is based off of some kind of enrollment limit being achieved per the course?

The Status field is based on several things. Either Date Deadline reached and/or Enrollment Cap reached.

 

If I try to make a relationship Staff::Status = Courses:Status, the relationship does nothing since the Courses:Status is a Calculation field. The relationship isn't valid and the Portal displays nothing.

 

I guess I could try a constant to constant relationship and then filter by Status.…

Please post your file. We need to see the graph and calc.