MS Access migration

Congratulations on the decision to migrate Access to the Web!

After all, it is moving to the modern JavaScript with Bootstrap and jQuery technology Front End! Which is powered by Python.

Jam.py is not exclusively developed for Access migration though. Just like Django (Python) or CodeIgniter (PHP), is not. Both mentioned products are not specialised for the databases oriented applications, even though one could migrate Access to any of those.

However, Jam.py is specialised. Just like Access is.

Before considering migrating Access (or any proprietary software), to the Web with the help of Jam.py, please review some answers below. Which might help with understanding what to expect from migration to Jam.py.

Top Migration Questions

The MS Access might be a bread and butter for many developers out there. Particularly in some specific industries like the Law practices or similar. And fair enough. It is the right tool for the job.

However, the risk is that in some years to come, the developers will retire. I know, I’m one of them. Hence, where will the business find people to maintain their expensive application? Maybe they already hired the MSSQL database administrator, since the Access developers told them to do so. The DBA’s might be around in years to come, but the Access developers? Or better, THE Access itself?

Hence, the faster we move away from proprietary database AND Front End application, the better. Simply because the technology will be around way after the MS Access is gone. This is business after all, let’s put aside the emotions. The HTML is here to stay. Who remembers the Netscape any more? Or Mosaic?

1. Complacency

Success breeds complacency. Complacency breeds failure. Only the paranoid survive. Andy Grove

About being paranoid, many MS Access Front End applications are not encrypted! This means the IP (Intellectual Property), is not protected. Even worse! The VBA code might contain email passwords or similar. For example: https://wellsr.com/vba/2020/excel/vba-send-email-with-gmail/

Is your application cyber safety compliant?

The application might be compliant for some business, however does it run on the Web? Apple Mac? Tablet? That is exactly what we are also addressing with the migration. The Future with the IP and Cyber Safety.

2. VBA?

Does your application rely on heaps of VBA for Business Rules (BR)?

The VBA can’t be migrated to the Web. The question is what is it used for? 5000 lines of VBA code might be a dinosaur code! Is it possible to use Python with 200 lines of code? How about 10 lines?

In addition, what might not be recognised is the speed. The VBA just does not compare with Python. The Developers will argue that this is due to the quality of code. Not quite true. The code is just as important as garbage collection, or utilising memory, etc. And this is where Python shines. It replaces massive technological debt with a few lines of code.

For example, the BR might be a way the application authenticates users (ie. a table with username, password, role). Jam.py can reuse the Access tables used for users, so no issues with that. When the database is migrated, the users authentication table is there and we can use exactly twelve lines of Python code, as seen on Demo, to authenticate users with the roles.

Also worth mentioning VBA to Python – 10 Simple Python vs VBA Examples. The article touches on some differences and interesting points from a seasoned VBA developer. However, for building the Jam.py application, JavaScript is used as a Front End. Hence, the real difference is between JS and VBA. For example, showing the MessageBox or conditional formatting and functions.

On that note, the above article touches on VBA code for MessageBox:

Sub MessageBox()
    'Information box
    MsgBox "Hello", vbInformation, "Information"
    'Yes / No question
    If MsgBox("Do you like this tutorial?", vbYesNo, "Question") = vbYes Then
        Debug.Print "They like it!"
    Else
        Debug.Print "They don't like it!"
    End If
End Sub

In JavaScript, Jam.py does that in a similar way:

var btn = item.add_view_button('Set invoice paid', {type: 'primary', btn_id: 'paid-btn'});
    btn.click(function() {
        item.question('Was the invoice paid?', function () {
            item.edit();
            item.paid.value = true;
            item.post();
            item.apply(true);
        });
    });

The code above actually does more then displaying the MsgBox! It will update all selected records in one go. Imagine writing the VBA code for the same task.

The MessageBox with JS in Jam.py is something like this:

item.alert('Successfully sent the mail');

The button in Jam.py is something like this:

var btn = item.add_view_button('Set invoice paid', {type: 'primary', btn_id: 'paid-btn'});
    btn.click(function() {
        -- do something here --
        });

3. Excel?

Does your application depends on Excel or some other Office products?

This might be a show stopper, simply because the organisation will not let go of Excel. This might be particularly true within some Financial institution. The good news is Python can read/write Excel document formats. For sure Access has better interoperability with those products. It is the Desktop application after all. However, some operations within Access using Excel, might take a long time, sucking up the resources. Not so with Python and Excel. It is super easy to integrate Excel with Jam.py application using DataTables.

Good resource for using Excel with Python.

4. Queries

Most of the “applications” consist of a single hard-coded query or a single linked table.

The above is a quote from Front-End for MS Access migration? It is an old thread with all points still valid. With Jam.py, one does not write queries. It is absolutely possible to do so, if needed.

