Newbie question anbout linking tables

[Peter Smith]

I am trying to create a web version of an Access database. The database is a discography of music by Elgar. I have succeeded in importing my Access data into sqlite and thence into jam.py. There are tables such as Title and Orchestra, which contain a text field and an index field, plus a master table which contains references to the index fields of the various tables but not the actual data. I have read all the documentation I can find, but I can’t see how to go about linking the tables in jam.py. Grateful for any pointers or assistance.

[Dean D. Babic]

Hi mate, 

and welcome.

In Jam, the fields are linked, not "the table". Linking a table is Access jargon. 

We link tables only for Master-Detail in Jam. Is that what u are after?  I think not at this stage tho.

If you look at online Demo, at "Application Builder":

https://jampyapp.pythonanywhere.com/

you'll see how the tables are linked. For example click on

Catalogs/Tracks table. Do you see all Albums and Genres Lookups?

Or the Docs for v7 version:

https://jampy-docs-v7.readthedocs.io/en/latest/programming/data/lookup_fields.html

Here is a short vid for a Form with tabs:

https://www.youtube.com/watch?v=twoKjU-HV1I

Or how was this built with two tables lookups: https://msaccess.pythonanywhere.com/

https://www.youtube.com/watch?v=Pa0wRvMt1Qk

The Demo app is covering your scenario I think. Let us know.

Dean

[Dean D. Babic]

Also,

I think you have a design issue from Access due to this:

 

"There are tables such as Title and Orchestra, which contain a text field and an index field, plus a master table which contains references to the index fields of the various tables but not the actual data."

Sounds like 2 tables with intermediate 3rd table which contains links to this two tables.

So, of course, you don't see anything at the start! This is due to how Access works.

You need to build UP, for example this 3rd table with a lookups to other tables and fields you like to see.

That will only work if this 3rd table has Primary Key with Auto increment - which is unknown for any tables you've

mentioned.

So when you click "New" it pulls out the needed info from other tables and inserts a new record with a references.
That part Access does for you, but Jam does not if there is no auto increment PK. It can, but that's off topic.

Same with other tables, no PK auto inc, no new record as in Access. The whole point of having a PK is to make it right.

Not the make it "good enough", as with Access.

Jam v7 has utility to "scaffold" the app for you:
https://github.com/jam-py-v5/jam-py/tree/develop/utils

Like:

jam-project.py;  python scaffold_sqlite3.py --db your_db 

(use --help if u like)

[Peter Smith]

Thanks for your prompt replies, Dean. I think I understand in principle how the demo databases work, but I don’t at the moment know which buttons to press on my own database to replicate them. You said that the primary keys need to be autonumber fields, something I haven’t seen mentioned anywhere else. In my tables, the primary keys are the ID fields which are not autonumber. Should I add a new autonumber field to each table and make it the primary key, and if so how do I do it? – the system doesn’t let me change the primary key.

 May I add that it has taken me a long time to get to this stage, with many frustrations along the way. Some of these were no doubt due to my lack of knowledge, but there were things which I couldn’t reasonably have been expected to know: for example, that you have to use cmd.exe in Windows 11, not Powershell, and that jam.py doesn’t run under the latest version of Python. Anyway, having spent so much time on this, I don’t intend to give up now!

[Dean D. Babic]

Have a look in here:

https://github.com/jam-py/jam-py

This version is NOT working under new Python. And Docs are outdated.

But this one is not:

https://jampy-docs-v7.readthedocs.io/en/latest/how_to/how_to_install_on_windows.html

https://groups.google.com/g/jam-py/c/ghxZnVp9_l8/m/7xSU6fbtBgAJ

It is valid for V7, which is the only supported version atm. Use only v7.
So not sure how you stumbled across Jam, but this could be the issue u are having.

Regarding a Primary Key:

https://jampy-application-design-tips.readthedocs.io/en/latest/migration/access.html#id1

Have a look at some Access examples on the left hand side as well. That's how it was done. 

If you start jam-project.py from SCRATCH, all of this issues would go away. 

It creates PK auto increment by default for any table. 

After that, it is super easy to import CSV data into sqlite table from Access "Save as" CSV file. 

Search in here how to. Or google.

Yes, the learning curve is high. This is normal, since the Web development is ultra complex this days.

Luckily, no need to learn massive libraries like jquery and such. Jam covered this really well.

Hope this helps. 
 

