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: 5
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

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1231
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pböttcher » 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?)

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1231
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pböttcher » 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']}");

naeja
Posts: 5
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

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1231
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pböttcher » 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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 921
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: 5
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: 921
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: 5
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

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1231
Joined: 2018-04-01 10:12

Re: HELP with code to update quantity in inventory database

Post by pböttcher » 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%'

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 921
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