It is a no-code, low-code or more-code RAD framework after all. And that can open the whole new world for a group of developers! Because each of them can work simultaneously on some other part of application, being the Forms or the Server procedures, everything is instantly accessible to everyone with the right privileges.

Hence, everything is simplified. The Jam.py framework will build complex queries just like Access does, with a few clicks of the mouse. Contrary to the Access, this query, in fact the complete application, will work on any supported database. Develop the application and deploy it to preferred database. Simple. No need to rewrite the queries.

5. Primary Keys

Be assured, there are a number of applications out there with no Primary Keys in Access.

The Primary Key (PK), is a must with all relational databases. With no PK, Jam.py cannot reference the Foreign Keys (FK). Not only relevant to Jam.py, Django does not do that either. Since both are sharing similar ORM. So no matter how we migrate Access to the Web, with which technology, the PK simply must exist. This is even true with Access web applications and web databases.

6. Deployment

If possible, have the front-ends copied locally on each workstation, for performance reasons.

Again, the same source Front-End for MS Access migration? See how nothing really changed from year 2008?

Some Access Developers would argue that RDP or Citrix or even VPN is the way to deploy the Front End application to the Web. All this technology was around in 2008. Nothing new.

What is new is the Cloud.

Back to topic. Regarding the Access packaging, some people are using one distributable file, sometimes protected with the encryption. Jam.py can do exactly the same. Even more, one can use SQLCipher to protect the IP. Combining both within a portable application, which can run from, for example Windows x64 with no installation at all, is a powerful deployment and secure method. With such approach, the application database might be off-site or even embedded and encrypted with SQLCipher. This method can provide safe and secure access even from a portable drive in an off-line environment, for example with no Internet access at all. Only the local area network is needed for more then one user.

Regarding Web servers deployment, Jam.py application can be deployed in a few minutes on almost any popular Web server. Which can serve thousands of users simultaneously. Jam.py flagship application is supporting 2000 remote locations, each with 10-20 users. There is no need for installation of anything and costs almost nothing.

Hope the above sparked some interest. Even though there are probably more reasons why one should or should not try to migrate Access application to the Web. With or without using the migration tool as below is providing.

Where from here?

To successfully migrate MS Access to Jam.py, the assumption is that at least some knowledge exist with using the Jam.py Application Builder interface. Or, if feeling confident, one can start creating everything from scratch, and then load the data into the tables manually. We think that the below procedure is way more faster method. And, it does not cost anything.

If agreed, please visit:

https://jampyapplicationbuilder.com/conv/

Depending on file size, the conversion might take a few moments! Please wait to see: File is successfully converted! Access Application with your data from here

After the Access file is uploaded and converted with no errors:

  • link to Application Builder will show up, which is using the sqlite3 data converted from Access instantly available on the Web! It is super easy and takes no time! If not interested at all in building the FrontEnd Application, please click on Export to download the DB as zip file which contains the sqlite3 file. The converted sqlite3 database has a long name exactly the same as in provided the link. This is to increase safety and security for all downloads.

Note

If interested in instantly showing data on the Web, meaning using Jam.py for the Front End, please continue with below:

The Application Builder from the provided link will set the Database to “DB manual mode”. This means your new database is safe from changing the structure, like incidentally deleting the table or similar. If you would like to continue migrating Access to Web with the help of Jam.py framework, import all tables into the “Test Import” Group (click on Test Import/Import button), taking care of all needed DB fields correction or missing info. All fields must have a datatype if not already recognized. The “DB field name” should not be touched, the “Caption” is normally MS Access field caption, and the “Name” must be valid JavaScript naming standard, in short, no spaces, special characters, etc. To help out with the process, here are some videos:

Video overview - Jam.py VS MS Access

Video tutorial - How to publish MS Access Application to Web using Jam.py and SQLite3

Video tutorial - Simple CRM built automatically with Selenium IDE in 2.45minutes, demonstrates the Tutorial. Part 1. First project

New style Demo - Jam.py Demo Application (expiry on 04 Jan 2023)

Personal Account Ledger - Migrated from MS Access template (expiry on 04 Jan 2023)

After Importing some tables, just replace builder.html with index.html from the link to access your Application. Sometimes the index.html page will not show anything due to Load Balancer issues. Please try after some time. Or start over. One can even set the App password or create users within the Builder. The default App/Builder username/password is admin/111, change it on Project/Users and set Project/Parameters/Safe Mode to restrict access, etc. One can set all lookups to different tables as usual. It should be a fully functional App with all the bells and whistles!

It is important to note that once the Access file is converted and all tables Imported through the builder.html, it is not needed to use this service at all. Immediately use the Export [CTRL+E] option and download the Export file. Then, install the Python Jam.py framework locally or on the server, create the New application, Import the same downloaded file, and point the Application to sqlite database as per creating the New application. That is all.

Notes/Issues

This tool is for the database tables only, it does not convert MS Access file with no tables in it. Hence, no need to upload a Front End application, which probably connects to SQL Server or some other database(s). Output will be empty, or it will contain only a few tables.

