HELP with code to update quantity in inventory database

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
naeja
Posts: 7
Joined: 2021-04-03 17:01

HELP with code to update quantity in inventory database

Post by naeja » 2021-04-05 19:17

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pbottcher » 2021-04-07 20:50

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?)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pbottcher » 2021-04-07 21:01

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']}");
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

naeja
Posts: 7
Joined: 2021-04-03 17:01

Re: HELP with code to update quantity in inventory database

Post by naeja » 2021-04-07 22:31

Didn't work, so I'm real frustrated now with such a simple calculation

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pbottcher » 2021-04-08 04:59

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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: HELP with code to update quantity in inventory database

Post by onoehring » 2021-04-08 06:54

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

naeja
Posts: 7
Joined: 2021-04-03 17:01

Re: HELP with code to update quantity in inventory database

Post by naeja » 2021-04-09 01:30

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

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: HELP with code to update quantity in inventory database

Post by onoehring » 2021-04-09 09:17

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

naeja
Posts: 7
Joined: 2021-04-03 17:01

Re: HELP with code to update quantity in inventory database

Post by naeja » 2021-04-09 21:56

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pbottcher » 2021-04-10 07:35

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%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: HELP with code to update quantity in inventory database

Post by onoehring » 2021-04-10 08:02

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

Post Reply