Display list in Portal using ExecuteSQL

[Bryan VonDeylen]

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.

[mr_vodka]

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?

[Bryan VonDeylen]

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.…

[mr_vodka]

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