Page 1 of 1
HELP with code to update quantity in inventory database
Posted: 2021-04-05 19:17
by naeja
I've been trying every example I can find to help me update the quantity field in my inventory database. I have a table called inventoryitem and have the following fields:
ItemID
ItemType
GasketModel
PlateModel
Add
Remove
Quantity
What I need to have happen is when a user adds, removes or deletes a record it updates the quantity with the new quantity that's in stock. The quantity field I have set to read only. I have tried to modify the bottom code I found but to no avail, I would seriously appreciate some help with this.
GRS
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-07 20:50
by pbottcher
Hi,
maybe you can post a little bit more information. From what I read, you want to update a quantity, but on which table is that field?
What is the identifier that qualifies your record uniquely? (Maybe the ItemID?)
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-07 21:01
by pbottcher
I just saw the code you posted, so based on that information you could try
Code: Select all
sqlvalue("UPDATE Inventoryitem i set i.Quantity = sum(i.Add)-sum(i.Remove) where i.itemtype ={$data['itemtype']}");
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-07 22:31
by naeja
Didn't work, so I'm real frustrated now with such a simple calculation
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-08 04:59
by pbottcher
What result do you get? maybe you need to use ItemType instead of itemtype.
Also maybe you need to explain if Add and Remove have a default value of 0, otherwise you need to check for a NULL result as the calc would not work.
It is just helpful if you provide more information as it is very difficult trying to help with very little information.
Even a "simple" calculation can go wrong for multiple reasons.
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-08 06:54
by onoehring
Hi,
wouldn't it work to set the quantity as a calculated field and let AG do the job?
Another idea: Do not use two fields to change the quantity, but only one: For example: "Correction". In that field the user types "5" or "-5".
Then, in the after before_update (of after_update) hook calculate the new quantity and set the "correction" field to 0. This procedure (using before_update or after_update hook) could be done with your setting as well I think.
You can use a similar approach to pbötchers
Pseudo code! Could also probably be easier
Code: Select all
// I decided fot after update as we have the same procedure to access quantity and the rest of $data
// as quantity is read only, it's not in the $data variable, we would need to pull it
function after_update($selectedID){
...
$quantity = sqlValue("select quantity from YOURTABLE where YOURPK=". $selectedID);
$add = sqlValue("select add from YOURTABLE where YOURPK=". $selectedID);
$remove = sqlValue("select remove from YOURTABLE where YOURPK=". $selectedID);
$quantity = $quantity + $add - $remove;
$result=sqlValue ("UPDATE YOURTABLE set quantity =". $quantity .", add=NULL, remove = NULL WHERE YOURPK = ". $selectedID");
...
}
Olaf
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-09 01:30
by naeja
Olaf
Thank you for your reply, I like the correction suggestion, how would I use that in the appgini calculation formula. I've tried several code variations in the calculation option of appgini. I really appreciate your help.
Gord
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-09 09:17
by onoehring
Hi,
you will need to open the file /hooks/inventoryitem.php and look for the function after_update. There this (actually: adjusted) code would go.
Olaf
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-09 21:56
by naeja
Thank you Olaf, I couldn't get anything to work, so what I've decided to do is create a table to work with this till it works. Ive created a table called
modeln with the following fields
id set to primary and unique
model set to varchar
add set to integer and 0 default
subtotal set to read only and added the following in the appgini calculated field
SELECT `modeln`.`subtotal` + `modeln`.`add`
FROM `modeln`
WHERE `modeln`.`id`='%ID%'
what happens is when I add 1 to the add field it give a subtotal of 3 when i select save changes button and everytime I open or close, save or refresh the form it multiplies the subtotal by 3 if I select the back button in the form it adds the amount again, this is very strange.
If i open phpmyadmin and select the table and run the sql query from their with the above code it gives me the following error
Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available
I'm having a challenge wraping my head around this
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-10 07:35
by pbottcher
Hi,
you have a logic error as you add the subtotal every time again.
What you could try is
Code: Select all
SELECT sum(IFNULL(`modeln`.`add`,0))
FROM `modeln`
WHERE `modeln`.`id`<='%ID%'
Re: HELP with code to update quantity in inventory database
Posted: 2021-04-10 08:02
by onoehring
Hi,
in addition to pbötcher, I want to add, that you should probably reset the "add" column to "0" after your calculation.
Then, even IF the code would run each time, it would only add 0 to the subtotal.
Olaf