First release of Northwind Traders from MS Access

259 views
Skip to first unread message

Drazen D. Babic

unread,
Dec 6, 2022, 9:08:19 PM12/6/22
to Jam.py Users Mailing List
Hi all,

here is the first release of migrated App from Access:


It can be directly compared  with the Desktop app.

Not finished yet,  and thanks to Danijel who helped.

The Products are showing multiple Suppliers name, 
hence a bit of delay in rendering. It is very fast on my PC.

The Export with the DB is there. The App has no users passwords atm,
since this is how Desktop App is designed.

D.


Drazen D. Babic

unread,
Dec 7, 2022, 9:07:11 PM12/7/22
to Jam.py Users Mailing List
Some explanations how the Northwind Traders Access App works, and what is still needed:
https://answers.microsoft.com/en-us/msoffice/forum/all/northwind-traders-template-how-is-on-hand/4cd99f2f-c391-493d-99eb-7ce2cebb8c51



The Inventory List is the most important, since without it the App is not useful. So I did have a look at Access queries,
and will try to implement. 

 Ultimately, the Inventory List should look exactly like on the Pic. Thanks
 
Inv05.png
Inv04.png
NOrthwind02.png
Inv01.png
Inv06.png
Inv02.png
Inv03.png

Drazen D. Babic

unread,
Dec 7, 2022, 9:55:48 PM12/7/22
to Jam.py Users Mailing List
Here is the complete MS Access SQL query for the Inventory List! Very complex stuff but we will get there!

Inventory query:
SELECT Products.ID AS [Product ID], Products.[Product Name], Products.[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz([Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level], Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level], IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To Reorder]
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold].[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID = [Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON Products.ID = [Products On Back Order].[Product ID];

So looks like it needs Inventory Sold query:

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Sold]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=2))
GROUP BY [Inventory Transactions].[Product ID];

and then Inventory Purchased:
SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Purchased]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=1))
GROUP BY [Inventory Transactions].[Product ID];

Than INventory on Order:
SELECT [Purchase Order Details].[Product ID] AS [Product ID], Sum([Purchase Order Details].Quantity) AS [Quantity On Order]
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Posted To Inventory])=False))
GROUP BY [Purchase Order Details].[Product ID];

And Inventory on Hold:
SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity On Hold]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=3))
GROUP BY [Inventory Transactions].[Product ID];

Finally, it need Products on Back order:
SELECT [Order Details].[Product ID] AS [Product ID], Sum([Order Details].Quantity) AS [Quantity On Back Order]
FROM [Order Details]
WHERE ((([Order Details].[Status ID])=4))
GROUP BY [Order Details].[Product ID]; 

Drazen D. Babic

unread,
Dec 9, 2022, 5:24:13 AM12/9/22
to Jam.py Users Mailing List
Hmm,
not sure how to solve this, the most of the data is in ONE single table, Inventory Transactions, which has 1,2 or 3 as criteria for calculating EACH Quantity:
  
WHERE ((([Inventory Transactions].[Transaction Type])=2))
WHERE ((([Inventory Transactions].[Transaction Type])=1))
WHERE ((([Inventory Transactions].[Transaction Type])=3))

So Quantity for Type=1 is different, that is Purchased Quantity, Type=2 is Sold Quantity, so we need to Extract Sold from Purchased 
That gives us "QTY on Hand", and Type=3 is "QTY on Hold" and Hand Minus Hold = "QTY Available" 
"Quantity On Order" and "Quantity On Back Order" is not affecting QTY Available.
It would be exactly the same for the Report, but this Virtual Table is used to click on "Purchase" button when "QTY Available" is ZERO.

Andrew is the Warehousing master, I hope he will find some time...
REgards
D.


Danijel Kaurin

unread,
Dec 11, 2022, 3:59:26 AM12/11/22
to Jam.py Users Mailing List
Hi Dražen.

You can try with custom sql query:

def on_open(item, params):
    connection = item.task.connect()
    cursor = connection.cursor()
    query = 'SELECT * FROM "demo_users"''
    cursor.execute(query)
    rows = cursor.fetchall()
    result = []
    #print(query)
    #print(result)

Regards

Drazen D. Babic

unread,
Dec 12, 2022, 12:03:47 AM12/12/22
to Jam.py Users Mailing List
Thanks Danijel,
SQLite3 is not using ALIASES, hence no SQL arithmetic in select clause.

Plus, Nz and IIf should be ported to sqlite3.
And then this sql would not work on some other DB's. Hence, not portable :(

However, I think set_where is portable. As seen, it is getting some data, I just don't know how to get all :)
I think it is a child play for the Boss, will see.

Drazen D. Babic

unread,
Dec 13, 2022, 3:47:43 AM12/13/22
to Jam.py Users Mailing List
All done!

To compare how MS Access Inventory List looks like, here is the Access screenshot which is the same as on 

It is not the most elegant or efficient way but it works:) Need to add "Qty Available" and reports.
The Export is there with the DB if someone would like to have a look!