The process is expecting that the Primary Key exists on all tables. Please address and fix this issue before attempting the conversion. Only Use One Numeric Field as the Primary Key, ie. AutoNumber field.

MS Access might have a special character, space, slash/backslash, dash (-),quote(‘), etc for the table field, as well as table name. The conversion will try to replace those, however, we would advise to fix the issues in MS Access, before attempting the conversion.

The conversion will fail if there are non-ODBC linked tables in MS Access. Please remove any linked tables before attempting the conversion. The ODBC linked tables are supported.

The conversion will fail if Access is using utf-16-li, no need to try again. No log or link will be produced and we suggest fixing Access.

There might be an #Error in the Access field, please fix it if conversion fails, and attempt again. The best is to inspect the Log file, sometimes clearly showing the error. If the log file is empty, something went wrong. No need to try again if Access wasn’t fixed.

Please do not upload confidential, private or password protected/encrypted database. If conversion to SQLite3 is needed for such data, the tool can be provided on demand. Password protected database conversion might fail, no need to try again.

Terms and Conditions

“This site and its components are offered for informational purposes only; this site shall not be responsible or liable for the accuracy, usefulness or availability of any information transmitted or made available via the site, and shall not be responsible or liable for any error or omissions in that information.”

Now what?

First of all, thanks for trying the migration!

Now that the database is converted to SQLite3, let’s see what we can do with it. The migration utility just speeds up the process of getting the data on the net. It does not build the Front End Application, even though we could easily automate this step.

But then, the Front End look and feel is subjective. If replicating the exact MS Access form as close as possible, it really takes no time to do it manually.

Before doing that, the best is to have a look at the MS Access Relational Diagram. It should clearly show the relations between the tables, as seen on the Chinook database.

For Access application with no Relation Diagram within it, it is still possible to look at the queries design. Each query would probably mean some sort of relation between two or more tables. Hence, in Jam.py, it is possible to create the Lookup fields to other tables just like MS Access does.

Tables

In order to use the automatic displaying of data (automatically with CRUD), the table name is used as table Caption! That is, if the Caption was not changed during the table Import.

Hence, if the database table name is django_ledger_accountmodel, then the JavaScript variable Name is also django_ledger_accountmodel! And more importantly, the visible table Caption on the Application is django_ledger_accountmodel as well.

So maybe better to set the table Caption to Account, Bank, etc. See how nicely everything looks like on the screenshot below?

This is also true for any table column during the table Import.

Finally, the application might look like this:

Django Ledger

The MS Access linked tables (by ODBC at.al), are now possible to import. There are some constraints though, not all datatypes can be recognised due to the ODBC connection. Of course, the MS Access linked tables are just the issue when using the conversion tool provided above. One can always connect to MSSQL server directly from Jam.py Application Builder and Import the tables.

Using UTF8

The above is all well and true for the English language. Or the Latin alphabet. However, using UTF8 in MS Access is supported, but not supported in JavaScript naming standard. Unfortunately, this is directly impacting Jam.py tables Import usability.

Automatic translation to comply with JavaScript naming standard

Because of that reason, when using our site for the migration, all non-Latin alphabet is translated to JavaScript naming standard automatically. The below notes are valid for official Jam.py installation downloaded from the Internet, which does not support the automatic translation yet.

Consider this scenario:

Italian Access Table

The Table name is 00_COSTES ALFONSO. And the list of the tables continues with 01_, 02_, 03_, etc. Unfortunately, it is also using this as a table name: 00_PRECIOS ICM > GENOCOL_IMAT.

Which just proves that MS Access will accept anything we throw on it. Is this a good practice? It is for Access tho. However, any modern framework will reject this practise. Including Jam.py. Sorry about that, it is what it is. Not Jam.py fault.

Importing the above table will produce the below:

Italian jam.py Table

The table Import as such will fail. It cannot be imported, and the problems needs to be fixed first.

Reviewing the actual table fields we see that the UTF8 is used for the table field: definición_de_la_patología. Note how the original MS Access field used is Definición de la patología with spaces? The conversion replaced spaces with the underscore automatically. It does that for the table name as well. However, read on.

The Import as such is going to fail, due to JavaScript naming standard for the column Name.

The solution is to rename definición_de_la_patología to definicion_de_la_patologia, or similar valid Name. The DB Field name can stay as is: Definición_de_la_patología.

Finally, the Italian language field from MS Access might look like this:

Italian Field

As seen, Jam.py will happily use the UTF8 for the Caption. As well as the SQLite3 for database table Name and the DB field name. JavaScript naming standard is now fixed for the Name *.

In short, for any non-English language, or non-Latin alphabet, like the below Hebrew language, the Name * must be changed to Latin alphabet, and the rest can stay the same:

Hebrew original

