Newbie questions about forms

[Peter Smith]

Thanks to Dean’s help, I have now correctly imported my Access data into jam.py, and am starting to grapple with forms. I have scoured the documentation, but I cannot find answers to the following:

1.     Bands: the Application Builder allows me to create bands, but does not allow me to do anything with them – I cannot format them or move fields into them. How is this supposed to work?

2.     How do I concatenate two fields for display? For example, if FirstName = “Peter” and LastName = “Smith”, how do I get the form to display “Peter Smith”?

3.     How do I add a second or subsequent form to a table?

I would like to use as little code as possible, though I realise that at some point I may have to dive into the murky world of Java!

All help appreciated.

[Dean D. Babic]

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

on 2.20 minute and before or see #2. Its dead simple.

2. use this admin.sqlite

Code is needed for that. A lot of it for the app, b/c there are a LOT of fields. You'll see it in my admin.

3. explain pls what do you mean by that?

Again, just replace files with this ones. All questions will be answered.
One thing to take from ElgarDiscog.db is NULL for the fields.

To have this "" (blank), is not the same as NULL. I fixed it for you, to get 

the proper Dashoard. Without NULL, it is impossible to do any sorting.

It is just JavaScript, not Java :) Plus, it is just copy/paste most of the time from Demo!
With slight changes to reflect the fields and table name. But, knowing logic is the must.
And visiting Demo is also a must to simply get familiar with what is what. Invoices has the exact
answer for your question:
https://jampy-application-design-tips.readthedocs.io/en/latest/howDemo/build.html#a-little-code

look for on_field_get_text

So just like before, create a folder and:

1. jam-project.py

2. copy admin.sqlite templates.html ElgarDiscog.db into here
3. start server.py
4. visit the app
Then start replicating my efforts in your old app, which you start as:
server 8081
and visit app on 12.0.0.1:8081

D.

[Peter Smith]

Thanks, Dean. Re 3, as I understand it each table has one form associated with it. If I want to base two forms on the same table, what steps do I need to take to add another? I want to create one form which will list multiple entries showing only certain fields, and another which will show all the data for one title on one page. 

[Peter Smith]

I followed your instructions:

1. jam-project.py

2. copy admin.sqlite templates.html ElgarDiscog.db into here
3. start server.py
4. visit the app
Then start replicating my efforts in your old app, which you start as:
server 8081
and visit app on 12.0.0.1:8081

then in Application Builder I  re-imported the tables and looked at the app. I can't see anything different from my version. Sorry if I'm being obtuse.

[Dean D. Babic]

Web can not replicate the exact Access Forms AND the Forms flow.

Not with the low-code approach, tho.

The Demo demonstrates what the expect. Your app, if you decide to use it,

already HAS 13 Forms on the Master Table! Plus subsequent entries! See the magnifier icon?
That is the form. On this MasterTable, more info can be added, which can be in Tabs AND Bands.
Totally up to you. Plus, the Details can be added too.

So if you are into tweaking this approach into something else, then youll need to be very very specific with 

the requirements. 

If a TOTALLY different form is needed, but the same table structure, than copy existing one and 

make a new changes. Do you see the Copy icon near New and Edit?

The assumption is not correct, Jam can have any number of Forms for one table.

The question is, why would you need it? What problem you are solving with two Forms which 

cannot be solved with one?

May I see your Access forms? Take a screenshot or a video. Then I can tell if is possible or not.
I don't have Access to try anything.

Please record the video with your doing this procedure:

1. jam-project.py

2. copy admin.sqlite templates.html ElgarDiscog.db into here
3. start server.py
4. visit the app

I just don't understand how is this possible? Here is how app looks like!
It is jam-py-v7 

[Dean D. Babic]

Ok, 

What are you doing differently than what's on this vid?

[Dean D. Babic]

Here is the newest admin.sqlite

[Dean D. Babic]

Here is the Export file:
https://drive.google.com/file/d/1FEE6U6KO5zYDu2vnAgqIu3R0Uw5evXl9/view?usp=drive_link

1. jam-project
2. server
3. fill details with ElgarDiscog.db (the db is in the zip)
4. Click Import for this zip
DO NOT UNZIP THIS FILE!
This is the standard way to send Jam apps...

[Peter Smith]

Right, well the problem, or at least one of them, is that I'm running jam.py 5.4.136

As far as I remember, I followed the instructions on this page

https://jam-py.com/docs/intro/install.html

and entered pip install jam.py

and that's what I got! How can I install v7?

I'm attaching screenshots of my Access forms - there are actually three including a search form.

Thanks, as ever.

