Finally a dbFront Users group

Mon, Jan 22, 2024

As I support dbFront, often using Zoom, I have the privilege of seeing some of the cool systems that users have built using dbFront.  Sometimes a support session turns into a mini-demo before we get down to fixing the issue at hand.  I have often thought that it would be great if various dbFront users could learn directly from each other but I have always cringed at the idea of setting up and managing a social network.  The dbFront Q/A was purposely designed to be a Q/A site (much like StackOverFlow) and not a discussion forum.

The best time to plant a tree was 30 years ago. The second best time is now.
— unknown

With the new year I realized that there were just too many practical benefits to a user group and so after some research selected Linked-In as the location for the group.

The Purpose

At its simplest level, dbFront is fairly easy to understand and implement, but a core part of the functionality comes from the database structure and server.  A properly designed data structure and correctly configured dbFront install can yield some impressive results but that requires a level of experience.

The purpose of this group is to allow dbFront users/admins to share tips, experiences, best practices, challenges, solutions, and generally support each other to build cool stuff.  Some of the discussions that might be covered are:

The answers to these questions can be highly dependent on the needs of a specific system.

Meetings and Training

A hope is that this group could facilitate training via online events.  Possibly via a periodic online meeting.

Help Set Direction

I also plan to poll group members for your opinions on various dbFront feature suggestions and look for general advice on the direction for dbFront.

Not Replacing Support

This group is not intended to replace the existing support avenues such as the Knowledge Base (dbFront.com/kb), the Q&A (dbFront.com/qa) or our other support options (dbFront.com/support)

Moderation

I intend to take a light touch with moderation but all posts containing links, or from new users will be moderated.  If anyone wants to market their dbFront related services then please talk to me and I will find an appropriate solution.

Why Linked-In?

Linked-In is a well-known, solid, and interconnected business network where the majority of dbFront users already have an account. For most dbFront users it requires a single click to join the group.  It also helps us that there is no extra infrastructure or cost other than the actual admin.

Let's Connect

See you there.

Continue Reading...


Thank you for 2023, stay with us as we go Mobile!

Wed, Jan 10, 2024

Thank you, Thank you, Thank you...

The snow is deep here in Canada as we bid farewell to 2023. As we start the New Year we extend our deepest gratitude to you our clients.  Not only have you continued to fund dbFront's growth but you have also continued to provide positive feedback and valuable ideas that we hope to implement over the coming years as dbFront matures.

Please know that we are hard at work making dbFront more powerful, more flexible, more mobile (yes Mobile) all while doing our best to keep things simple and solid.

Notable Clients

We would love to name the notable clients who have funded many of the features that have become core to dbFront.  Suffice it to say, without their funding, much of the functionality that makes up dbFront would not exist today.  We may name those clients in the future,... with their permission of course.  The current licensing cost does not leave much room for new feature development.  If there is a specific feature you would love to see then please consider funding it.  Please see the following list for some suggestions:  Feature Requests.   In comparison here is the list of features that have been requested and added.  Completed Feature Requests.

Stability with dbFront 1.2.6

dbFront 1.2.6 is now the Stable release.  Version 1.2.6 has been functioning as the feature release for some time and accumulated a good set of fixes and we are confident that it is ready for our most demanding clients.  A deeper explanation of what is in dbFront 1.2.6 can be found in the blog post on dbFront 1.2.6.

Custom Search

One of the most significant features completed in the 1.2.6 release of dbFront is Custom Search Buttons.  Custom Search Buttons allow administrators and users to create single-click filter buttons that are placed just below the tables.  The filtering expressions can optionally include SQL, or prompt for user input.

This allows users to quickly move between different views of the same data which makes for happy users…  Because finding what you need is key to being productive and happy!

To see how to set up and use Custom Search buttons, check out the corresponding blog post: Custom Quick Search Buttons

Featuring dbFront 1.3.1

The latest version of dbFront 1.3.1 has been promoted to the feature release.  This is a smaller release that includes all of the updates from 1.3.0 and 1.3.1.  The most notable additions are:

More details to follow.  To get a sneak peek see: dbFront 1.3.1 release history or download the Beta build from dbfront.com/download.

Going Mobile

Traditional mobile applications are expensive to build and maintain.  We could have built a single dbFront mobile app, but it would have stretched our resources, and the app would have been too generic to meet most client needs.  Individual clients would not have been able to create and distribute their own custom HR or Finance app for their own internal users.

With the continued advance of web standards, specifically PWA (Progressive Web App) technology, it's now possible to install apps directly from correctly designed websites.  These apps work across multiple platforms (desktop, IOS, Android, ...) and don't need an app store to install.  If desired, individual clients could convert their dbFront PWA-enabled install to an app store-ready IOS or Android app with a minimal amount of effort.

We are actively working on turning the dbFront client into one or multiple user-installable web application(s).  The deeper off-line functionality will take time to develop.  As noted above, additional funding to support the mobile application development would be appreciated.  To vote on this feature please see Android / IOS app for dbFront.

We hope to have something to play with in the very near future.

Summary

dbFront has seen active development for more than 10 years and there is no plan to stop anytime soon.  The release history can be found at https://dbfront.com/releasehistory

To see these new features download your free trial of dbFront at: dbfront.com/download

Continue Reading...


dbFront 1.2.6 Feature Release

Tue, Sep 26, 2023

With the release of dbFront 1.2.6, we now wrap up the 1.2 branch of dbFront.

dbFront 1.2.6 will soon be marked as stable and all new feature development will happen in the latest 1.3 release of dbFront.

Notice: Licenses with Support & Maintenance that have expired for two years will require a full purchase to upgrade

dbFront Version 1.2.6 Features

This release includes thirteen new features and multiple fixes.
New Features:

Custom Search

One of the most significant features completed in the 1.2.6 release of dbFront is Custom Search Buttons.  Custom Search Buttons allow administrators and users to create single-click filter buttons that are placed just below the tables.  The filtering expressions can optionally include SQL, or prompt for user input.

This allows users to quickly move between different views of the same data which makes for happy users…  Because finding what you need is key to being productive and happy!

To see how to set up and use Custom Search buttons, check out the corresponding blog post: Custom Quick Search Buttons

dbFront 1.3.0

Waiting in the wings is dbFront 1.3.0 which is nearly ready to be a feature release.  More details to follow.  To get a sneak peek see: dbfront.com/releasehistory#v1.3.0 or download the Beta build from dbfront.com/download.

Summary

dbFront has seen active development for more than 10 years and there is no plan to stop anytime soon.  The release history can be found at dbfront.com/releasehistory

Try dbFront for Free

dbFront offers a 30-day free trial so that you can download, install and test it in your environment.  After the trial you can continue to use the FREE version of dbFront for up to 3 users.  Please review the video tutorials and plan to start a free trial today.  We believe you will be impressed.


Continue Reading...


ChatGPT & dbFront handle Depreciation

Wed, Mar 01, 2023

Today we look at how you can use ChatGPT and dbFront to rapidly create or extend custom database applications. In this case we are going to add a module to handle asset depreciation.

Asset depreciation refers to the decline in value of an asset over time due to usage, wear and tear, and obsolescence. Tracking asset depreciation is essential to ensure accurate financial reporting, to meet taxation requirements, and to plan for asset replacements.

Generating the Database Structure

ChatGPT describes itself as a "powerful language model that can generate natural language responses to any given prompt." Today we are going to examine ChatGPT's functionality as we attempt to generate the required database structure for an asset depreciation module. We are going to start with a simple request.

  1. Browse to chat.openai.com, and either login or create an account,
  2. Start a new chat and ask "Create a database to track asset depreciation",
  3. In response ChatGPT quickly generates a response that looks similar to the following.

Sure! I can help you create a simple database to track asset depreciation. Here's an example schema that you can use as a starting point:

Asset Table

