Hi people!
I'm making a database to control the toner stock of ou printers and I creates some tables for it:
entradas: table for incoming items to warehouse
salidas: table for outgoinf items to the printers
impresoraMarca: table for makes of printers
impresoraModelo: table for models of printers
impresoras: table for printers filtering makes and models
tonerModelo: models of toners cartridges filtering by makes and models
impresoraToner: table for indexing toner cartridges by makes and models
All that works fin but I need to know the stock of each toner model. I think I have to sum entradas.cantidad (quantity of incoming items for an specific model) and substrack the sum of outgoing items (salidas.cantidad) and store it on tonerModelo.stock for each model of toner.
The field entradas.modeloToner is a lookup field of tonerModelo.modeloToner. It is, when I have incoming items, I select the model of cartridge there and insert the quantity.
the thing I don't have clear to do is to store the stock by model of toner cartridge because the child record:
Table entradas:
id
date
marca (maker, lookup field of table impresoraMarca for dropdown list)
modelo (printer model, lookup field of table impresoraModelo for dropdown list)
modeloToner (cartridge model, lookup field for tonerModelo.modeloToner for dropdown list and select the correct model of cartridge filtering by previous make and model)
cantidad: quantity of incoming items
Table tonerModelo:
id
marcaImpresora (printer maker, look up field impresoraMarca.marca for dropdown list)
modeloImpresora (printer model, look up field impresoraModelo.modelo for drop down list filtering by marca)
modeloToner (cartridge model, arbitrary field created when I have a new model of cartridge)
My columns ar shown like this in tonerModelo:
id marcaImpresora modeloImpresora modeloToner stock
1 OKI ES5462 5462-K 1
2 OKI ES5462 5462-C 2
3 Kyocera M3540idn TK3100 1
This field "stock" has to be calculates as sum(entradas.cantidad) - sum(salidas.cantidad) and related to the column modeloToner.
I arrived to sum all incoming's but are show for all modeloToner, all them appears to hace 4 units in this example (1+2+1).
How can I make this relationship?
Thanks a lot.
SUM in another table
Re: SUM in another table
Hi,
not sure if I gol all tables correct, but I assume that the stock is in the tonerModelo.
You can try to put the code into the modeloToner_init function:
not sure if I gol all tables correct, but I assume that the stock is in the tonerModelo.
You can try to put the code into the modeloToner_init function:
Code: Select all
function modeloToner_init(&$options, $memberInfo, &$args) {
sqlvalue("UPDATE modeloToner left join (SELECT modeloToner,sum(entradas.cantidad) as c from entradas group by modeloToner ) e on e.modeloToner=modeloToner.id left join (SELECT modeloToner, sum(salidas.cantidad) as c from salidas group by modeloToner ) s on s.modeloToner=modeloToner.id set modeloToner.stock=(ifnull(e.c,0)-ifnull(s.c,0))") ;
return TRUE;
}
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: SUM in another table
Hi pböttcher.
I dind't see your messages before.
Yes, the stock field is in tonerModelo table.
I'll try your option, is elegant and in the hook file, so I can modify it at any moment without compiling the project.
After a lot of reading I make this query that, in first instance testing, works:
SELECT (IFNULL(entradaToner.entradaCantidad,0) - IFNULL(salidaToner.salidaCantidad,0)) AS total
FROM
(SELECT SUM(cantidad) entradaCantidad, entradas.modeloToner FROM entradas where entradas.modeloToner='%ID%') entradaToner,
(SELECT SUM(cantidad) salidaCantidad, salidas.modeloToner FROM salidas where salidas.modeloToner='%ID%') salidaToner
I made it as a Frankestein because I was coping, pasting and modifying until it works.
Many many thaks for your help.
I dind't see your messages before.
Yes, the stock field is in tonerModelo table.
I'll try your option, is elegant and in the hook file, so I can modify it at any moment without compiling the project.
After a lot of reading I make this query that, in first instance testing, works:
SELECT (IFNULL(entradaToner.entradaCantidad,0) - IFNULL(salidaToner.salidaCantidad,0)) AS total
FROM
(SELECT SUM(cantidad) entradaCantidad, entradas.modeloToner FROM entradas where entradas.modeloToner='%ID%') entradaToner,
(SELECT SUM(cantidad) salidaCantidad, salidas.modeloToner FROM salidas where salidas.modeloToner='%ID%') salidaToner
I made it as a Frankestein because I was coping, pasting and modifying until it works.
Many many thaks for your help.