2. PHPRunner General questions
  3. Coping field data from a new record to an activity table
This topic is locked

Coping field data from a new record to an activity table

 		9/13/2013 18:57:38
PHPRunner General questions
B
 bencroftweb author

I am setting up a crm and wish to display customer activity
i.e when a new customer is added that it adds a record into the activity table with their ID and name and whom was logged in when it was created.
I have copied the example in the help file but I'm pretty stuck so far.
I can get it to create a record with static text but not with the field data i.e. Customer ID or their name.
global $conn;

$strSQLInsert = "insert into Activity (ClientID, Description) values (Id, 'Client added to system')";

db_exec($strSQLInsert,$conn);
I'm really hoping it's something silly I am missing.
Help!!
Ben
Sergey Kornilov admin 9/16/2013

According to documentation in events like AfterAdd you can access any field value as $values["FieldName"] or $keys["FieldName"], if this field is a key column.

More info:

http://xlinesoft.com/phprunner/docs/after_record_added.htm
Assuming ID is a key column:

global $conn;

$strSQLInsert = "insert into Activity (ClientID, Description) values (" . $keys["ID"] .", 'Client added to system')";

db_exec($strSQLInsert,$conn);
B
 		bencroftweb author 9/17/2013

Great! That got the ID working a treat!
Now I tried the same format to insert and field value but I get an error:
Entered into "After Record Added"
global $conn;

$strSQLInsert = "insert into Activity (ClientID, Name, Description, CurrentUser) values (" . $keys["id"] .", " . $values["FullName"] .", 'Client added to system')";

db_exec($strSQLInsert,$conn);
Displayed Error:
Unknown column 'test' in 'field list'

insert into Activity (ClientID, Name, Description) values (27, test, 'Client added to system')
test is the value that comes from the FullName field - but it thinks it's looking for a field named test?
Sergey Kornilov admin 9/17/2013

Text values must be surrounded by single quotes in SQL query:

global $conn;

$strSQLInsert = "insert into Activity (ClientID, Name, Description, CurrentUser) values (" . $keys["id"] .", '" . $values["FullName"] ."', 'Client added to system')";

db_exec($strSQLInsert,$conn);