Column Name Data Type Description
asset_id integer unique identifier for the asset
asset_name varchar(50) name of the asset
purchase_date date date the asset was purchased
purchase_price decimal(10, 2) purchase price of the asset
useful_life integer expected useful life of the asset (in years)
salvage_value decimal(10, 2) estimated salvage value of the asset
depreciation_method varchar(20) method used to calculate depreciation (e.g. straight line, double declining balance, etc.)

Depreciation Table

Column Name Data Type Description
depreciation_id integer unique identifier for the depreciation entry
asset_id integer identifier for the asset that was depreciated
depreciation_date date date that the asset was depreciated
depreciation_amount decimal(10, 2) amount that the asset was depreciated for this period
book_value decimal(10, 2) book value of the asset after depreciation

With this schema, you can record the details of each asset, including its purchase date, purchase price, expected useful life, and depreciation method. You can then create depreciation entries for each asset at regular intervals (e.g. monthly, quarterly, or yearly), recording the amount of depreciation for that period and the book value of the asset after depreciation. You can use this information to calculate the current value of your assets and plan for future purchases or upgrades.

What is interesting is that the structure includes a short explaination describing how this structure can be used. I noticed that it did not include a supporting table for the "depreciation_method" so I updated my request as follows.

Create a database to track asset depreciation and include the depreciationmethod

I then received an updated structure that included the missing table.

Once I was satisfied with the data structure I then made the following request to get the TSQL and some sample data.

Generate the TSQL create statements including the relationships and 10 rows of sample data in each table

ChatGPT responded with the TSQL code as follows. It paused during the code generation but I just told it to continue and it completed with only one minor hiccup in the sample data.

-- Creating the DepreciationMethods table
CREATE TABLE DepreciationMethods (
    DepreciationMethodID INT PRIMARY KEY,
    DepreciationMethodName VARCHAR(50)
);

-- Creating the Assets table
CREATE TABLE Assets (
    AssetID INT PRIMARY KEY,
    AssetName VARCHAR(50),
    PurchaseDate DATE,
    PurchaseCost DECIMAL(18,2),
    SalvageValue DECIMAL(18,2),
    UsefulLife INT,
    DepreciationMethodID INT,
    FOREIGN KEY (DepreciationMethodID) REFERENCES DepreciationMethods(DepreciationMethodID)
);

-- Creating the Depreciation table
CREATE TABLE Depreciation (
    DepreciationID INT PRIMARY KEY,
    AssetID INT,
    [Year] INT,
    [Month] INT,
    DepreciationAmount DECIMAL(18,2),
    AccumulatedDepreciation DECIMAL(18,2),
    FOREIGN KEY (AssetID) REFERENCES Assets(AssetID)
);

-- Inserting sample data into the DepreciationMethods table
INSERT INTO DepreciationMethods (DepreciationMethodID, DepreciationMethodName)
VALUES 
        (1, 'Straight Line'),
        (2, 'Double Declining Balance'),
        (3, 'Sum of Years Digits');

-- Inserting sample data into the Assets table
INSERT INTO Assets (AssetID, AssetName, PurchaseDate, PurchaseCost, SalvageValue, UsefulLife, DepreciationMethodID)
VALUES 
        (1, 'Computer', '2022-01-01', 1000.00, 100.00, 5, 1),
        (2, 'Printer', '2022-02-01', 500.00, 50.00, 3, 2),
        (3, 'Forklift', '2021-07-01', 5000.00, 1000.00, 10, 1),
        (4, 'Office Chair', '2022-03-15', 100.00, 10.00, 7, 3),
        (5, 'Delivery Van', '2021-01-01', 20000.00, 2000.00, 5, 2),
        (6, 'Construction Equipment', '2020-06-01', 50000.00, 5000.00, 8, 1),
        (7, 'Copier', '2022-02-15', 1500.00, 150.00, 4, 2),
        (8, 'Office Desk', '2021-11-01', 200.00, 20.00, 10, 3),
        (9, 'Delivery Truck', '2020-05-01', 50000.00, 5000.00, 7, 1),
        (10, 'Conference Table', '2022-01-15', 800.00, 80.00, 5, 2);

