AppGini Community Forums

Posted: **2020-11-28 17:47**

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.

Posted: **2020-11-28 20:12**

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:

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;
	}

Posted: **2020-11-29 11:05**

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.

AppGini Community Forums

SUM in another table

SUM in another table

Re: SUM in another table

Re: SUM in another table