Please note the Primary key field as well. Because the Name is in Hebrew language, the Primary Key field is also in Hebrew and Import will fail.

For that reason, we developed the way to translate all necessary items automatically, with no manual intervention needed. The above example with Hebrew language would automatically appear as per below. Due to translation inconsistencies, it is always advisable to double check the translated string. In this example, the “doubt” word probably is not correct, hence leaving to the readers to decide.

Hebrew Translated

If happy with the way the table looks like and DECIMAL(15,4) is changed to CURRENCY data type, we can proceed with the Import. It is possible to immediately check the result on the Internet, by removing builder.html from the browser link.

For this particular example with Hebrew language, the Application needs the support for right-to-left language.

This can be achieved by finding below code in index.html file (on ProjectTask):

<html lang="en">

and replacing it with:

<html lang="he" dir="rtl">

Field Captions

To expand on UTF8 and field Captions, there is a utility for MS Access to dump all table fields Captions (MS Access optional Descriptions), to the CVS file. Why would we do that? Well, almost all third-party database conversion utilities do not write the Captions to the new database. Hence, the Access information might be lost. This is particularly true for a non English Access Front End Application, where the developers might utilise Captions from the Language table. Hence, the table name would always be the same, only the Captions would dynamically change.

In the above Italian language scenario, the Access Caption might be Definición de la patología. The MS Access Developer decided not to use Captions, as they are optional. Hence, the table Import will use the table name for the Caption, which is fine for this example as there are no many non-Latin names.

However, please consider the Access application with the field captions! This is what we are trying to resolve, so we reuse all the hard work already done in Access.

For example, if the MS Access table DEMO_TRACKS has a field NAME and the Caption for the field Track title 3, the CSV file might look like below and the code to update it:

import sqlite3
import numpy as np
captions = np.loadtxt('test.csv', dtype=str, delimiter=',')
#print(captions)
#captions = [
#        ['DEMO_TRACKS', 'NAME', 'Track title 3']
#    ]
con = sqlite3.connect('admin.sqlite')
cursor = con.cursor()
for table_name, field_name, caption in captions:
    cursor.execute("SELECT ID FROM SYS_ITEMS WHERE DELETED=0 AND F_TABLE_NAME='%s'" % table_name)
    res = cursor.fetchone()
    if res:
        item_id = res[0]
        cursor.execute("SELECT ID FROM SYS_FIELDS WHERE DELETED=0 AND OWNER_REC_ID=%s AND F_DB_FIELD_NAME='%s'" % (item_id, field_name))
        res = cursor.fetchone()
        if res:
            field_id = res[0]
            cursor.execute("UPDATE SYS_FIELDS SET F_NAME='%s' WHERE ID=%s" % (caption, field_id))
con.commit()
con.close()

Note

To prepare the CSV file, use Excel as “Save As” - “CSV UTF” - very important!

Also very important to run dos2unix command on the file in Linux, since it will otherwise do this at the output:

