dynamic valuelists question

[shiraz]

Hi there,

Glad to say I'm back on my Dataface project after a 6-month hiatus.


My first question of the year:

Is it possible to populate a dynamic valuelist using a SQL query that which includes a variable, that variable being a field in the current record's parent record?

The example:

The valuelist query is currently like so:

__sql__ = "SELECT Course_Code FROM CourseEvent ORDER BY Course_Code"

But I only want the drop-down to offer courses in which the student is currently enrolled, using a query like such:

__sql__ = "SELECT CourseEvent.Course_Code FROM CourseEvent WHERE CourseEventID IN (SELECT CourseEventID FROM Course_Attendees WHERE MasterID='$MasterID')"

So $masterid is the variable in question.


One approach I've considered, perhaps not the most elegant:

I found the elseif clause in table.php which handles the valuelist queries. Within this routine, I could do a substring replacement on $value of $MasterID with the value of the masterid for the parent record. The funny problem I'm having here is how do I simply retrieve the parent record? Or for that matter, the current record? The API documentation as it stands now only describes how to retrieve the related records, so I'm at a loss.


Hope that was clear.


Many thanks,

Shiraz

[shannah]

This can be done in the delegate class. You have the ability to define valuelists in the delegate class by defining methods named "valuelist__%nameofvaluelist%()" that returns an associative array keyed on the valuelist keys and with values being the valuelist values.

For example, here is a simple valuelist showing students:

Code: Select all

function valuelist__students(){
    $res = mysql_query("select studentid, name from students");
    $out = array();
    while ( $row = mysql_fetch_assoc($res) ) $out[$row['studentid']] = $row['name'];
    return $out;
}


The benefit of defining it in the delegate class is that you can then access information from the environment to decide which values to return as part of the list.

for example, suppose we want to have a valuelist for courses, but we want undergraduate students to see only undergraduate courses and graduate students to see only graduate courses.

Code: Select all

function valuelist__courses(){
   
    // Caveat:  The valuelist is only calculated once per request, so in list view
    // or views where more than one record will be displayed, we should return all
    // of the courses, or we will get some blank values.
   
    // Let's get some information about the current request to
    // see which action is being performed
    $app =& Dataface_Application::getInstance();
    $query =& $app->getQuery();
   
    // Set default SQL query to be the list of all courses:
    $sql = "select courseid, coursename from courses";

    // See which action is being performed.  We only want special cases
    // for the edit and new actions
    $action = $query['-action'];
    switch ($action){
        case 'edit':
        case 'new':
           
            // Check to see if the current user record is an undergrad
            // or grad student so we load the current record
            $userrecord =& $app->getRecord();
            if ( $userrecord){
                if ($userrecord->getValue('status') == 'undergrad'){
                    $sql = "select courseid, coursename from courses where `type`='undergrad'";
                } else {
                    $sql = "select courseid, coursename from courses where `type`='grad'";
                }
            }
            break;
    }
   
    $res = mysql_query($sql);
    $out = array();
    while ($row = mysql_fetch_assoc($res) ) $out[$row['courseid']] = $row['coursename'];
    return $out;
}


Hope this helps a little

-Steve

[shiraz]

Wow, this is great!

I haven't been able to get it working though. I tried testing it on my Company table, creating a Company.php file in the Company folder as such:

(sorry I don't know the tag to enclose code with this forum...?)


class tables_Company {
function valuelist__cats(){



// Caveat: The valuelist is only calculated once per request, so in list view

// or views where more than one record will be displayed, we should return all

// of the courses, or we will get some blank values.



// Let's get some information about the current request to

// see which action is being performed

$app =& Dataface_Application::getInstance();

$query =& $app->getQuery();



// Set default SQL query to be the list of all courses:



$myCats["Me"]="Merry";
$myCats["Pi"]="Pippin";
$myCats["He"]="Hergie";

#while ($row = mysql_fetch_assoc($res) ) $out[$row['courseid']] = $row['coursename'];

return $myCats;

}

}
?>


So you can see I made it really simple. Shouldn't I now be getting a valuelist called cats when entering a new record into the Company table? Because I can't see anything anywhere in that table.


I did try looking for documentation on this feature so as not bother you anymore but couldn't find it. Perhaps you can point me to it if it's around.


Thank you,

Shiraz

[shannah]

Yes.. that is correct. there should be a valuelist named 'cats'. To use it you would need to specify one of your fields to use it in your fields.ini file.

e.g.

[cat_type]
widget:type=select
vocabulary=cats

-Steve

[shiraz]

ahhh, I was missing the fields.ini entry!

It worked like a charm!

This is really, really, elegant code you've got going here Steve!

[Paul]

Hi Steve,

This is a solution I was searching for.

I noticed that function valuelist__%nameofvaluelist%() is not included in the documentation for delegate_class

Perhaps it can be added for future reference.

Paul

[shannah]

Thanks for bringing this up Paul. There are actually dozens (maybe even hundreds) of hooks and config options that haven't been documented yet. I am currently working on a definitive manual using TeX that will endeavor to include all options. This manual will be released with the next version of Xataface. I'm shooting for May to finish this manual.

In the mean time, unfortunately you have to suffer with substandard documentation

-Steve