Page 1 of 1

can we have more than 1 column field in the lookup window?

PostPosted: Thu Feb 25, 2010 8:44 am
by fabiofromfrance
Hi,
I am using widget:type = lookup with widget:table = address where I want to select an address for a new contact. It works great but the only issue I have it that it only display the field addresse1 in the widget and not the entire table fields such as city, zip code and country ...

Is there a way to say to the lookup widget to CONCAT field from the table it is looking at?

Thankyou
Fabrice
my setup
table = address
Code: Select all
address1 (varchar 64)
city (varchar 32)
zip code (varchar 16)
country (varchar 32)


fields.ini (from contact)
Code: Select all
[adr_id]
widget:type = lookup
widget:table = address

Re: can we have more than 1 column field in the lookup window?

PostPosted: Thu Feb 25, 2010 5:43 pm
by shannah
This is strange. It should show all fields from the table. Do you have permissions on the table that might prevent access to some of these fields?

Re: can we have more than 1 column field in the lookup window?

PostPosted: Fri Feb 26, 2010 2:24 am
by fabiofromfrance
Steve,
I don't have permission set for my table and I do logging as Admin role so I can see everthing. So I don't know what the code does but let me clarify my issue

In the contact table I have a field called adr_id where I want to put the preferred addresse for the given contact. I have tried many things (as you know) and so far find the lookup tool very good. so I have put in my fields.ini (under tables/contact) the code to have lookup widget. Now when I edit or add new contact I see the adresse field with a lookup button (all ok so far).Once I click on the button I have a nice window which display a search field, a select field an add new button and a cancel and select button. The issue I have is that in the select field of that widget I only see the address1 field, nothing else. I don't see the city, zip code ... Do I need to specify it and if so where and how?
my address table has got 5 fields (address_id, address1, city, zipcode and country)

I believe that in the code so far we only specify the table for the lookup i.e widget:table = tablename . Is there a way to also indicate the column of that tablename to be display in the select window of the lookup widget?

Thank you
Fabrice,

Re: can we have more than 1 column field in the lookup window?

PostPosted: Fri Feb 26, 2010 8:27 am
by shannah
Oh, I see. The lookup widget displays the titles of records in its list view. You can set the title for a record using the getTitle() delegate class.
e.g.
Code: Select all
function getTitle(&$record){
    return $record->val("addr1").' '.$record->val('addr2').', '.$record->val('city'). .... etc....
}

Re: can we have more than 1 column field in the lookup window?

PostPosted: Mon Mar 08, 2010 5:00 am
by fabiofromfrance
It works like a charm ... :D thank you

Re: can we have more than 1 column field in the lookup window?

PostPosted: Tue Jul 06, 2010 11:12 am
by jvinolas
Hi,

I have solved my problem with showing more than one column in my lookup widget. But... when I use the search box, it only searches in certain fields, not the ones that I'm showing. I mean, if I have this fields:

ID mediumint(9) No None auto_increment
SOCI mediumint(9) No None
NOM varchar(40) utf8_general_ci No None
COGNOMS varchar(40) utf8_general_ci No None
COMPTEI varchar(14) utf8_general_ci Sí NULL


And I'm using this getTitle (that it is working well to show the fields):

Code: Select all
function getTitle(&$record){
   return $record->val('SOCI')." - ".$record->val('COGNOMS').", ".$record->val('NOM');
}


Why it doesn't find, for example, number 101 in SOCI field and it is finding it in COMPTE (that it has 934010123 number inside).

So, in the end, what I feel is that the search is getting somewhat 'aleatory' fields on search. Does anyone know what can be causing this extrange behaviour? Or how to define the search fields?

Thanks.

Re: can we have more than 1 column field in the lookup window?

PostPosted: Tue Jul 06, 2010 11:27 am
by shannah
I think the search only searches varchar and text columns. You're hoping for it to search an int column.
If you create a grafted field with a concatenation of what you want to be able to search it should find it.

e.g.
in the fields.ini file:
Code: Select all
__sql__ = "select t.*, concat(SOCI,' ',NOM) as number_and_name from mytable t"

[number_and_name]
    visibility:list=hidden
    visibility:browse=hidden


Re: can we have more than 1 column field in the lookup window?

PostPosted: Tue Jul 06, 2010 12:20 pm
by jvinolas
Now it is not showing anything. This is my new sql as you said:

Code: Select all
__sql__ = "select oficina.*, contact(SOCI,' ',NOM) as number_and_name from socis oficina"


What I don't understand is how is going to get all records from table 'socis' if the relation I have in relationship.ini in socis is:

Code: Select all
[oficina]
oficina.SOCI = "$SOCI"


My table oficina uses lookup widget to search in table socis, and I can't realize how the sql is going to get all the oficina records and all the socis records to search inside.

I also disabled my oficina.php, trying to get raw table results, but nothing shown.

If you need more info about, just ask for it and I will post it.

Thanks a lot. ;-)

Re: can we have more than 1 column field in the lookup window?

PostPosted: Wed Jul 28, 2010 1:21 am
by jvinolas
I solved the problem not showing anything... it was a problem in the sql statement. This is now my sql:
__sql__ = "select oficina.*,ID as COMENTARIS, concat(socis.SOCI,' ',socis.NOM) as number_and_name from socis, oficina"


But the main problem still remains: The lookup widget is not searching in number_and_name field. I'm not sure the sql statement is getting all records from socis, but what it is true is that lookup widget is not searching in number_and_name.

Am I missing something?

Re: can we have more than 1 column field in the lookup window?

PostPosted: Wed Jul 28, 2010 8:49 am
by shannah
That SQL statement should not be used in __sql__. You are using an implicit outer join. You should only use left joins to ensure that your custom set contains the same rows as the original set.

Re: can we have more than 1 column field in the lookup window?

PostPosted: Sun Aug 21, 2011 8:35 pm
by ADobkin
Digging up an older thread, but I recently had a similar issue and found the solution for it. I added a new "Additional Tips" section to the lookup widget page in the Xataface wiki. Hopefully, these tips will be of use to others using the lookup widget:

http://xataface.com/wiki/lookup

In order to customize the display of the lookup widget's select list, you must edit the delegate class for the table which is referenced by the widget:table directive. There are two important points to note:
1. The items in the selection list are formatted based on the getTitle(&$record) delegate class function if it is defined. However, ...
2. The Search box will search on text based on the titleColumn() delegate class function if it is defined.

Therefore, if you have details displayed in the selection list from the getTitle function that are not in the titleColumn function, the search will ignore that part. It is best if they are set to display the same data to keep these functions consistent.