[[’ufeffDEMO_TRACKS, ..]

Note

The good news is, our conversion utility now supports Access Captions and they are written directly into the SQLite3 database as comments. All popular SQLite3 utilities, like “DB Browser for SQLite” or “SQLiteman”, can read the database schema with the comments.

The below is an example for Arabic language where MS Access utilises the optional Description, which Jam.py might use for Captions:

CREATE TABLE `Location` -- منطقه (`ID` INTEGER PRIMARY KEY AUTOINCREMENT -- ردیف
,
`location` VARCHAR(255) DEFAULT '' -- منطقه
,
`Edare` VARCHAR(255) DEFAULT '' -- اداره
,
`Address` VARCHAR(255) DEFAULT '' -- آدرس
)

We are working towards enabling Jam.py to read the table and field comments and interpret them as Captions while importing the table. As seen, the JavaScript naming standard does not apply for the above table, since the table is using Latin language for everything except Captions.

Deleted Flag

In order to use a full Jam.py functionality with so called Deleted Flag, which basically prevents data to be permanently deleted, we need to add DELETED field to the tables. However, this is not required for Jam.py or the Application to function. It is an nice feature to have, specially if the Foreign Key enforcing is enabled. Meaning, some data cannot be deleted if some other data exist elsewhere as a reference. Deleted Flag totally eliminates the hustle of enforcing Foreign Keys deletion.

We could easily automate adding the DELETED field during the conversion, as it is not required for all tables probably. This is what we can do:

  • we can add a field to any table with a Python code, as per below. The db.sqlite3 is the database converted from Access.

Note

“Order” word is a reserved word for Python, so we exclude it. There are other reserved words, please consult the Python or Jam.py documentation.

import sqlite3
connection  = sqlite3.connect("db.sqlite3")
cursor      = connection.cursor()
tableQuery = "select * from sqlite_master where type = 'table' and name != 'Order' "
cursor.execute(tableQuery)
tableList = cursor.fetchall()
for table in tableList:
    renameTable = "ALTER TABLE %s"%(table[1]) + " ADD COLUMN DELETED integer"
    print(renameTable)
    cursor.execute(renameTable)
connection.close()

Now, we should be able to select the Deleted Flag as deleted field on the table Import.

Indexes

As stated, the migration does not import indexes. This is due to a number of constraints. The good news is once the data is converted, the indexes can be created within the Builder. The good practise is to index all lookups on some table.

Why is this important? Because doing it within the Builder, all indexes are kept and can be recreated on any supported database engine. If the indexes exist within the database only, the Builder would not know about it and they would be lost when moving Application to the preferred database.

Foreign Keys

Similar scenario, after the data is migrated, we can create FK’s within the Builder. Again, the Jam.py utilises lookups for looking up for data.

Users table

If already using some MS Access database table for users authentication, the only thing missing is the Role lookup. It is quite possible that the role is hard-coded in Access, and this is what we are trying to avoid. Use lookups as much as possible.

The process is described in here: How to authenticate from custom users table.

Obviously, it is the best practise to use password hash for storing the passwords. This simply means adding a column with a Password hash text to the Users or similar table.

Note

To Be Continued…

Some migrated examples

To make it easier for a would be Jam.py Users/Developers, we started to build the showcase repository with the Applications directly migrated from MS Access. It is important to understand that one can start completely from scratch and build the application without any conversion or migration tools. We just think it’s easier to use the tools on our disposal.

Please find the list of some applications available as Templates in Access and migrated to Jam.py. All source code is available for download on Export tab.

Personal Account Ledger

The application lives here:

https://msaccess.pythonanywhere.com

The Access Template Personal Account Ledger has a “feature” which enables typing the negative value for currency. Then the actual Expense is automatically converted to Income, since the negative currency becomes a positive value! Our migrated project does not allow for this.

The complete application was developed in a few hours` time. Below is the approximate process:

  • The template was uploaded to https://jampyapplicationbuilder.com/conv/ to give us all tables and data needed for the project.

  • All tables were imported into the Jam.py Application Builder from the provided link after the upload. Since the Access Categories Form has a Drop-down list with the Income and Expense, the Lookup List was created with the same.

Personal Account Ledger
  • By looking at the Access Database Tools Relationships, the Account Transactions table Category field was linked by the Lookup Item to Categories Description field. The Income or Expense field was created in the Transaction table and linked to the Categories same field. Also, the actual_amount field was added, since in Access this is a query field.

Personal Account Ledger
  • The Categories Income or Expense was linked to Lookup Value List.

Personal Account Ledger
  • The first code was added to restrict adding a negative currency with the MessageBox displaying the Alert.

function on_field_validate(field) {
    if (field.field_name === 'transaction_amount' && field.value < 1) {
            return 'The amount cant be negative!';
    }
}
  • Then, we observed how is Access displaying the Expense/Income formatting in red and green color on the Data Grid. The decision has been made to display the actual Expense/Income text in a coloured way as well as the value.

  • The code was added to accommodate for the above:

function on_field_get_html(field) {
    let item = field.owner;
    if (field.field_name === 'category') {
        let color = 'green';
        if (item.income_or_expense.display_text === 'Expense') {
            color = 'red';
        }
        return '<span style="color: ' + color + ';">' + field.display_text + '/' + field.owner.income_or_expense.display_text + '</span>';
    }
    if (field.field_name === 'actual_amount' || field.field_name === 'category') {
        let color = 'green';
        if (item.income_or_expense.display_text === 'Expense') {
            color = 'red';
        }
        return '<span style="color: ' + color + ';">' + field.display_text + '</span>';
    }

}
  • Because Access is triggering the displaying of Actual Amount relative to Expense option in red color, or Income as green colour, the code was added to trigger the similar behaviour:

function on_field_changed(field, lookup_item) {
    var item = field.owner;
    if (field.field_name === 'transaction_amount' || field.field_name === 'category') {
        calculate(item);
    }
}
  • The JS function is needed to actually do the displaying of positive to negative values and vice versa:

function calculate(item) {
    if (item.income_or_expense.value) {
        item.actual_amount.value = item.transaction_amount.value;
        if (item.income_or_expense.display_text === 'Expense') {
            item.actual_amount.value = -item.actual_amount.value;
        }
    }
    else {
        item.actual_amount.value = 0;
    }
}
  • Some formatting was added to make the Entry Title in bold. The below function was added at the end of on_field_get_html function from above:

if (field.field_name==='entry_title') {
    return '<strong>' + field.display_text + '</strong>';
}
  • Then, the Dashboard/Reports was added. Some tlc is still needed for those. The Dashboard was covered in “How was Demo built?” topic

Note

  • The above is all from the actual application Front End point of view. It looks pretty similar to Access, correct? At this moment it is absolutely possible to turn on the “Safe Mode”, which is the Authentication.

  • All of the above was added to Account Transactions Client module. No more code is needed for Access basic Front End look and feel. The additional code for Authentication was copy/paste from the https://jampyapp.pythonanywhere.com/ project. Please observe the code and needed tables within the Builder/Authentication tab. Also, the index.html file was modified and added simple-line-icons support with the Task/project.css. Same copy/paste from the mentioned project.

  • The Clone button was developed latter to emulate the support for highlighted row copy/paste.

  • The Delete button was disabled with the MessageBox displaying the Alert, since the deletion is disabled for Publicly accessible application. This is controlled in Task/Account Transaction/Client module, please search for deleted, since deletion is actioned on CTRL+DEL as well.

  • After two weeks running and some feedback from the Users, the Account Transaction/Filter was added. Also, some formatting was developed, particularly for displaying red and green Actual Amount on the Form. This is not the Data Grid formatting, which was added from the beginning. Hence, consider the above positive to negative function. The function was missing the CSS needed for the Form. The new function is below:

function calculate(item) {
    if (item.income_or_expense.value) {
        item.actual_amount.value = item.transaction_amount.value;
        if (item.income_or_expense.display_text === 'Expense') {
            item.actual_amount.value = -item.actual_amount.value;
            item.edit_form.find('input.actual_amount').css('color', 'red');
        }
        else {
            item.edit_form.find('input.actual_amount').css('color', 'green');
        }
    }
    else {
        item.actual_amount.value = 0;
    }
}
  • More formatting was added to resize the fields. Here, we introduced a new Jam.py function for changing the CSS when the Edit/New Form is active. Which is as well changing the CSS for the field, but this time on the Edit/New Form:

on_edit_form_shown()
function on_edit_form_shown(item) {
    if (item.income_or_expense.value) {
        //item.actual_amount.value = item.transaction_amount.value;
        if (item.actual_amount.value < 1) {
            item.edit_form.find('input.actual_amount').css('color', 'red');
            //item.actual_amount.value = -item.actual_amount.value;
        }
        else {
            item.edit_form.find('input.actual_amount').css('color', 'green');
        }

    }

  item.edit_form.find('input.entry_number').parent().width('40%');
  item.edit_form.find('input.transaction_amount').parent().width('40%');
  item.edit_form.find('input.category').parent().width('60%');
  item.edit_form.find('input.categories').parent().width('60%');
  item.edit_form.find('input.income_or_expense').width('60%');
}
  • History/Edit Lock was added. This is a no code operation done on Project/Parameters.

  • The Report was missing the Income/Expense Parameter. Added. The Reports design and code might be covered latter on.

  • The Filters was added for Account Transaction table. Because of the “combined” Category with the “/” sign, some tweaking was needed. Here is the code added to the Account Transactions table, and the code to add three buttons. The buttons “Clone”, “Filter by type” and “Clear filters”, respectively:

function on_view_form_created(item) {
    if (!item.lookup_field) {
        var clone_btn = item.add_view_button('Clone', {image: 'icon-magic-wand'});
        clone_btn.click(function() { clone_record(item) });
    }
    //here is setting for filter by type of transaction (income or expense)
    let filter_type_btn = item.add_view_button('Filter by type', {type: 'primary', image: 'icon-filter'});
        filter_type_btn.click(function() {
            filter_by_type(item);
        });

    let clear_filters_btn = item.add_view_button('Clear filters', {type: 'danger', image: 'icon-remove'});
        clear_filters_btn.click(function() {
            item.clear_filters();
            item.refresh_page();
        });

}

function filter_by_type(item) {
    let copy = task.categories.copy({handlers: false});
    copy.open({fields: ['income_or_expense'], open_empty: true});

    copy.edit_options.title = 'Filter by type of transaction';
    copy.edit_options.history_button = false;

    copy.on_edit_form_created = function(c) {
        c.edit_form.find('#ok-btn')
            .text('Select type')
            .off('click.task')
            .on('click', function() {
                try {
                    c.post();
                    let categories_by_type = task.categories.copy({handlers: false}),
                        categories_list = [];
                        categories_by_type.set_where({income_or_expense: c.income_or_expense.value});
                        categories_by_type.open({fields: ['id']});

                        categories_by_type.each(function(s){
                            categories_list.push(s.id.value);
                        });

                        item.filters.category.value = categories_list;
                        item.refresh_page();
                    c.cancel_edit();
                }
                finally {
                    c.edit();
                }
            });
    };
    copy.on_edit_form_keyup= function(c, e) {
        if (e.keyCode === 13 && e.ctrlKey) {
            e.preventDefault();
            return true;
        }
    };
    copy.append_record();
}
  • More summary was added for Account Transaction table. This is a no code operation done on View Form.

  • Added the Export tab to download the latest source code. The Export feature is available by default on the Application Builder. The Builder is not accessible publicly due to the security reasons. Here is the code added to index.html file below “about” and Account Transaction table “function on_page_loaded(task)”:

<li id="about"><a href="#">About</a></li>
<li id="export"><a href="#">Export</a></li>
$("#menu-right #export a").click(function(e) {
            var url = [location.protocol, '//', location.host, location.pathname].join('');
            url += 'static/internal/AccountTransactions.zip';
            window.open(encodeURI(url));
});
  • Maybe the last feature needed is the CVS (spreadsheet) export/import. This step is fully documented in the official documentation.

Note

To Be Continued when/if more features were added …

Inventory transactions

This is interesting template due to a number of reports. There is no VBA at all. However, there is a logic within the reports to reorder the Inventory based on Reorder Level, Current Stock, and Target Stock Level.

The complete application was developed in a few hours` time. Below is the approximate process:

  • The template was uploaded to https://jampyapplicationbuilder.com/conv/ to give us all tables and data needed for the project.

  • All tables were imported into the Jam.py Application Builder from the provided link after the upload.

  • The Lookup Lists are within the forms, namely:

Category
Location
TransactionType
  • Because how the Access application works, it might be a good decision to convert the Lookup List into tables. That way we might utilise the Jam.py reports and Dashboard features with no additional code needed. The problem with any Lookup Lists is the table relationship, which can not be built with the SQL, and must be addressed with code. This is how Personal Account Ledger was built.

Note

To Be Continued when/if more features were added …

Northwind Traders

The application lives here:

https://northwind.pythonanywhere.com

The Northwind template has some VBA attached to it. It is a fairly simple code from the migration point of view. Below is the approximate process:

  • The template was uploaded to https://jampyapplicationbuilder.com/conv/ to give us all tables and data needed for the project.

  • All tables were imported into the Jam.py Application Builder from the provided link after the upload. Since there are a few tables with no Primary Key, this are the candidates for the Lookup List or tables:

Employee_Privileges
Inventory_Transaction_Types
Order_Details_Status
Orders_Status
Orders_Tax_Status
Purchase_Order_Status
  • For this exercise, we decided to add the Primary Key for the above tables, instead of making the Lookup Lists. The only Lookup List created in the beginning of migration was for Purchase orders and Orders table. Which in Access is hard coded as Cash, Check and Credit Card information (in the sense of exporting the data into a csv):

Payment Method

Because of that reason, the Payment Method is the VARCHAR Type in MS Access. It was changed to INTEGER, after the Import, and pointed to a Lookup List.

Note

Which opens a question: how to migrate the hard coded text to a Lookup List or even a table? A bit latter about that.

Virtual Table

  • The biggest challenge was implementing the Inventory List Virtual Table, as the MS Access query is quite large:

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];


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];

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];

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];

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];

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];

