join table and select box

Archived from the Xataface Users forum.

duggoff — Mon Apr 16, 2007 2:54 pm

First of all, thanks for this tool. I’ve been able to get my database set up and configured in record time (sorry…bad pun).

I have a table for courses, a table for instructors, and a join table so that one course can have multiple instructors and one instructor can teach multiple courses. My instructor table has fields for title (mr, mrs, miss, etc), firstname and lastname. Presently, when I am creating a course, I can click on the instructor tab and choose an existing instructor record using a select dropdown list. But the list only contains the title value from the existing instructor records. How can I populate the select with concatenated strings containing title, firstname, and lastname for all existing instructor records?

Thanks,

Doug Gough

shannah — Mon Apr 16, 2007 3:44 pm

Hi Doug,

This is where the delegate class can come in handy.Ê Simply implement the titleColumn() method to return an SQL column name of the column which should be treated as the title for your records.Ê (this would be the delegate class for the Instructors table).

e.g.

function titleColumn(){

ÊÊÊ return “CONCAT(lastname,’, ‘,firstname)”;

}

Hope this helps.

Best regards

Steve

duggoff — Mon Apr 16, 2007 4:01 pm

Works perfectly. Thanks.

Doug Gough

chapin — Mon Nov 03, 2008 7:09 am

shannah wrote:<p>Hi Doug,</p><p>This is where the delegate class can come in handy.� Simply implement the titleColumn() method to return an SQL column name of the column which should be treated as the title for your records.� (this would be the delegate class for the Instructors table).</p>

e.g.

function titleColumn(){

��� return "CONCAT(lastname,', ',firstname)";

}

Hope this helps.

Best regards

Steve

OK, it works perfectly! But I would like to sort the fields, I want them sorted by id, but when I open the select list they appear like this:

01

010

0100

01000

01001

01002

…

but I want them like this:

1

2

3

4

…

How do I proceed?

Thanks i advance!

shannah — Mon Nov 03, 2008 7:27 am

The select list will be in whatever order your current found set is in. You can sort your found set by clicking the heading on the columns that you wish to sort on, in list view.

-Steve

chapin — Mon Nov 03, 2008 7:43 am

shannah wrote:The select list will be in whatever order your current found set is in. You can sort your found set by clicking the heading on the columns that you wish to sort on, in list view.

-Steve

Hello! Thank you for the fast answer!

I think you missunderstood me. I mean when I want to “Add an existing record” to the table and I open the select list, then the records are sorted the way I mentioned.

Thanks!

shannah — Wed Nov 05, 2008 8:34 am

You can define a vocabulary for this field as follows.

  1. Define a valuelist that represents the options you want to have for adding related records.

  2. In the relationship definition (in the relationships.ini file) add the following

Code: Select all
vocabulary:existing=XXXX

where XXXX is the name of your valuelist.

And the key here was that you are able to sort records however you want in your valuelist.

-Steve