2. PHPRunner General questions
  3. Using lookup wizard to concatenate multiple fields
This topic is locked

Using lookup wizard to concatenate multiple fields

 		9/18/2009 11:49:51 AM
PHPRunner General questions
M
 mlcprs author

Hi,

The lookup wizard / lookup table feature is great for making a meaningless INT key meaningful, but I have a situation where a record can't be described by a single field, so I need to concatenate 2 fields to generate a description, one of which is a foreign key to another table; the other is a straightforward date field. What's the best way to go about dealing with this?

In other words, I'm in the Editor for Table A. It has a field which is a foreign key to Table B, which is the situation handled by the lookup table wizard, provided Table B has a suitable "name" field to use as the display field. However, Table B has no single field that does the job, and is best described by a combination of one date field plus the name field in Table C, whose key is in Table B.

I can't create a dummy field in the "Query/SQL" section to use in the lookup, because the Wizard uses only the actual table fields and isn't affected by the Query/SQL code. I can't use the Wizard "custom expression" to access another table (as far as I can tell).

I can partly get round it by actually adding a further field to Table B which contains the concatenation I want, but I need this field to be added automatically e.g. by an event.

Could anyone please recommend a course of action?

Regards,

Peter
T
 		thesofa 9/21/2009

Hi, welcome to PHPR

if you send me a mysql dump of the tables and data with a description of what you are trying to do, I shall have a go for you.
J
 		Jane 9/21/2009

Peter,
I recommend you to use custom expression as display field on the "Edit as" settings dialog on theVisual Editor tab.

Here is a sample:

concat(FieldName1,' ',FieldName2)
M
 		mlcprs author 9/21/2009

Hi Jane,
Thanks very much for your reply, but I can't get this to work. It works OK if I want to concatenate actual fields from table B, but one of the fields I need is in table C, and only appears in B's list page etc due to the SQL query for Table B, which uses a left join on table C.
In other words I want
concat(FieldName1,' ',TableC.FieldName2)
If you could reply ASAP I'd be very grateful as my trial period runs out in 1 day. Thanks for your response to my other thread.
Another minor point - is there something wrong with the forum search facility? I seem to get loads of results that don't include the word(s) I specified. Also, is it possible to do a search on A B where both A and B must be found (as opposed to either A or <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=44213&image=1&table=forumreplies' class='bbc_emoticon' alt='B)' />?
Regards,
Peter
J
 		Jane 9/22/2009

Peter,
I see what you're saying.

You can't use view created in PHPRunner as lookup table on the "Edit as" settings dialog. As workaround create view in the database directly and then use this view as lookup table.

To create view in MySQL use this syntax:

create view `viewname` as

select ...


I recommend you to open a ticket at http://support.xlinesoft.com with urgent questions.
M
 		mlcprs author 9/22/2009

Thanks very much, that cracked it.
Peter