So, to conclude, this App has absolutely no SQL. The only JS code is for some tables, and Python side for Inv list.
The data was directly moved to Jam with our utility. 
After that, just a Lookup List was created and some Primary Keys for below tables:
Inventory_Transaction_Types Order_Details_Status Orders_Status Orders_Tax_Status Purchase_Order_Status 


Did not test much since not finished yet. 

D.

Screenshot from 2022-12-13 13-39-30.png 

Drazen D. Babic

unread,
Dec 15, 2022, 1:52:47 AM12/15/22
to Jam.py Users Mailing List
Started testing it. A few bugs, some smaller some big :)
The biggest obstacle is splitting the table into two, for example Purchase Orders has Payment Type, which is Cash, Check, etc.
This should go into a Lookup List (or a table), but for that, the field must be INT, and now is TEXT.

If this is changed to a Lookup, that breaks data import (not Export zip!) to MySQL, due to:
.
INFO - copying table purchase_orders records: 29
ERROR - invalid literal for int() with base 10: 'Check'
Traceback (most recent call last):
.
copy_rows
    r[j] = int(r[j])
ValueError: invalid literal for int() with base 10: 'Check'

Because it is still 'Check' in the table. 
One solution is this:
table2.open(open_empty=True)
for t in table1:
    table2.append()
    t.id.value = table2.id.value
    t. PaymentType .value = table2. PaymentType .value
    table2.post()    
table2.apply()

However, this is not the best because it leaves the data in table1, which is redundant.

Drazen D. Babic

unread,
Jan 6, 2023, 1:57:28 AM1/6/23
to Jam.py Users Mailing List
Hello all,

I implemented Fabio's SQL ( https://groups.google.com/g/jam-py/c/6yx7c724OtM), instead of Python loop
on Server side for virtual table.

The Inventory List is quite fast and the Export is available to see how it's implemented.
Next step would be to finish the logic for buttons (for Purchase, Approve, etc.)

Cheers

D.

Drazen D. Babic

unread,
Jan 6, 2023, 9:18:17 PM1/6/23
to Jam.py Users Mailing List
All good, I figured what to do!

Dean D. Babic

unread,
Mar 24, 2023, 1:04:48 AM3/24/23
to Jam.py Users Mailing List
Sorry for spamming :)

Jam.py is now listed on the first google page results when searching for "Moving MS Access to Web"!!!

Screenshot from 2023-03-24 12-57-32.png

Dean D. Babic

unread,
Jul 9, 2023, 8:06:15 AM7/9/23
to Jam.py Users Mailing List
Hi all, 

the new release is up in here:

And it is getting very close to the Access desktop App.
Thanks to Danijel Kaurin, who implemented the new "Home Page" and most of the backhand stuff!

Access_home.png

The "Inventory to Reorder" is a Virtual Table. The "Active Orders" is a table.
"Quick Links" is a code in Virtual Table "Home Page" in catalogs, and it is shown first.  
The "Send Mail" is used on Employees, Shippers and Customers, and pulling email address from the field. 
That is all for now.

Enjoy

Dean D. Babic

unread,
Apr 15, 2024, 9:57:20 AMApr 15
to Jam.py Users Mailing List
Hi all, 

it has bee a while for an update on this app.
Today, I finally tested the application and managed to Pump >5 000 000 records!
Then did the Analytics RFM pandas, numpy and matplotlib test after I updated the Python code.

Here is the result:

sqlite3 northwind.sqlite3

SQLite version 3.43.2 2023-10-10 13:08:14

Enter ".help" for usage hints.

sqlite> SELECT count(*) FROM products p LEFT JOIN order_details o ON p.id=o.product_id WHERE Date_Allocated is not NULL and Date_Allocated > '2008-01-01' and TOTAL_PRICE is not NULL;

4939745

Now the RFM Pandas test which pulls all 5mil rows into the memory to present the result in less than a minute:

127.0.0.1 - - [15/Apr/2024 21:43:19] "POST /api HTTP/1.1" 200 -

RFM Started

Plotted

127.0.0.1 - - [15/Apr/2024 21:44:11] "POST /api HTTP/1.1" 200 -

This is actually phenomenal! 

Cheers

Dean D. Babic

unread,
Jul 2, 2024, 7:07:41 AMJul 2
to Jam.py Users Mailing List
Hi all, 

this is the V7 NorthWind Traders preview:


Comparison with v5:

Everything works as expected, except the password change slider.
The css was touched for Status ID on Active Orders.

The only table that is not read only is the API CALL.
It is fetching data from the Account Transactions App summary.
So when someone changes the data there, the summary would change.
This is how Jam can fetch some data from any site.
Note, the Help icon (?) has changed on this table with v7.

The biggest obstacle to migrate to v7 were the templates. 

Also, I'm trying to fix dependencies so when the export is downloaded,
the dependencies should install automatically. Will see how that goes.

I also released new v7 today, which fixed group_by issue (gh321),
and some minor stuff.

The feel and look on mobile devices is now much nicer.

D.
Reply all
Reply to author
Forward
0 new messages