-- Inserting sample data into the Depreciation table
INSERT INTO Depreciation (DepreciationID, AssetID, [Year], [Month], DepreciationAmount, AccumulatedDepreciation)
VALUES
        (1, 1, 2022, 1, 166.00, 166.00),
        (2, 1, 2022, 2, 166.00, 332.00),
        (4, 1, 2022, 4, 166.00, 498.00),
        (5, 1, 2022, 5, 166.00, 664.00),
        (6, 2, 2022, 1, 50.00, 50.00),
        (7, 2, 2022, 2, 66.67, 116.67),
        (8, 2, 2022, 3, 83.33, 200.00),
        (9, 2, 2022, 4, 50.00, 250.00),
        (10, 2, 2022, 5, 33.33, 283.33),
        (11, 3, 2021, 7, 416.67, 416.67),
        (12, 3, 2022, 1, 416.67, 833.34),
        (13, 3, 2022, 7, 416.67, 5000.00),
        (14, 4, 2022, 3, 9.52, 9.52),
        (15, 4, 2022, 4, 13.61, 23.13),
        (16, 4, 2022, 5, 11.00, 34.13),
        (17, 5, 2022, 1, 8000.00, 8000.00),
        (18, 6, 2022, 1, 6250.00, 6250.00),
        (19, 6, 2022, 6, 6250.00, 31250.00),
        (20, 7, 2022, 2, 300.00, 300.00),
        (21, 7, 2022, 3, 360.00, 660.00),
        (22, 7, 2022, 4, 216.00, 876.00),
        (23, 8, 2021, 11, 4.17, 4.17),
        (24, 8, 2022, 1, 4.17, 8.33),
        (25, 8, 2022, 11, 4.17, 45.83),
        (26, 9, 2020, 5, 5208.33, 5208.33),
        (27, 9, 2021, 1, 5208.33, 10416.67),
        (28, 9, 2021, 5, 5208.33, 31250.00),
        (29, 10, 2022, 1, 160.00, 160.00),
        (30, 10, 2022, 2, 160.00, 320.00);

One point that could be made is that the Asset table is thin on any details not related to depreciation. In this case that is useful because the intention would be to connect this depreciation functionality to an exist asset management database via a 1 - 1 join. Not all assets require depreciation.

I went further asking ChatGPT to generate the required stored procedure which would calculate the yearly depreciation.

Generate the Procedure required to calculate the depreciation

The result was an incomplete procedure and requesting it to continue, did not result in a useful function.

I then sent three separate requests as follows, one for each depreciation type.

Generate the Procedure required to calculate the depreciation for "Straight Line"

Generate the Procedure required to calculate the depreciation for "sum of years digits"

Generate the Procedure required to calculate the depreciation for "Double-Declining-Balance"

Followed by a request to create a procedure that would call the correct procedure based on the depreciation type.

Generate a procedure called CalculateDepreciation, that will call CalculateSLDepreciation or CalculateDDBDepreciation or CalculateSYDDepreciation based on the Depreciation type of the asset

This resulted in four separate procedures as follows.

/**************************************************************************/
CREATE PROCEDURE CalculateSLDepreciation
        @AssetID INT,
        @Year INT