Note

The above SQL will work only in MS Access. Of course, developing a completely new SQL is possible for any database provider. Jam can use any SQL within the Server Module, however we will demonstrate a pure Jam way of doing it.

Here is the JS and Python code, respectively, replacing the need for SQL. The button Purchase was added just like it exists on MS Access.

The important JS function is:

on_after_open()

As this JS function is taking the result of get_records Server Module function, it is placed in on_after_open. This is important to understand, because in formal Jam.py Documentation it is sparsely mentioned. Most of the time, the on_after_open is used with Master/Detail views. There is a topic How to link two tables in the Docs, which I would encourage to visit.

function on_view_form_created(item) {
    item.paginate = false;
    item.table_options.new = false;
    if (!item.lookup_field) {
        var email_btn = item.add_view_button('Purchase', {image: 'icon-pencil'});
        email_btn.click(function() { purchase() });
    }
            item.view_form.find("#edit-btn").hide();
            item.view_form.find("#delete-btn").hide();
            item.view_form.find("#new-btn").hide();
}

function on_after_open(item) {
    item.alert('Working!');
    item.server('get_records', function(records) {
        records.forEach(function(rec) {
            item.append();
            item.product_name.value = rec.product_name;
            item.target_level.value = rec.target_level;
            item.quantity_on_hold.value = rec.quantity_on_hold;
            item.quantity_on_order.value = rec.quantity_on_order;
            item.quantity_on_hand.value = rec.quantity_on_hand;
            item.quantity_purchased.value = rec.quantity_purchased;
            item.quantity_sold.value = rec.quantity_sold;
            item.post();
        });
    });
}
function on_edit_form_created(item) {
    var title = 'Purchase ';
    item.edit_options.title = title;
    item.edit_form.find('#ok-btn')
        .text('Purchase')
        .off('click.task')
        .on('click', function() {
            purchase(item);
        });
}
function on_field_get_html(field) {
    let item = field.owner;
    if (field.field_name === 'quantity_on_hand') {
        let color = 'green';
        if (item.quantity_on_hand.display_text < 30) {
            color = 'red';
        }
        return '<span style="color: ' + color + ';">' + field.display_text + '</span>';
    }
}

