Autopopulate fields based on lookup on another table

[rleyba]

Hi Steve,
I just need some assistance crafting an afterinsert trigger on the form I'm designing.

One of the fields in the form is DEVICE_NAME. The form also contains other fields like LOCATION, VENDOR, IP_ADDRESS and SERIALNUM, etc. I already have another table in the same xataface application called INVENTORY, which has among its fields, DEVICE_NAME, IP_ADDRESS, SERIALNUM (and there is one-to-one correspondence among these fields).

My intent is that once I fill up the form and the user keys in the DEVICE_NAME, when the record is saved, xataface will do a lookup of the IP_ADDRESS and SERIALNUM from the INVENTORY table based on the DEVICE_NAME entered by the user, then just auto-populate these two fields in the form. If there is no match, I would like some generic message like UNKNOWN to be keyed in in IP_ADDRESS and SERIALNUM.


Thanks very much.

[shannah]

Sounds like you have it figured out. What exactly are you stuck on?

[rleyba]

Hi Steve,
Unfortunately, i couldn't find out the correct syntax, but basically what I hope to accomplish is something like below.

Code: Select all

function afterInsert(&$record){
    $record->setValue('IP_ADDRESS', __SQL__ ="select IP_ADDRESS from INVENTORY where INVENTORY.DEVICE_NAME= $record->val('DEVICE_NAME')");
    $record->setValue('SERIALNUM', __SQL__ ="select SERIALNUM from INVENTORY where INVENTORY.DEVICE_NAME= $record->val('DEVICE_NAME')");

}


*Not sure how to test if __SQL__ query returns null. In that case I'd like the field to return the string UNKNOWN.

Thanks very much.

[auphi]

rleyba,

Try this:

Code: Select all

function afterInsert(&$record){
   $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>$record->val['DEVICE_NAME'])); //This will pull the data
   $record->setValues(array('IP_ADDRESS'=>$rec->val['IP_ADDRESS'], 'SERIALNUM'=>$rec->val['SERIALNUM'])); //This will set both values at once
   $record->save(); //Save the record
}


You can also check here for more examples:
http://xataface.com/wiki/Introduction_t ... taface_API

[rleyba]

Hi auphi,

thanks very much for your tips. The two fields below IP_ADDRESS and SERIALNUIM are returning blank, and not being inserted in my database even though I can confirm that I am using the exact field names and table names. How do I troubleshoot that a record is actually being retrieved?
I tried to put an echo $rec but this doesn't show up on screen. how do I debug the value of $rec to confirm that this variable is being populated?
Also, pls. see the trials I set below, denoted by the ## sign. Anything else you may suggest?

Thanks and regards.

Code: Select all

function afterInsert(&$record){
   $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>$record->val['DEVICE_NAME'])); //This will pull the data
##   $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>'Router-A')); //i explicitly tried to retrieve an EXACT match, and i still get a blank when the record gets inserted
##   $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>'=Router-A')); //i also tried to put an = sign inside the string as I saw some examples doing this, still I get a blank when the record gets inserted.
   echo $rec;  //  This record is not being displayed when I do this.  How do I echo that entire retrieved record to the screen?
   $record->setValues(array('IP_ADDRESS'=>$rec->val['IP_ADDRESS'], 'SERIALNUM'=>$rec->val['SERIALNUM'])); //This will set both values at once
##   $record->setValues(array('IP_ADDRESS'=>'10.10.10.10', 'SERIALNUM'=>'ABC123'])); //by explicitly forcing an explicit value, then this works, so I know this line is working.
   $record->save(); //Save the record
}

[rleyba]

I think I got it now (partly). I changed the brackets to parentheses and then i get closer to the correct info. However, in the case below, the line prepended with the ## doesn't work. but the next line, where I did a test and EXPLICITLY put in a value for the Name field, worked OK. I can't seem to see why this construct

Code: Select all

array('DEVICE_NAME'=>$record->val('DEVICE_NAME')

doesn't work when I have the names right. It's as though the value of $record->val('DEVICE_NAME') is undefined when it is evaluated during the afterInsert function.

Code: Select all

function afterInsert(&$record){
##     $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>$record->val('DEVICE_NAME)));
   $rec = df_get_record('INVENTORY, array('DEVICE_NAME'=>'FIREWALL-A'));
   $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'SERIALNUM'=>$rec->val('SERIALNUM'))); //set both values at once
   $record->save(); //Save the record
}

Would appreciate any ideas, thanks.

[auphi]

