2. PHPRunner General questions
  3. mutliple controls/ one field
This topic is locked

mutliple controls/ one field

 		6/14/2009 11:08:23 PM
PHPRunner General questions
R
 rmac author

Hello all;
I'd like to get some suggestions on this from some of the more web-savvy members. I'm sure it's not a just PHPRunner specific issue.
I've got an inventory program with three main tables; part lookup, inventory and part history. The lookup table has all of the details common to all parts (part numbers, description, notes, etc. The inventory table has only a ref number (our primary key), serial number and revision code. The history table has all transient info for each individual part like stock location, current status, record date, etc. The tables are linked like this:
Part_Lu__Inventory____History

lu_id (pk)-------> lu_id (fk)

---------------------ref_no (pk)---------> ref_no (fk)
My problem is this. In the inventory pages all of the user-informative info for the part is tied to fields from the lookup (part # and description). Using the lookup works fine for the LIST page, but ADD is clumsy. The lookup table is nearly 8000 rows, and the same part may have up to three different part numbers. Right now I use the lu_id field as a drop down showing the main part number and then key the rest of the lookup fields to that. It works, but I'd like to be able to let the users enter parts through ANY of the fields (part number 1, 2 or 3, description, category, etc.) but I don't know how to trigger a change on any one of the controls and get the rest to update and sync up. I've also tried making all of the lookup fields a separate alias of lu_id, hoping that since they were all the same field they would update in unison, but of course it didn't work. To add or edit that way I needed to match every field to the same row:( Could a separate control (like an update button) be used to force everyone to sync to the last control that had focus or something similar?
I'm sure this is done all the time, but maybe would require too much custom coding to make it worthwhile for this project. Nonetheless, I'd sure appreciate insight on to how it might be achieved.
Thanks,

Ray
J
 		Jane 6/16/2009

Ray,
I suppose you want to update two tables (Part_Lu and Inventory) at the same time.
PHPRunner doesn't support updating multiple tables on one page.

You can implement this manually using Before record updated/addedevents.
I.e. use something like this:

global $conn;

$sql = "update othertable set joinedfield=".$vales["joinedfield"]." ... ";

db_exec($sql,$conn);

unset($vales["joinedfield"]);
R
 		rmac author 6/22/2009

Jane;
Thanks for your response. What I wanted to do was not update multiple tables. I needed to update one table with one value but be able to lookup that value either through multiple controls or through one control w/ multiple fields; in this case search on any one of three different part numbers that pertain to the same part. What I ended up using instead was your great mod <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=42060&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' /> from the link below, which works even better. I concatenated all three lookup fields in the control, so now the user can do a free-form AJAX search on any part number in any order to find a match.
http://www.asprunner.com/forums/index.php?...x+search+in+add
As well as this works, I think it should be promoted to the "tips and tricks" section, and maybe considered as an AJAX lookup option for future versions (checkbox for AJAX search anywhere in the string).
Thanks again.
Ray

to show all values based on any symbol open generated ..._lookupsuggest.php file, find this line:

$LookupSQL .= "`FieldName` LIKE '".db_addslashes($value)."%'";
and replace it with this one:
$LookupSQL .= "`FieldName` LIKE '".db_addslashes($value)."%'";