How To Set a Field Value Based on Another From Table

Topic: How To Set a Field Value Based on Another From Table (Read 1824 times) previous topic - next topic

How To Set a Field Value Based on Another From Table

March 10, 2020, 02:49:57 AM

Hello,
I want to set a field value on a Page, Before Add, and it's not working well. I know am missing something.

The field 'unitid' on the page is to be set to max(unitid)+1 from table 'deptunits' where 'dept' in 'deptunits' = current 'dept' specified on the page.
My current codes are:

$res = $db->rawQuery("SELECT * FROM deptunits WHERE dept='$dept' ORDER BY UnitID DESC LIMIT 1");
$modeldata['UnitID'] = $res['UnitID'] + 1;

I need current syntax that could help achieve this on the Before Add event.

BR

Re: How To Set a Field Value Based on Another From Table

Reply #1 – March 13, 2020, 06:39:01 AM

What DB system are you using?

You might find that MySQL's Auto Increment Feature allows you to just insert without sending the ID# and it will automatically assign the next number.

There are also ways to make use of these numbers on pages like https://www.tutorialspoint.com/how-to-get-the-next-auto-increment-id-in-mysql.

Re: How To Set a Field Value Based on Another From Table

Reply #2 – March 27, 2020, 02:01:23 AM

Helllo robertr,

Thanks for your reply, am using MySQL and I use auto increment well.

This is however different. The field unitid counts from 1 grouped by dept. That is, for each dept it starts from 1 so in the table you would have:
DEPT, Unitid
ADMIN, 1
ADMIN, 2
ACCT, 1
ACCT, 2
ACCT, 3
SALES, 1
SALES, 2
SALES, 3
etc.