[Dean D. Babic]

Can you explain this:

"but I don’t at the moment know which buttons to press on my own database to replicate them" 

You have the sqlite file, correct? With data in it? So can u follow the second YT video I posted above?
It is for Jam v5, but the same procedure is for v7.
Or better, attach here this sqlite file and I will have a look. 

[Peter Smith]

I decided to reinstall everything, this time using Python3.13 and installing imghdr as per the instructions. However before I go through the rather longwinded process of importing my data again, I thought I should let you see my sqlite file in case I need to change anything there. Once again, I’m grateful for your help

[Dean D. Babic]

Here is your app.

Took me 10m mins, including installing Python 3.13, from instructions as above. 

I'll update the Docs with one tiny thing for Win11. Basically, after running python.exe -m venv py313 like this on my laptop:

C:\Users\c12027\Downloads>C:\Users\c12027\AppData\Local\Programs\Python\Python313\python.exe -m venv py313

here we can activate Virtual Environment with just typing below:

C:\Users\c12027\Downloads>py313\Scripts\activate.bat

then proceed with the rest. 

As per instructions for Ms Access, ALL JavaScript names should be compliant:
https://jampy-application-design-tips.readthedocs.io/en/latest/migration/access.html

So in table MasterTable, field name 78 is not compliant - we just rename the NAME to f_78.

Now it is compliant, and the table MasterTable will work as expected.

If we did not do that, the table Import would fail.

Now you can create Lookups for MasterTable. as there are already integers there for other tables.

Then, with a few clicks, we can create a nice tabbed Form, or 3 columns Edit Form, add History, 

add Soft Delete (off topic), Users, Roles, etc.

Easy. When you know how to :)

I'll even throw a freebee for you. Here is the admin.sqlite which u just replace after running jam-project.py

and copying your database into the folder.

Everything just works.

You own me a pint of beer :)

[Dean D. Babic]

So now we can do cool stuff:

[Peter Smith]

Thanks for this, and I’m sure I’ll owe you more than one pint by the time I’m finished!

 I have hit two issues, possibly related.  Firstly, when I run server.py I get this message:

 WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.

 * Running on all addresses (0.0.0.0)

 * Running on http://127.0.0.1:8080

 * Running on http://192.168.0.117:8080

Press CTRL+C to quit

 The application builder still opens, but when I attempt to import tables, no tables are shown.

[Dean D. Babic]

If searching for import, this shows up: 

https://jampy-docs-v7.readthedocs.io/en/latest/admin/integration_with_existing_database.html

Just copy admin.sqlite which I posted in here into the folder together with your DB file.

Re:

" WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead."

- is normal. 
There are no issues. Just lack of experience I'm afraid. Similar to Django or any Python project:
https://docs.djangoproject.com/en/5.2/intro/tutorial01/#creating-a-project

[Peter Smith]

Sorry to be a pain, but are you sure you sent the right admin.sqlite file? The application window is showing  "Jam.py Demo Application". I tried to delete the common tables (is that the right terminology?) but it won't let me do so. Possible becauseof that, there are still no tables to import.

[Dean D. Babic]

Yes, it is the correct file, have a look at the commands below. I downloaded admin.sqlite.
I do not know why there are TWO ElgarDiscog.sqlite and ElgarDiscog.db downloaded when I click on your file.

Obviously, only ElgarDiscog.db is used.  If using ElgarDiscog.sqlite, it wont work, unless DB name is changed on F4.

No, it is not because of that.

The process does not mention deleting any common tables.

To help you out, have a look how simple that is to do from scratch, like 3 mins for 2 tables:
https://www.youtube.com/watch?v=twoKjU-HV1I

The important part is on 36 sec - "DB Manual Mode"

[Dean D. Babic]

Here is a new admin.sqlite

With nice Dashboards as well to start with. Replace templates.html with this one for Dash.

BTW, Chart.min.js should be placed in static folder, copy it from Demo. Or leave the online one

in index.html - then Dash won't work when offline! But this is off topic for now.

This was all done under 1 hr - when you know how to :)

The important take out from this is ChatGPT discussion how to remove undefined data from Dash:
https://chatgpt.com/share/68a7e173-08c0-8012-8a52-758b21843be9

Because there are so many undefined values!
After updating all captions and views, it will be a very polished app, indeed a professional one imo.

Anyone can download stuff from here and spin up this app. 
Logging off...