How to Search on Custom Field

PHPMaker General Discussion (PHPMaker)
1

I’m using a Custom Field named vt_Calc to calculate values using lookup of another table like this: (SELECT SUM(amount) FROM payments WHERE subscriptions_all.subscription_id = payments.subscription_id)But when I search using Ext Search on this field, it gives an error saying “Unknown column vt_Calc”.Then I replaced the Search Filter code in Recordset_Searching with the actual lookup calculation used in vt_Calc. This gives an error in MySQL saying unknown table subscriptions_all.Finally I inserted the calculated field in the SQL of the Views table to appear as a normal field. Now the Ext Search works fine, but I cannot Add records to this Views table!Q1. What is best way to get calculated fields to work with Ext Search as well as allow Add? Surely this must be a common problem!Q2. In a Views table list page when the user clicks the [+] button to add new record, is there a way to redirect the Add to the underlying main table? This would solve the whole situation also.Please advise best way forward. Thank you!

2

You may search UpdateTable keyword from this forum for your references.

3

This was so amazing! This is such a powerful feature of PHPMaker that I’m surprised it is not documented in the help files. This needs a whole section in the Tutorials, because now Views can be freely used without any restrictions! Thank you so much for your help. :)For those who are new to this like me, this is what I understood you can do.Use Views table freely without worrying about whether it is updateable. But when it comes to Add or Edit of a record you can Add/Edit directly in the underlying main table instead of the Views table. To do this:

  1. In Row_Inserting and in Row_Updating of your Views table put$this->UpdateTable = “UnderlyingTableTame”; //change table into which we Add or Edit so that we do not add/edit in the Views table!
  2. If you have any fields in the Add screen that are not present in the underlying table then remove them from Add/Edit. . But if you need them for any reason then either make them ReadOnly or insert the following command:unset($rsnew[“FieldThatIsNotInUnderlyingTable”]); //remove any fields not existing in underlying table
4

I’m inserting records into a View made from table named “timeslotgroups” which has primay key field timeslotgroup_id which is AutoIncrement.

function Row_Inserting($rsold, &$rsnew)
{
	$this->UpdateTable = "timeslotgroups"; //change table into which to insert so that we do not insert in the View table!
	$rsnew["timeslotgroup_id"] = 0; //put a value here to avoid invalid keyfield error. OR ELSE you can also use: $this->ValidateKey = FALSE; 
    return true;
}

After this the record in inserted but I have no way to access the actual value assigned by MySQL to timeslotgroup_id from code in Row_Inserted.

function Row_Inserted($rsold, &$rsnew)
{
    $timeslotgroup_id = $rsnew["timeslotgroup_id"];
    log("new timeslotgroup_id=$timeslotgroup_id"]);
    //...... processing code that uses value of timeslotgroup_id to generate other table entries.
}

This gives timeslotgroup_id=0. And if I use $this->ValidateKey = FALSE; then I get timeslotgroup_id=.The same code used on the main table (not needing UpdateTable) gives the correct value of timeslotgroup_id in Row_Inserted().Please guide on how to get the assigned value of AutoInc primary field in Row_Inserted.

5

This following topic perhaps would help:

6

Works perfectly! Thank you so much! :DUpdated working code as below:

function Row_Inserted($rsold, &$rsnew)
{
	$rsnew["timeslotgroup_id"] = Conn()->lastInsertId(); //update to value assigned after insert!

 	$timeslotgroup_id = $rsnew["timeslotgroup_id"];
	log("new timeslotgroup_id=$timeslotgroup_id"]);
	//...... processing code that uses value of timeslotgroup_id to generate other table entries.
}