AS
BEGIN
        DECLARE @UsefulLife INT
        DECLARE @AcquisitionCost MONEY
        DECLARE @SalvageValue MONEY
        DECLARE @DepreciationRate FLOAT
        DECLARE @AccumulatedDepreciation MONEY = 0
        DECLARE @DepreciationAmount MONEY

        SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
        FROM Asset
        WHERE AssetID = @AssetID

        IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
        BEGIN
                RAISERROR('Asset not found or incomplete data', 16, 1)
                RETURN
        END

        SET @DepreciationRate = (@AcquisitionCost - @SalvageValue) / @UsefulLife

        IF @DepreciationRate IS NULL
        BEGIN
                RAISERROR('Invalid useful life', 16, 1)
                RETURN
        END

        WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
        BEGIN
                SET @DepreciationAmount = @DepreciationRate

                IF @DepreciationAmount > (@AcquisitionCost - @AccumulatedDepreciation)
                        SET @DepreciationAmount = @AcquisitionCost - @AccumulatedDepreciation

                IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
                        SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation

                INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
                VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)

                IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + 1 > 12
                        BREAK

                SET @Year = @Year + 1

                IF @DepreciationAmount < @SalvageValue
                        BREAK

                SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
        END
END

/**************************************************************************/
CREATE PROCEDURE CalculateDDBDepreciation
        @AssetID INT,
        @Year INT
AS
BEGIN
        DECLARE @UsefulLife INT
        DECLARE @AcquisitionCost MONEY
        DECLARE @SalvageValue MONEY
        DECLARE @DepreciationRate FLOAT
        DECLARE @AccumulatedDepreciation MONEY = 0
        DECLARE @DepreciationAmount MONEY

        SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
        FROM Asset
        WHERE AssetID = @AssetID

        IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
        BEGIN
                RAISERROR('Asset not found or incomplete data', 16, 1)
                RETURN
        END

        SET @DepreciationRate = 2.0 / @UsefulLife

        IF @DepreciationRate IS NULL
        BEGIN
                RAISERROR('Invalid useful life', 16, 1)
                RETURN
        END

        WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
        BEGIN
                SET @DepreciationAmount = (@AcquisitionCost - @AccumulatedDepreciation) * @DepreciationRate

                IF @DepreciationAmount > (@AcquisitionCost - @AccumulatedDepreciation)
                        SET @DepreciationAmount = @AcquisitionCost - @AccumulatedDepreciation

                IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
                        SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation

                INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
                VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)

                IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + 1 > 12
                        BREAK

                SET @Year = @Year + 1

                IF @DepreciationAmount < @SalvageValue
                        BREAK

                SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
        END
END

/**************************************************************************/
CREATE PROCEDURE CalculateSYDDepreciation
        @AssetID INT,
        @Year INT
AS
BEGIN
        DECLARE @UsefulLife INT
        DECLARE @AcquisitionCost MONEY
        DECLARE @SalvageValue MONEY
        DECLARE @DepreciationRate FLOAT
        DECLARE @AccumulatedDepreciation MONEY = 0
        DECLARE @DepreciationAmount MONEY

        SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
        FROM Asset
        WHERE AssetID = @AssetID

        IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
        BEGIN
                RAISERROR('Asset not found or incomplete data', 16, 1)
                RETURN
        END

        SET @DepreciationRate = 1 / ((@UsefulLife * (@UsefulLife + 1)) / 2)

        IF @DepreciationRate IS NULL
        BEGIN
                RAISERROR('Invalid useful life', 16, 1)
                RETURN
        END

        WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
        BEGIN
                SET @DepreciationAmount = (@AcquisitionCost - @AccumulatedDepreciation) * @DepreciationRate * 
                        (DATEDIFF(month, DATEFROMPARTS(@Year, 1, 1), DATEADD(year, 1, DATEFROMPARTS(@Year, 1, 1))) + 1) / 12

                IF MONTH(DATEADD(month, DATEDIFF(month, -1, DATEFROMPARTS(@Year, 1, 1)), -1)) = 12
                BEGIN
                        SET @DepreciationAmount = @DepreciationAmount - (@AcquisitionCost - @AccumulatedDepreciation - @DepreciationAmount) * @DepreciationRate
                END

                IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
                        SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation

                INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
                VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)

                IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + DATEDIFF(month, DATEFROMPARTS(@Year, 1, 1), DATEADD(year, 1, DATEFROMPARTS(@Year, 1, 1))) > 12
                        BREAK

                SET @Year = @Year + 1

                IF @DepreciationAmount < @SalvageValue
                        BREAK

                SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
        END
