New record value based on previous record

Archived from the Xataface Users forum.

ADobkin — Thu Aug 18, 2011 3:39 pm

What is the best way to pre-populate a new record form with a calculated value based on the previous record or a maximum value from all records? For example, when tracking automobile mileage, rather than starting from scratch with each new record, I would like to prefill the new odometer value with the previous odometer value plus the trip miles. Or, when generating invoice numbers, work order numbers, etc. I would prefill the new value with the maximum existing value plus one. I can’t use an auto-incremement field for this because it is not the primary key, and MySQL only allows one per table.

Thanks,
Alan

---

Jean — Fri Aug 19, 2011 4:58 am

Alan,

You need to calculate your value in the delegate class of the table with the function fieldname__default
http://xataface.com/wiki/Delegate_class_methods

Jean

---

ADobkin — Fri Aug 19, 2011 5:17 am

Thanks Jean,

I am somewhat familiar with the fieldname__default function. I actually created that wiki page.

But I am not sure how to use the result of an SQL query with this function, specifically to retrieve the value from a previous record or the MAX() value from all previous records. Can someone provide an example of how to do this?

Thanks,
Alan

---

Jean — Fri Aug 19, 2011 6:05 am

Well you can have something like this :

Code: Select all

`$this->app =& Dataface_Application::getInstance();

        $result = mysql_query(“select MAX(field_name) AS maximum from Table”, $this->app->db());
       

            while($row = mysql_fetch_assoc($result))
            {
                return $row[‘maximum’];
            }`

---

ADobkin — Fri Aug 19, 2011 7:59 am

That’s perfect! Thank you!

Alan