[Dean D. Babic]

Right.

https://jam-py.com/ has changed, just visit it and you'll know...

[Dean D. Babic]

Peter, 

you have the same data for conductors and choir! 

Have a look at the Filter option. This is your form for search. On builder, click on MasterTable and Filters, add it there.

Re your Access app, I think it is for viewing and Jam is for CRUD - create/update/delete. Sure, we can make all fields read/only.
But then, no usage for New,Delete and Edit button.

[Peter Smith]

Thanks. Yes, I had spotted the error in the choirs table and have now corrected it. The bit I hadn't got round to doing in Access is an input form to allow new entries to be made and existing rescords to be amended. So I do need CRUD!  Tied up today, but will resume tomorrow.

[Dean D. Babic]

No worries.
I also think that there is a problem with the MasterTable. 

See the Chat:

https://chatgpt.com/share/68aff5cc-59b8-8012-9844-1fef91746ad6

REFERENCES are the lookups in Jam.

Basically, there are NULLs everywhere. 
There is an option in Access called "Analyze Table" on DB Tools. It should show this after
running.

Here is the reason why this is bad: it is impossible to know which soloist is in which MasterTable record.

Not because the info is not there! But because it is all over the place (sol1, sol2 ...), so 
we would need multiple filters to cover all this fields! Which is ugly. And this is needed for all other fields.

As mentioned, it might work for Access, but not for Rational Databases. Thats visible on the 
Access search screenshot, where it might work. Or not, not sure.

That is the primary reason why many Access to Web migrations fail.

As it is now is not to bad, imo. It somewhat works as a concept.
I've spent quite some time on this, just realizing now where the main issue is...

D.

[Dean D. Babic]

This is now getting very interesting with ChatGPT:

Me:
" Hi, I think the original MasterTable idea is because how soloist apeared on the concert. The soloist number one, soloist number two etc. This is the reason why there is solo1ID, solo2ID, etc."

Him/her/it:

"Ahh, got it 👍 — so the ordering of soloists was baked into the schema.
That actually makes sense in music/concert databases: you often want to record not just who performed, but in which order/role they appeared (e.g., “first soloist = soprano, second soloist = tenor…”).

The mistake in MasterTable is storing that order by having separate columns (Solo1ID, Solo2ID …), which locks you to 6 and makes queries ugly."

...
The question for you Pete is this:
" 👉 Do you want me to sketch the SQL migration that would take existing Solo1ID–Solo6ID columns from MasterTable and automatically populate this RecordingSoloist table?"

:)  

[Peter Smith]

Thanks for spending time on this, Dean. By way of background, the data comes originally from Excel, and has been built up by an enthusiast over something like 50 years. It was full of inconsistencies, many of which I sorted out, but I decided that the date information was better left as free text – sometimes it is a full date, sometimes just a month, sometimes more than one date. I’m not sure what ChatGPT’s point about the 78, Tape etc field is; I think they are fine as simple free text. At some stage I was planning to add a free text search facility which would enable someone looking for, say, a particular record catalogue number to find it.

 In Access I have a master query based on the master table which pulls in all the data from the other tables. That provides the data for the search form, which is then filtered to provide the data requested by the user, initially as a summary list, and then if required the full data for one record. That is what I was trying to replicate in jam.py. If there’s a better way to do it, I’m very happy to learn it.

 Re the soloists, the order in which they appear is not particularly important, though there is a convention that higher voices and instruments come first (soprano-alto-tenor etc, or violin‑viola-cello etc) . I created six fields because that’s the highest number of soloists for any one piece in the database. I was aware that this was a bit clunky, but it works OK in Access. I have however hit a problem related to this in jam.py in trying to get the master table to include the voice_instrument field. (The data for voice/instrument is incomplete. I could add quite a bit to it from my own knowledge, but at this stage my aim is simply to get the existing data into a working online database.)

 So yes, if you can show me a better way to handle the soloist fields, that would be appreciated.

[Peter Smith]