END

/**************************************************************************/
CREATE PROCEDURE CalculateDepreciation
        @AssetID INT,
        @Year INT
AS
BEGIN
        DECLARE @DepreciationMethod VARCHAR(50)

        SELECT @DepreciationMethod = DepreciationMethod
        FROM Asset
        WHERE AssetID = @AssetID

        IF @DepreciationMethod = 'Straight-Line'
        BEGIN
                EXEC CalculateSLDepreciation @AssetID, @Year
        END
        ELSE IF @DepreciationMethod = 'Double-Declining-Balance'
        BEGIN
                EXEC CalculateDDBDepreciation @AssetID, @Year
        END
        ELSE IF @DepreciationMethod = 'Sum-of-Years-Digits'
        BEGIN
                EXEC CalculateSYDDepreciation @AssetID, @Year
        END
        ELSE
        BEGIN
                RAISERROR('Invalid depreciation method', 16, 1)
                RETURN
        END
END

Testing the Results

The next step was to test the results and create the structure and the procedures to verify that they worked as a whole.

Observations on the Structure

The data structure appeared to hold together quite well and only required minor correction. The sample data was able to load into the structures and the lookup values were relevant. The depreciation values were incorrect but sufficient as far as sample data was concerned.

Depending on how I asked, ChatGPT could generate a variety of different structures.

  • Sometimes a column for the SalvageValue was present, sometimes not,
  • The DepreciationMethod could be a missing, a simple attribute, or a foreign key with lookup table,
  • The date of the depreciation could either be stored in a Date column named EntryDate, or else it might be broken up in to Year and Month columns, or ChatGPT might generate a Period table.
  • When I asked ChatGPT to name all tables in the singular it create an entirely new structure.

It was helpful to have ChatGPT return different structures since each structure showed specific benefits. I found that there was too much variety in the ChatGPT results for them to be brushed of as simple canned responses.

Observations on the Code

The stored procedures were very disappointing. The code looked plausible and showed just enough undertanding to awaken hope. Upon deeper examination it was clear that the code contained massive logic errors, invalid object names and references and/or could not compile. The procedures were nowhere near ready to run.

Observations on the Source

When I asked ChatGPT for its sources, it responded as follows.

This answer was generated solely by me, ChatGPT, a language model created by OpenAI. The database structure and sample data were based on my own understanding of asset depreciation, and the stored procedure was written by me using T-SQL syntax.

When I asked ChatGPT for "specific references for your understanding", it responded as follows.

As an artificial intelligence language model, I don't have subjective opinions or personal experiences. However, the understanding of asset depreciation used in my answer is based on general accounting principles and industry best practices. Some relevant sources for information on this topic include:

  • The Generally Accepted Accounting Principles (GAAP) and International Financial Reporting Standards (IFRS) for accounting treatment of asset depreciation.
  • The U.S. Internal Revenue Service (IRS) tax code and publications for tax-related depreciation rules.
  • Accounting textbooks and online courses for comprehensive explanations and examples of asset depreciation methods and calculations.
  • Industry-specific guides and regulations for asset depreciation in particular sectors, such as real estate, manufacturing, or mining.

I used this general understanding of asset depreciation to create a basic relational database structure and stored procedure that could be used to manage depreciation in a simple system.

I also followed up by running various searches for sample code or data structures for Asset Depreciation and found very little that came anywhere close to the results that ChatGPT returned.

I will leave it to others to figure out how ChatGTP actually arrived at its understanding of depreciation and what that means for us mere mortals.

Adding the UI & bringing dbFront into the mix

The next logical step was to have ChatGPT generate a UI for the data structure it generated.

ChatGPT generated UI

When I asked ChatGPT to create a single page UI for the data structure it responded as follows.

As an AI language model, I cannot generate a user interface (UI) for you, but I can provide you with a general idea of what a single-page UI for this data structure might look like.

