2. PHPRunner General questions
  3. 2 Lookup Table Questions
This topic is locked

2 Lookup Table Questions

 		7/15/2008 6:39:50 AM
PHPRunner General questions
L
 Lisa2006 author

Hi All,
I have 2 questions relating to Lookup Tables
_Table Schema

_Table: journey
Table: country

Field: country
I have set the country field as a lookup table in journey_add with the following switch 'Allow to add new values on the fly'
Q1

How do i display the contents on the drop down list to be first letter uppercase, remaining lowercase.

i.e. something like $value = ucwords(strtolower($value));

England

Germany

Scotland
Q2

Assuming i have a country list as:

Belgium

France

Germany

Holland

Scotland

UK
How can i change this so that it will always display

UK

Scotland

... followed by the ascending order of countries entered.
Thank you in advance.
Lisa
T
 		thesofa 7/15/2008

Hi All,

I have 2 questions relating to Lookup Tables
_Table Schema

_Table: journey
Table: country

Field: country
I have set the country field as a lookup table in journey_add with the following switch 'Allow to add new values on the fly'
Q1

How do i display the contents on the drop down list to be first letter uppercase, remaining lowercase.

i.e. something like $value = ucwords(strtolower($value));

England

Germany

Scotland
Q2

Assuming i have a country list as:

Belgium

France

Germany

Holland

Scotland

UK
How can i change this so that it will always display

UK

Scotland

... followed by the ascending order of countries entered.
Thank you in advance.
Lisa


This post here about capitalising words may help
In reply to your last question, add a field to the country table and call it order or summat like that, then number the ones you want at the top in order you want, so UK = 1

Scotland =2

Wales = 3

then number all the others as 4
In your sql query, have the order field as your first sort order, then have the name field as your second order.

That should work
J
 		Jane 7/15/2008

Regarding the first question.

Use custom expression as display field on the "Edit as" settings dialog.

Here is the list of MySQL string functions:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
L
 		Lisa2006 author 7/15/2008

Regarding the first question.

Use custom expression as display field on the "Edit as" settings dialog.

Here is the list of MySQL string functions:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html


Hi All,
I understand the logic to question 2 ... will try this.
In reponse to question 1, by placing the code
$value = ucwords(strtolower($value));
in Visual Editor | country field | View as custom, this does not change the format of the counties in the drop down list on the add page.
(Please note the physical list of countries in the database are all in lowercase. I do not want to change this case type in the database.... only how the countries appear in the drop down list)
Please help.
Lisa
J
 		Jane 7/16/2008

Please read thread more carefully.

You need to use MySQL functions in the custom expression on the "Edit as" settings dialog.
L
 		Lisa2006 author 7/16/2008

Please read thread more carefully.

You need to use MySQL functions in the custom expression on the "Edit as" settings dialog.


Hi Jane,
I'm a little confused ....
I'm assuming your are asking me do the following:
Visual Editor | double click country field | country: "Edit as" setting dialog box | Lookup Wizard | enter something in the Where Expression dialog box?
Please Help
Lisa
J
 		Jane 7/16/2008

Use custom expression as display field on the "Edit as" settings dialog.
L
 		Lisa2006 author 7/16/2008

Jane,
You keep advising "Use custom expression as display field on the "Edit as" settings dialog".
Where is this?????
ARE YOU TALKING ABOUT VISUAL EDITOR????
Just so confused......
Please can you clarify
Lisa
K
 		kdeloach 7/16/2008

L
 		Lisa2006 author 7/16/2008

Hi Kdeloach,
Thank you for your help.
Jane, i now see what you mean by custom expression.
I'm still having problems. I need to get the country field to be first letter upper and remaining letter lower. Exactly the same as using ucwords. I've used the following syntax ucwords(strtolower($value)), but cannot get to display as required.
Please help
Lisa
K
 		kdeloach 7/16/2008

Replace ucwords with ucfirst

ucfirst(strtolower($value))
L
 		Lisa2006 author 7/17/2008

Replace ucwords with ucfirst


ucfirst(strtolower($value))


I added again blank results. When executed it simply produces an error screen.
Your help would be much appreciated.
Lisa
J
 		Jane 7/17/2008

Hi,
there is no ucfirst MySQL function.

You need to use only MySQL functions in the custom expression on the "Edit as" settings dialog.

Here is just a sample:

CONCAT(UPPER(SUBSTRING(FieldName, 1, 1)), LOWER(SUBSTRING(FieldName FROM 2)))
L
 		Lisa2006 author 7/17/2008

Hi,

there is no ucfirst MySQL function.

You need to use only MySQL functions in the custom expression on the "Edit as" settings dialog.

Here is just a sample:


Thank you jane.
Whilst the above solution works for single word countries, it fails with an error message screen for double word countries such as . How can the code be amended to suit.
Thanks in advance
Lisa
L
 		Lisa2006 author 7/21/2008

Hi forum,
If anyone could help, i would be much appreciated.
Lisa
J
 		Jane 7/21/2008

Hi,
this code works fine on my test box.

Please make sure you've applied changes correctly.

If it doesn't help publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.