One specific question: I asked a while back about concatenating FirstName and Surname. I copied the code from Nick Antonaccio’s Calculations video and amended it to concatenate firstname and surname in the conductor table. However it doesn’t work: the fullname field remains obstinately blank.

 The code I came up with was this:

 function calc(item) {
    item.fullname.value = item.confirstname.value + " " + item.consurname.value;
}

 function on_field_changed(field) {
    var item = field.owner;
    calc(item);

I copied it into condtable.js.

What have I done wrong?

[Dean D. Babic]

I would suggest to sneak peek at my code, which is provided in admin.sqlite!

This is complex for me to explain to each new user, that's why Ive written the Doc:
https://jampy-application-design-tips.readthedocs.io/en/latest/howDemo/build.html#a-little-code

The function calc has nothing to do with concatenated fields.

For this App, the minimum code possible is only for displaying concatenated fields.
And a bit of formatting - not really needed.  Not going into Dashs, since off topic.

But for a newbie this is a hell lot to take! The database is designed with two

values for the name, so it makes no sense to concatenate that info on supporting table! Which is a condtable at.al.

But it makes absolute sense to concatenate on MasterTable!
Have a look at the pic! There is a LOT to concatenate and ALL is a lookup to a different tables. 
But, you need to know how to make this lookups. The MasterTable has now 40 fields. And half are lookups,

and not finished yet.

That is what Jam enables you to do - minimum code. But...

... to explain all of this, it would be an essay! 

Just replace admin again with this one and have a go at what I did. It is almost all done.

[Dean D. Babic]

Sorry, posted wrong pic...

Here is the one you'll get with latest admin.sqlite Plus the Filter for Conductor. Add as many Filters you need.

[Peter Smith]

Thanks, Dean. Two issues: firstly I never sent you a corrected ElgarDiscog.db file so the error witht the choir table is still there. Correct version attached. Secondly I have copied your admin.sqlite file into my project folder, but I don't get the layout shown in your picture - just the usual columns. 

[Dean D. Babic]

Hi,

youll need to record a video
with all steps u are doing Im afraid...

This alll should be a breeeze imo

D.

[Peter Smith]

Video attached.

[Dean D. Babic]

Peter, 

sorry for probably stupid question, but do you know how to use the app?
Like, 

did you actually see any Jam.py Demo apps in action? Like this one:
https://northwind.pythonanywhere.com/

or:

https://msaccess.pythonanywhere.com/

This apps have zillion visitors!

Long story short, click on bloody row mate! :) Any ROW!

[Dean D. Babic]

Plus, you are still on 5.4.136 version! Which is unsupported, have a look:
https://jam-py.com/

Install v7 as per:

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

D.

[Peter Smith]

This is a good example of something which is blindingly obvious to the expert but not to the newcomer. Yes, I have looked at the demos, and no, I didn’t deduce that I needed to double-click (not click) on any line to open the edit form. Indeed,  I hadn’t realised that your changes applied to the edit form, since what I am trying to build at this stage is a read-only form. Is there a way to turn the edit form into a view form?

 I installed version 7 so I don’t know why I’m still running v5 (how can you tell, by the way?).

[Dean D. Babic]

The Jam version is at the top right corner. 

Back to this:

"Re your Access app, I think it is for viewing and Jam is for CRUD - create/update/delete. Sure, we can make all fields read/only.
But then, no usage for New,Delete and Edit button."

So below question:
"Is there a way to turn the edit form into a view form?" is answered in my comment.

However, one could run the app with Authentication turned on, so Demo user/password would have a read only access.

The same would be with MS Access - impossible to expect read only and read write on the same app
with no authentication. But then, with auth, no one would use your app :) So it's a catch 22.
You would need a paid consultant to solve this and deploy it for you. And agree on the specific so

you both are on the same page.

Because up to now, you didn't really touch base on what your idea with the App is, for us to 

be able to advice whats possible and what not...

Hope that helped.

[Dean D. Babic]

To help you even more, 

it is absolutely possible to run TWO applications on the same database!
One can be read only, and the other can be read write. Of course, you need to make a read only fields.

And share the same database. See the pics with in red color.

As per Docs, to start the second app is:

server 8081

from a different folder that is. You cant run two apps from the same folder to have read only app.

Just copy everything to this folder.

I think this concludes pretty much everything. Follow the install for Windows and u should be ok.

[Peter Smith]

Just to clarify. the intention is that a read-only database will be published on the web for anyone to use. There will also need to be a write access only available to administrators to allow updates and corrections. I guess this is pretty standard. Thanks for all  your help.

[Peter Smith]

Having installed v7 again I now find that the application window is completely blank (on both /127.0.0.1:8080 and 192.168.0.117:8080). The builder window displays as usual.

[Dean D. Babic]

I don't know man. One video or even a pic could provide more info.

I'm not sure Jam is a good fit for your requirements tho. 
Jam is not suitable for public sites, just like MS Access is not.

[Peter Smith]

I have gone back to v5 and the problem has disappeared.

Why is jam.py not suitable for public sites?

[Dean D. Babic]

Because as I mentioned, read only cant be enforced that easily.

Jam is not for displaying, but for pure CRUD.