The Python code needs a lot of explanation for a would be Python developer. In short, we are looping through three SQL statements, and not six as in MS Access. All of this is to populate the list res and to return the information back to JavaScript function. Each SQL statement can be recognised by set_where function:

set_where()
def get_records(item):
    res, inventory = [], product = [], ''

    product = item.task.products.copy()
    #product.set_where(id=41)                               # here we can specify just one item for testing
    product.open(fields=['id', 'product_name', 'target_level'],
        group_by=['id'], order_by=['id'])


    for p in product:
        allocated_inventory = 0
        target_level = 0
        product_id = product.id.value
        target_level += product.target_level.value
        print(product_id)

        order_details = item.task.order_details.copy()
        order_details.set_fields('product_id', 'status_id', 'quantity')
        #order_details.set_where(product_id=product_id, status_id=4); # here we can specify just one item for testing
        order_details.set_where(product_id=product_id);

        order_details.open(fields=['product_id', 'quantity', 'status_id'],
            funcs={'quantity': 'sum'},
            group_by=['product_id'], order_by=['product_id'])
        for i in order_details:
            quantity = 0;
            transaction_type = 0
            quantity_sold = 0
            quantity_purchased = 0
            quantity_on_hold = 0
            quantity_on_hand = 0
            quantity_on_order = 0
            quantity_on_back_order = 0

            #print()

            inv_transactions = item.task.inventory_transactions.copy()
            inv_transactions.set_fields('product_id', 'transaction_type', 'quantity')
            inv_transactions.set_where(product_id=product_id, transaction_type__in=[1,2,3])
            inv_transactions.open(fields=['product_id', 'transaction_type', 'quantity'],
                funcs={'quantity': 'sum'},
                group_by=['transaction_type'], order_by=['product_id'])

            for i in inv_transactions:
                if inv_transactions.transaction_type.value == 1:
                    quantity_purchased += inv_transactions.quantity.value
                if inv_transactions.transaction_type.value == 2:
                    quantity_sold += inv_transactions.quantity.value
                if inv_transactions.transaction_type.value == 3:
                    quantity_on_hold += inv_transactions.quantity.value
                quantity_on_hand = quantity_purchased - quantity_sold

                purchase_order_details = item.task.purchase_order_details.copy()
                purchase_order_details.set_fields('product_id', 'posted_to_inventory', 'quantity')
                purchase_order_details.set_where(product_id=product_id, posted_to_inventory=0)
                purchase_order_details.open(fields=['product_id', 'quantity'],
                    funcs={'id': 'count'},
                    group_by=['product_id'], order_by=['product_id'])
                for i in purchase_order_details:
                    quantity_on_order = purchase_order_details.quantity.value

            res.append(
                {
                    'product_name': i.product_id.display_text,
                    'target_level': target_level,
                    'quantity_on_hold': quantity_on_hold,
                    'quantity_on_hand': quantity_on_hand,
                    'quantity_purchased': quantity_purchased,
                    'quantity_sold': quantity_sold,
                    'quantity_on_order': quantity_on_order
                }
            )
            print(res)

    return res

