AppGini Community Forums

Posted: **2021-09-09 01:49**

Hi,

In the Inventory Management application, is there a report that shows the balance of items per Storage Location?
Or where can I readily see this in the app? AG Homepage says it is easy to pinpoint the location of items, but this view has been eluding me.

: PerLocation.PNG (5.11 KiB) Viewed 3735 times

Posted: **2021-09-13 02:30**

Haven't had a response yet, but this is how I achieved the desired result set of the expected data.
Below are the transactions for one of the items.

: CasilanTransactions.PNG (20.21 KiB) Viewed 3657 times

I then created a temporary table via pivot with the following script

Code: Select all

TRANSFORM Sum(transactions.[quantity]) AS SumOfquantity
SELECT items.item, transactions.[section]
FROM transactions INNER JOIN items ON transactions.item = items.id
GROUP BY items.item, transactions.[section]
PIVOT transactions.[transaction_type];

This produced the following:

: CasilianTransactionSummary.PNG (4.07 KiB) Viewed 3657 times

and called CT_itemsPerLocation

The query below was then applied

Code: Select all

SELECT CT_itemsPerLocation.item, sections.section, Nz([Incoming],0)-Nz([Outgoing],0)-Nz([Damaged],0)-Nz([Expired],0) AS Balance
FROM CT_itemsPerLocation INNER JOIN sections ON CT_itemsPerLocation.section = sections.id;

: ItemBalancePerLocation.PNG (3.24 KiB) Viewed 3657 times

NB - this was done via creating an ODBC with MS Access. I will attempt to do the same query in native MySQL.
How the end result can be displayed in AppGini, application side, not as a report, or even have the query created and displayed via AG would be appreciated.

Posted: **2021-09-13 15:22**

Hi,

I am not sure if I understand you correct. You should check the MySQL "if" function (https://www.w3schools.com/SQl/func_mysql_if.asp and https://www.mysqltutorial.org/mysql-if-statement/).
Once you have, you should be able to multiply the quantity by +1 or -1 (depending on "incoming" / "outgoing" etc.) , sum and group by the item type - done.
I believe you will not need a Pivot table before, but you can access the AG tables directly.

It's not a SQL query, but maybe it shows you a/the way.

Olaf

Posted: **2021-09-13 18:01**

Olaf,

Thanks for steering me in the right direction.

Code: Select all

SELECT transactions.item, transactions.section,  
SUM(IF(transactions.transaction_type = 'Incoming',(QUANTITY)*1,-(QUANTITY)*1)) as Balance
FROM transactions
GROUP BY transactions.item, transactions.section

Dennison

Posted: **2021-09-15 02:34**

I've created the above as a View in phpmyadmin. Anyone has a quick reference tutorial of how to get a custom view into the app?

Posted: **2021-09-15 08:05**

Hi,

just create a "dummy table" with the name of the view. AG will access the data that is available in the view as it would be the table. Of course, you can not add to that "table" in AG

Olaf

Posted: **2021-09-15 13:20**

Oh my, like all else in AG, works like a charm! Thanks Olaf

Posted: **2021-09-15 14:59**

HI,

you are welcome and thank you for reporting back

Olaf

AppGini Community Forums

Items Per Storage Location

Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location

Re: Items Per Storage Location