Problem with depselect (or lookup widget)

[Mike Wood]

I have a problem using depselect, or the lookup widget in the following circumstances:

1. Creating a new record

2. The key field for the lookup is a relationship field that is passed from the parent record.

To be specific, I have a table called 'applicant' which contains records for people. There are two other tables called 'grants'  and 'payment'. A grant is linked to an applicant using the a_id applicant id field. Similarly a payment is linked to an applicant using the same a_id applicant id field.

Finally, each payment is further associated with a grant through the g_id grant id field. All these relationships are set up and work well. No problems so far.

The problem comes in the 'New Payment' form. This form is already linked to the Applicant and has the a_id ($a_id) field set by that linkage. You then have to select the grant id to assign the payment to, this has to be a grant that is to the same applicant, I.e. the a_id fields must again be the same. I'm using the following depselect in the payment fields.ini:

[g_id]

widget:type=depselect

widget:table=grants

widget:filters:a_id='$a_id'

widget:keycol=g_id

widget:labelcol=g_description

widget:label="Grant Description"

visibility:list = hidden

visibility:browse = hidden

This should list all grants that are linked to that Applicant through the a_id field which is common to all tables by filtering the grants table on a_id = "$a_id"

This works as expected when editing an existing payment, however the select list is empty when creating a new payment. It appears that the filter isn't picking up the value of $a_id.

(I get a similar problem if I try and use the lookup widget, or even if I try and use a __sql__ command.)

Is there a problem with picking up the current value of the variable $a_id when it is the variable used to link two tables together (and is thus fixed and uneditable)? Perhaps it doesn't exist until after the record is saved?

Thanks

[Mike Wood]

I see that the relationship field is in the URL with an extra (encoded) = sign. I assume Xataface marks it like this so it knows about the link. Perhaps I can pick it up by accessing the Get variables in a delegate class routine?

[Mike Wood]

Yep - that worked! I gave up on using depselect and wrote my own routine in the table delegate class to create a valuelist vocabulary for select instead. Then parsed the appropriate GET variable to extract the contained a_id:

function valuelist__availablegrants(){

$my_recordid = $_REQUEST["-recordid"];

$pos = strpos($my_recordid, "a_id=") + 5;

$availablegrants = array();

if ($pos !== false) {

$my_a_id = substr($my_recordid,$pos);

$res = mysql_query("select g_id, g_description from grants where a_id = '$my_a_id'", df_db());

while ($row = mysql_fetch_row($res) ) $availablegrants[$row[0]] = $row[1];

return $availablegrants;

} else {

  return array();

[Steve Hannah]

This valuelist won't work properly in list view and other contexts where you expect to display the value of the field rather than the id.

Steve

--
Steve Hannah

Web Lite Solutions Corp.

[Mike Wood]

That's fine in this case. This field is hidden in list view, it's only in editing that it's needed.

What could be a useful addition to Xataface is making the relationship variable (as included in the -recordid parameter) available to forms. That way you could use either the depselect or lookup widgets.

Thanks!

Mike

[Mike Wood]

Steve,

It bugged me that the query wouldn't always work, even if I wasn't using it, so I amended it like this. Look better?

function valuelist__availablegrants(){

$app =& Dataface_Application::getInstance();

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

$availablegrants = array();

$my_a_id = "";

// Are we adding a new related record?

// If so, then find the a_id variable in the -recordid GET string and extract the value

    if ( $query['-action'] == 'new_related_record') {

$my_recordid = $_REQUEST["-recordid"];

$pos = strpos($my_recordid, "a_id=") + 5;

if ($pos !== false) {

$my_a_id = substr($my_recordid,$pos);

}

} else {

// Otherwise, get the a_id value by conventional means

$record = $app->getRecord();

$my_a_id = $record->strval('a_id');

}

// Search the grants table for records with matching a_id and return the result array