Sorry about the syntax error; I freehanded the code, and apparently didn't error check myself, but you are correct they should be () not [].

Couple of quick things: echo won't work with record objects (the variables $record/$rec) by themselves b/c they are arrays, but you can do "print_r($record->vals())" to show the contents of your record (just learned that myself, thanks Steve). You can however use an echo if you specify the field, i.e. "echo $record->val('DEVICE_NAME')".

I noticed that your line: $rec = df_get_record('INVENTORY', array('DEVICE_NAME'=>$record->val('DEVICE_NAME))); has a syntax error, needing a ' after DEVICE_NAME. So try that. If that's not what's causing the problem, do an "echo $record->val('DEVICE_NAME')" or "print_r($record->vals())" to see what's showing up.

[rleyba]

Thanks auphi,
I figured out what was wrong, the foreign table I was referencing had very similar field names to another table that I was also using for something else, and I got some typos there. Here is the working code now. I did some other changes, am now calling MDL table instead of INVENTORY, and need the MODEL instead of the SERIAL number. The first part of the code is working. I just noticed that for new records, everything works fine, but once I either use a form to copy, update, or use the normal edit feature to change the most important value, DEVICE_NAME, then after clicking submit, the page just sort of hangs, then later on saying connection was reset then have to res-submit the form to make it work.

Code: Select all

function afterInsert(&$record){
    $rec = df_get_record('MDL', array('Name'=>$record->val('DEVICE_NAME')));
    $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'MODEL'=>$rec->val('Model'))); //set both values at once
   $record->save(); //Save the record
}




##function beforeSave(&$record){
##    $rec = df_get_record('MDL', array('Name'=>$record->val('DEVICE_NAME')));
##    $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'MODEL'=>$rec->val('Model'))); //set both values at once
##    $record->save(); //Save the record
##}



I am sure it has something to do with the beforeSave(&$record) function. I saw that there is an after_action_edit function. But I couldn't find an after_action_copy and after_action_update. Because most of my users would like to use the shortcut button "With Seletected [COPY][UPDATE]".

I believe I should be doing this, just not sure of the syntax, and it looks like it will affect the entire table, not just the set of records I am copying, or updating.

Code: Select all

function after_action_edit() {
__SQL__= "UPDATE INCIDENT,MDL SET INCIDENT.IP_ADDRESS=MDL.IP_Address, INCIDENT.MODEL=MDL.Model where INCIDENT.DEVICE_NAME=MDL.Name"
}

Thanks for all the help. Unfortunately, I am still green on PHP

[auphi]

I'm trying to wrap my head around what you're trying to do... but it kinda sounds like you're on the right track.

If you want the "auto copying" feature to work on records after they've been created, then you don't want to use afterInsert (as that only works when a record is being created). You could use either beforeSave or afterSave instead. I typically use beforeSave, but I imagine that either would probably work fine. I assume these take into account the case where you use the Copy/Update buttons, but I don't know, as I've never used them, and actually disabled them for my application.

As far as the question about the reset connection... I'm not sure what's causing it, but I'm pretty sure you don't want to use $record->save() in beforeSave(). I don't know if that's what you're doing, but if so, try getting rid of that line and see if it works.

I.E.

Code: Select all

function beforeSave(&$record){
    $rec = df_get_record('MDL', array('Name'=>$record->val('DEVICE_NAME')));
    $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'MODEL'=>$rec->val('Model'))); //set both values at once
}


[rleyba]

Hi auphi,
EXCELLENT! Now the whole thing is working. And you are exactly right, the $record->save(); messed up the whole thing.

This is what I had originally:

Code: Select all

function beforeSave(&$record){
    $rec = df_get_record('MDL', array('Name'=>$record->val('DEVICE_NAME')));
    $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'MODEL'=>$rec->val('Model'))); //set both values at once
    $record->save(); //Save the record

Simply changing it to this made everything work right, whether I edit from the menu, do multiple Updates, or edit manually after viewing the form, etc.
My code is now like this:

Code: Select all

function beforeSave(&$record){
    $rec = df_get_record('MDL', array('Name'=>$record->val('DEVICE_NAME')));
    $record->setValues(array('IP_ADDRESS'=>$rec->val('IP_Address'), 'MODEL'=>$rec->val('Model'))); //set both values at once


Thanks very much for the help. I had thought that since the beforeInsert, afterInsert requires an explicitly save command, I had expected that the beforesave and afterSave would require the same. Not quite sure why though. but thanks again.