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
HELP with code to update quantity in inventory database
Re: HELP with code to update quantity in inventory database
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?)
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.
Re: HELP with code to update quantity in inventory database
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.
Re: HELP with code to update quantity in inventory database
Didn't work, so I'm real frustrated now with such a simple calculation
Re: HELP with code to update quantity in inventory database
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.
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.
Re: HELP with code to update quantity in inventory database
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
Olaf
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");
...
}
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: HELP with code to update quantity in inventory database
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
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
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: HELP with code to update quantity in inventory database
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
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
Hi,
you have a logic error as you add the subtotal every time again.
What you could try is
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.
Re: HELP with code to update quantity in inventory database
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view