SUM in another table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
ucevista
Veteran Member
Posts: 30
Joined: 2016-05-08 09:41

SUM in another table

Post by ucevista » 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.

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

Re: SUM in another table

Post by pbottcher » 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;
	}
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.

ucevista
Veteran Member
Posts: 30
Joined: 2016-05-08 09:41

Re: SUM in another table

Post by ucevista » 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.

Post Reply