Items Per Storage Location

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Items Per Storage Location

Post by udennison » 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
PerLocation.PNG (5.11 KiB) Viewed 1693 times

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Items Per Storage Location

Post by udennison » 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
CasilanTransactions.PNG (20.21 KiB) Viewed 1615 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
CasilianTransactionSummary.PNG (4.07 KiB) Viewed 1615 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
ItemBalancePerLocation.PNG (3.24 KiB) Viewed 1615 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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Items Per Storage Location

Post by onoehring » 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

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Items Per Storage Location

Post by udennison » 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

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Items Per Storage Location

Post by udennison » 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?

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Items Per Storage Location

Post by onoehring » 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

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Items Per Storage Location

Post by udennison » 2021-09-15 13:20

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


Post Reply