It then proceeded to list out what it felt were the specific requirements for the UI. The rest of the reponse follows.

Assuming you want a UI that allows you to view, add, edit, and delete assets and their depreciation information, here are some possible components:

  1. A navigation bar at the top of the page that includes links to different sections of the app (e.g. "Dashboard", "Assets", "Depreciation Methods", "Reports").

  2. A dashboard section that provides an overview of your assets and their current depreciation status (e.g. a summary of assets by category, a chart of depreciation expenses over time, etc.).

  3. An "Assets" section that allows you to view and manage your assets. This section could include:

  • A table that lists all of your assets, with columns for asset name, purchase date, cost, salvage value, useful life, and current depreciation status.

  • Buttons or icons that allow you to add a new asset, edit an existing asset, or delete an asset.

  • A modal window or form that appears when you click on an asset in the table, allowing you to view and edit its details.

  1. A "Depreciation Methods" section that allows you to view and manage the different depreciation methods available in your system. This section could include:
  • A table that lists all of the depreciation methods, with columns for method name, description, and formula.

  • Buttons or icons that allow you to add a new method, edit an existing method, or delete a method.

  • A modal window or form that appears when you click on a method in the table, allowing you to view and edit its details.

  1. A "Reports" section that allows you to generate reports on your assets and depreciation expenses. This section could include:
  • A dropdown menu that allows you to select a report type (e.g. "Asset List", "Depreciation Schedule", "Expense Report").

  • A form that allows you to specify parameters for the report (e.g. date range, asset category, etc.).

  • A preview pane that displays a preview of the report, with options to export it as a PDF or Excel file.

  1. A footer section that includes links to your company's social media pages, contact information, and legal notices.

Of course, the exact design and functionality of the UI will depend on your specific needs and preferences. But this should give you a general idea of what a single-page UI for this data structure might look like.

Not willing to give up so quickly, I asked it to "Generate a web application to maintain those tables.". ChatGPT then proceeded to spit out a basic PHP web application. A fair bit of prompting was required before it would spit out the entire application. With more direction and prompting, ChatGPT moved to jQuery, AJAX and Datatables.net to generate a more advanced application.

Some things that stood out were:

  • The generated UI is heavily tied to the data structure so any data structure changes would result in significant refactoring.
  • The UI was simplistic and additional functionality would require a significant amount of work and experience.
  • ChatGPT left out pieces, possibly expecting you to specifically ask for the missing items.
  • The generation limits force you to request specific pieces of the UI or database structure and then stitch them together.
  • Because of these issues and the previous failures with the stored procedures I abandoned the generated UI.

Using dbFront for the UI

I then opened dbFront and pointing it to the database that contained the newly created asset depreciation structures.

In short order I had a UI that satisfied 5 out of the 6 UI requirements that ChatGPT had outlined.

  1. A navigation menu showing the core tables,
  2. A home screen with instructions.
  3. An assets view that allowed you to fully manage all aspects of the asset. In addition you had the option of calculating next years depreciation.
  4. A maintenance screen for the depreciation methods,
  5. Reports were available on the home screen and related asset.
  6. A fully configurable footer.

The only missing item was a dashboard containing a summary of the data with a chart. The asset summary and chart could be created using a Crystal Report or export to an Excel template containing a chart.

Because dbFront generates it's UI dynamically, it automatically follows along as the database structure changes. This also applies to any of its template based reports or exports. This is specifically helpful because it allows a DBA to continue working on the database structure without having to recreate the UI.

Demo

We took the Depreciation Calculator database structure and the dbFront config and migrated it to our demo environment so that you can see it in action.

Below is a link to the Demo environment and also a link where you can download the source. For the demo environment, you can use the usernames dbAdmin or dbGuest and the password "password".

Wrap Up

There is a significant amount of hype around ChatGPT and some of that hype is deserved, but not all. What ChatGPT generates looks impressive but it fails to show real comprehension of the actual material and therefore it can't self correct. It simply regurgitates plausible looking answers. ChatGPT's inability to self correct is most obvious when it generates reasonable looking code that is incomplete, incorrect, or won't compile. Correcting code salad (think "word salad") is not trivial and often requires a rewrite.

