Radicore Forum: How To » Search when some columns are dropdown lists

Home » RADICORE » How To » Search when some columns are dropdown lists

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Search when some columns are dropdown lists [message #517]

Mon, 01 January 2007 05:00

David Lee
Messages: 44
Registered: June 2006

Member

I have a table with two drop-down selection columns. Within the search screen, the drop-down selection lists appear. However, I do not know how to enable a "don't care" option on either of these drop-down lists, so my searching does not work!

Report message to a moderator

Re: Search when some columns are dropdown lists [message #520 is a reply to message #517]

Mon, 01 January 2007 05:48

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

That's strange. The framework is supposed to automatically insert an "(undefined)" entry in each dropdown list when building a search screen. If you take a look in file include.xml.phpX.inc at line 296 you will see the code. Can you step through with your debugger to see what is happening there?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Search when some columns are dropdown lists [message #526 is a reply to message #517]

Wed, 10 January 2007 12:03

David Lee
Messages: 44
Registered: June 2006

Member

Having finally installed a debugger and found some more time, in file include.xml.php4.inc at line 302, array_key_exists($field, $fieldspec) evaluates as false, skipping the add blank entry code!
The reason is that my lookup table has the key location_id, but the main table field is fklocation_id.

This practice of prefixing foreign table indexes with fk comes from using the QSEE-Superlite program, which I, being a novice in the database world, found very useful for creating the entity relationship diagrams, and then the database structure from those diagrams.

To solve this problem, the first need is to confirm is Radicore should work with this naming convention, or if this breaks the design rules.

That should be easy for you to confirm, Tony!

Regards
David Lee

Report message to a moderator

Re: Search when some columns are dropdown lists [message #527 is a reply to message #526]

Wed, 10 January 2007 12:28

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

Radicore does not use any naming convention, such as an 'fk' prefix, to identify foreign keys. A field becomes a foreign key whenever it is used in a relationship regardless of its name, and it can be used in more than one relationship.

The Radicore framework "knows" that a field is a foreign field when the relationship details are entered into the Data Dictionary and exported to the application. This information is then made available in the $child_relations and $parent_relations arrays within the '<table>.dict.inc' file

When you are associating a lookup array with a field it is a good idea to have the lookup name the same as the field name as the code has to determine the field type before being able to add a blank entry to the array.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Search when some columns are dropdown lists [message #531 is a reply to message #517]

Wed, 10 January 2007 15:32

David Lee
Messages: 44
Registered: June 2006

Member

As I understand it, the current code only adds the extra item to the list if $field is a key in the $fieldspec array. The keys of the fieldspec array come from the main table to be displayed, and, in my case include fklocation_id. However, the $field comes from the lookup table, and, in my case, is location_id. Therefore, the extra item will not be added, and will only be added if I renamed my fklocation_id column to location_id.
This, according to the previous post, should not be needed, and is therefore a bug in the code.

The value of location_id does appear as a lower level in the $fieldspec data structure, as the name of the optionlist for fklocation_id. Should the code check for this variable?

At present I do not understand why this check is in the code at all, but there is probably a very good reason for it, and I would not want to try altering it without understanding that reason!

Report message to a moderator

Re: Search when some columns are dropdown lists [message #532 is a reply to message #531]

Wed, 10 January 2007 16:10

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

If the contents of lookup array 'location_id' is to be loaded into field fklocation_id, then rename the lookup array to 'fklocation_id'. The name of the lookup array is supposed to be associated with the destination field (the foreign key) not the source field (the primary key). Does that make sense?

Or you can drop the 'fk' suffix so that the foreign key and primary key names are the same.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Search when some columns are dropdown lists [message #534 is a reply to message #517]

Wed, 10 January 2007 17:01

David Lee
Messages: 44
Registered: June 2006

Member

To clarify, are you saying that the foreign key and primary key names MUST be the same in the database (which appears to contradict your post of 17:28 of today), or are you saying that the Radicore system allow an alias type renaming to be introduce (if so, where in the menu system?) or are you saying that the current workaround is to make the foreign key and primary key names the same?

Thanks
David Lee

Report message to a moderator

Re: Search when some columns are dropdown lists [message #536 is a reply to message #534]

Wed, 10 January 2007 18:13

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

No, the foreign key names do not *have* to be the same as the primary key names, but my convention is to keep them the same wherever possible.

What I am saying is that to avoid the problem you encountered the lookup array should have the same name as the field in which it will be displayed. So if the field is called 'fklocation_id' the lookup array should also be called 'fklocation_id' and not 'location_id'. The easiest way for the framework to associate a lookup array with a field is if they have the exact same name.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Search when some columns are dropdown lists [message #537 is a reply to message #517]

Thu, 11 January 2007 14:06

David Lee
Messages: 44
Registered: June 2006

Member

This post is mainly to help any others who come down this same track, rather than pursing any further options.

As my lookup array comes from another table, this implies that to use this functionality I need to
1) Modify the MySQL field name on that table to fklocation_id
2) Re-import the table into radicore
3) Re-setup the realtionships
4) Export the dictionary for that table
5) Edited any scripts that reference the old field name

Rather more work than I hoped, especially as in my complete system I have 6 tables which may be used as drop-down lists.

Maybe I should check out two other possibilities I vaguely remember appearing in the documentation:
1) Create a subclass with the modified field name - something like this was possible for handling some kind of name conflict
2) Can the GetExtraData routine be forced to return the array with the name of fklocation_id?

Thinking about the possible problems with those routes, I think I had better change the database field names, and keep to Tony's convention, which allows the system to fully work. Otherwise I may find similar problems elsewhere.

I did also try using a pop-up list, and found that such lists assume that the index field is a short form version of the item, and normally display that short form. I had not seen this documented anywhere, but as my index fields are numeric, caused me to drop that idea.

David Lee

Report message to a moderator

Re: Search when some columns are dropdown lists [message #538 is a reply to message #537]

Thu, 11 January 2007 14:40

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

I do not understand point #1 - surely you mean to rename the foreign key from 'fklocation_id' to 'location_id' (i.e remove the 'fk' prefix) so that it matches the name of the lookup array. There is no point in ADDING that prefix as it serves no useful purpose.

If you do not wish to rename that field on the database all you have to do is rename the lookup array from 'location_id' to 'fklocation_id'. This just means changing one line of code so would be much easier than changing the database.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Search when some columns are dropdown lists [message #540 is a reply to message #517]

Fri, 12 January 2007 18:17

David Lee
Messages: 44
Registered: June 2006

Member

Point #1 was just making the two fields the same name - it does not matter if they are both "location_id" or "fklocation_id"

However, I have finally realised what you were saying, and/or how to overcome this problem. It requires two small, matching changes:

1) In the data dictionary for the main table, column fklocation_id, the name of the option list should be fklocation_id, not location_id. The updated structure needs exporting to PHP after changing.

2) In the cm_getExtraData routine of the main table, the name of the key for the required array should be fklocation_id, not location_id

Having made those changes, the search dropdown list have the extra entry of "undefined"

Thanks for your help, Tony, and I hope this thread saves someone else some time!

David Lee

Report message to a moderator

Re: Search when some columns are dropdown lists [message #543 is a reply to message #540]

Sat, 13 January 2007 05:13

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

Perhaps it may be a good idea for me to update the next release as follows:

A dropdown list or radio group is populated from a lookup array which may be obtained at runtime either from the contents of a database table or from an entry in a language_array.inc file (part of the Internationalisation feature). This array by default contains only non-blank entries, but sometimes a blank entry is required to indicate that no choice has yet been made. This can be done automatically by the framework (using a blank key with a description of "undefined") when the lookup array is loaded into the XML document. Blank entries are NOT required in multi-choice dropdown lists, so the framework needs to check the control type of the field before it knows whether to insert a blank entry or not. The name of the lookup array need not be the same as the field into which that array will be loaded, so the framework will function as follows:

(a) If the lookup array has the same name as a field on the current screen, then it will be assumed that the array belongs to that field.

(b) If the lookup array does not have the same name as a field on the current screen then the framework will step through the contents of $fieldspec until it finds the entry where "optionlist" contains the name of that lookup array.

Will this be sufficient?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Previous Topic:	List with pre-defined search active
Next Topic:	flexible approach

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu Jun 19 03:17:01 EDT 2025

Total time taken to generate the page: 0.07775 seconds