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.
Items Per Storage Location
Re: Items Per Storage Location
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.
I then created a temporary table via pivot with the following script
This produced the following:
and called CT_itemsPerLocation
The query below was then applied
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.
Below are the transactions for one of the items.
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];
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;
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.
Re: Items Per Storage Location
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Items Per Storage Location
Olaf,
Thanks for steering me in the right direction.
Dennison
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
Re: Items Per Storage Location
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?
Re: Items Per Storage Location
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Items Per Storage Location
Oh my, like all else in AG, works like a charm! Thanks Olaf
Re: Items Per Storage Location
HI,
you are welcome and thank you for reporting back
Olaf
you are welcome and thank you for reporting back
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view