ChatGPT appears to have a good ability to assemble what belongs together and even some concept of sequence. But it just can't do the logic to wire things together correctly and validate that what it is doing is correct.

What we can salvage from this experiment is that ChatGPT showed an interesting ability to assemble a reasonable looking data structure for a known business problem. Any errors in the structure were easy to fix and the ability to generate sample data was useful. The caveat is that ChatGPT requires direction by someone who understands the business requirements and the implications of any database structure decisions.

dbFront again proved to be a dynamic and solid choice when it came time to build the UI.

To check out dbFront, go to dbFront.com.

In the next blog post we are going to try to solve a more complex business problem with ChatGPT & dbFront. Till then.

Continue Reading...


How to Create a Report

Tue, Jan 10, 2023

Today we look at how you can use dbFront to create quick reports. For an example we will be building a report to view the age of submitted expense reports using the Expense Report app.

Setup

To create a new report, click on the Quick Print button. By default this button is only available to Administrators but you can easily enable it for your regular users.

report%20creation

From here, you can now prepare the report. If it is merely a one-time report, click run after you have selected the template and table columns.

If you are going to need to report on the same thing repeatedly, you can save the report as a button. Just provide a caption for the button and save your report.

Report Type

There are two report types to select from:

Multirow Report: this combines multiple records into a single table; this is used when you want to look at a cross-section of your data such as a Staff Listing or ToDo List.

Single Row Report: this returns a single record in a specific table with all related rows. This is used when you need to report on the selected record (for example printing an invoice with its details).

Output

This sets how the report will be outputted. When the report button is first created, you can only have one method of output. Available options are: Csv, Excel (if you want to export the data), PDF, Quick View, and Print at Server.

If the report is saved then you can add more output options:

adding%20outputs

When you add PDF as an output option, you will also be able to setup the page format for the PDF file:

pdf%20page%20layout

When Excel is added as an output option, an Excel tab is added that allows you to use an existing Excel spreadsheet template for outputting to. For more information on how to use this feature, please refer to the article on Excel templates in the knowledge base.

Details

Here you can customize what fields to report on as well as how you want the information ordered.

adding%20rows

Administrators can add SQL filter expressions to further refine the report if necessary. An example of an expression:

sample%20epxression

Child Tables

If the table you are reporting on has child tables, you can add rows from those tables to the report. These child tables will show up in the Options list as Table_name::Rows:

child%20table

Adding these to the Field/Rows section will add new tabs that you can now use to add specific rows and columns from the child tables to the current report.

adding%20child%20table

Report

If the report tab is visible it means that there are available report templates that you can choose to use. These templates can be edited to further customize the output.

A final note about these reports. While the Quick View and PDF allow for a formatted, static view of the specific data, keep in mind that the CSV/Excel output will be more useful in terms of allowing users to work with the data directly and create graphs and reports within Excel that are better suited for their purposes.

The Example

We are going to build a report to show all expenses that have an age of at least 20 days. As we want to see all expenses, we want a multirow report.

In the Details tab, we add the “Age” field in the Fields/Rows section, add ‘vAge” into the Sort Order as we are going to sort on age in descending order and then add an expression in the Filter field so that we only get items that have an age of 20 days or more. We are also going to remove the “Purpose” and “Expense Report ID” from the Fields/Rows section.

details%20tab

With the report now setup, we can run it and see what the results are:

sample%20report

This is an example of a report using single row as the report type and having a child table (in this case, data from the Expense Detail table) added to the report:

sample%20report%20with%20child%20table

Wrap Up

With the ability to create quick custom reports and exports, the user has another tool to better visualize the data that they need to work with whether as a static one-time report or outputted data in a spreadsheet. To check out how these features work, go to dbFront.com, download and explore these features and more.

Continue Reading...