Note

What was achieved with the above Python code is portability. It will execute against any database provider.

  • The set_where function deserves the separate topic though. For now, it is similar to specifying WHERE clause in SQL.

Master/Details Edit

  • In order to implement Master/Details functionality, the PO Detail and Order Detail Group Item was created, and the related tables were imported into this Item Group. This enables the functionality to add Detail to any Master table for viewing and editing.

Master/Details View

  • For Master/Details View only, with no need for editing, there is no need to do anything special other than to add a code to display the Details for a Master table.

    Below is the code which will display for each Supplier the relevant Purchase Order as Details:

function on_view_form_created(item) {
    if (!item.lookup_field) {
        item.table_options.height -= 200;
        item.purchase_orders = task.purchase_orders.copy();
        item.purchase_orders.paginate = false;
        item.purchase_orders.create_table(item.view_form.find('.view-detail'), {
            height: 200,
            summary_fields: ['submitted_date', 'purchase_order_id'],
        });

    }
}

var scroll_timeout;

function on_after_scroll(item) {
    if (!item.lookup_field && item.view_form.length) {
        clearTimeout(scroll_timeout);
        scroll_timeout = setTimeout(
            function() {
                if (item.rec_count) {
                    item.purchase_orders.set_where({supplier_id: item.id.value});
                    item.purchase_orders.set_order_by(['-submitted_date']);
                    item.purchase_orders.open(true);
                }
                else {
                    item.purchase_orders.close();
                }
            },
            100
        );
    }
}

Here we showing the difference with code used for ie Suppliers and Customers. It is the similar functionality to display Orders as Details for each Customer:

Master/Details

Note

As seen, almost the same code is used for all Master/Details Views for Products, Employees and Shippers. It is really simple to add an Detail table to a Master table when knowing the Primary Key.

Note

To Be Continued…