DEV Community: Chris Benjamin

Tutorial: Remix - Material DataGrid

Chris Benjamin — Mon, 14 Nov 2022 17:51:32 +0000

Summary

This tutorial covers working with Material DataGrid (x-data-grid) in Remix (https://remix.run). It will cover standard DataGrid render, and then also using Remix Loader to render the DataGrid. This tutorial will cover JavaScript.

We will create this DataGrid in this tutorial

I created this tutorial after personally adding DataGrid to a Remix project I'm working on where I'm pulling data from a SqlLite Database using Prisma. I wanted to use DataGrid to display the data in the admin dashboard. From this I created a quick tutorial to hopefully benefit at least one other person with the same goal in the future.

Github Code Links:

Phase One: https://github.com/cbenjamin2009/Remix-DataGrid-Example-Tutorial/tree/phase-one

Finished: https://github.com/cbenjamin2009/Remix-DataGrid-Example-Tutorial

What is DataGrid?

Material UI DataGrid is an extremely powerful table grid layout for data in a React based project. It's very simple to use, comes with a free and paid version, and can be adapted into a Remix project with ease.
See MUI for Further

Getting Started Phase-One

First we will need to setup our Remix project using default basic application and JavaScript type.
npx create-remix@latest

Then we will install Material UI and DataGrid dependencies
npm i @mui/material @mui/x-data-grid @emotion/react @emotion/styled

Let's create a new file under routes called datagrid.jsx

Setup the base file as such:



import { DataGrid } from "@mui/x-data-grid";

export default function Index() {
    let rows = [];
    let columns = [];
 return (
    <div>
        <header>
        <h1>DataGrid Component</h1>
        </header>

        <section id="DataGrid" style={{ height: 350, width: '50%', marginLeft: 'auto', marginRight: 'auto' }}>
            <DataGrid rows={rows} columns={columns} sx={{backgroundColor: "#caffca"}}/>
        </section>

    </div>
  )
}

For DataGrid to work, it requires at least a height in it's parent container, and information for the rows and columns. In this case we are going to set the height of the parent section to 350px and the width to 100%. We are going to pull the rows and columns. Let's add that data now.

Rows are key-value pair objects, meaning you must map the column names to their values. Each row should contain an 'id' reference. We will then use these row names for the columns property.
Update rows as follows:



    let rows = [
        { id: 1, firstName: 'Jamaal', lastName: 'Gorczany',  email: 'jg@example.com' },
        { id: 2, firstName: 'Deon', lastName: 'Jast', email: 'dj@example.com'},
        { id: 3, firstName: 'Francisca', lastName: 'Lowe', email: 'fl@example.com'},
        { id: 4, firstName: 'Christine', lastName: 'Hermiston', email: 'ch@example.com'},
        { id: 5, firstName: 'Gunner', lastName: 'Will', email: 'gw@example.com'},
        { id: 6, firstName: 'Ereyn', lastName: 'David', email: 'ed@example.com'},
        { id: 7, firstName: 'Ambrose', lastName: 'Johnston', email: 'aj@example.com'},
        { id: 8, firstName: 'Telly', lastName: 'Witting', email: 'tw@example.com'},
        { id: 9, firstName: 'John', lastName: 'Gutman', email: 'jg@example.com'},
        { id: 10, firstName: 'Spencer', lastName: 'Kemmer', email: 'sk@example.com'},
        { id: 11, firstName: 'Holly', lastName: 'Parisian', email: 'hp@example.com'},
        { id: 12, firstName: 'Jonas', lastName: 'Ambrose', email: 'ja@example.com'},
        { id: 13, firstName: 'Mark', lastName: 'Smith', email: 'ms@example.com'},
        { id: 14, firstName: 'Carole', lastName: 'Spencer', email: 'cs@example.com'},
        { id: 15, firstName: 'Rebecca', lastName: 'Nelson', email: 'rn@example.com'},
    ];

Now let's update the Columns based on the rows. We know we will need a firstName, lastName, and email field. We will also set flex so the columns can grow if needed



    let columns = [
        {field: 'firstName', headerName: 'First Name', flex: 1},
        {field: 'lastName', headerName: 'Last Name', flex: 1},
        {field: 'email', headerName: 'Last Name', flex: 1},
    ];

Let's have a run to see how our site looks. Open the console and type npm run dev

Note at the time of writing there is an issue with Remix github ```

Unexpected Server Error
TypeError: isbot is not a function

If you receive an error about isbot is not a function, do the following. Open the `entry.server.jsx` file and modify the line `const isbot = require("isbot").default;` to `const isbot = require("isbot"); // remove the .default;`

If you followed along with this tutorial, you should receive this result:
![Website with header DataGrid Component and green table in the middle, three columns for First Name, Last Name, and Email. Random sample data is included](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/96077ltzwx9d0oomgj6n.png)

**You have completed Phase-One of the tutorial**. Your code should match the Phase-One branch in the Github repo if you are having any difficulties please compare your code to this repo. 


## Loading data using Remix useLoaderData()
With Remix we have the useLoaderData() function for pulling in data. This could be pulling in data from a database or from an API or from a local file. Regardless of the source, we can use this function to pull in our row data to be mapped to the DataGrid. This tutorial will keep it simple and pull the data from a local file that contains our data. 

Create a new folder called `data` in the `app` folder. 
Create a new file called `data.js` in the `data` folder

![Development IDE window showing folder structure of App](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xqdykisw6moefjr6kkrm.png)

Inside Data.js update with the same data we used previously as follows:
```javascript


const sampleData = [
    { id: 1, firstName: 'Jamaal', lastName: 'Gorczany',  email: 'jg@example.com' },
    { id: 2, firstName: 'Deon', lastName: 'Jast', email: 'dj@example.com'},
    { id: 3, firstName: 'Francisca', lastName: 'Lowe', email: 'fl@example.com'},
    { id: 4, firstName: 'Christine', lastName: 'Hermiston', email: 'ch@example.com'},
    { id: 5, firstName: 'Gunner', lastName: 'Will', email: 'gw@example.com'},
    { id: 6, firstName: 'Ereyn', lastName: 'David', email: 'ed@example.com'},
    { id: 7, firstName: 'Ambrose', lastName: 'Johnston', email: 'aj@example.com'},
    { id: 8, firstName: 'Telly', lastName: 'Witting', email: 'tw@example.com'},
    { id: 9, firstName: 'John', lastName: 'Gutman', email: 'jg@example.com'},
    { id: 10, firstName: 'Spencer', lastName: 'Kemmer', email: 'sk@example.com'},
    { id: 11, firstName: 'Holly', lastName: 'Parisian', email: 'hp@example.com'},
    { id: 12, firstName: 'Jonas', lastName: 'Ambrose', email: 'ja@example.com'},
    { id: 13, firstName: 'Mark', lastName: 'Smith', email: 'ms@example.com'},
    { id: 14, firstName: 'Carole', lastName: 'Spencer', email: 'cs@example.com'},
    { id: 15, firstName: 'Rebecca', lastName: 'Nelson', email: 'rn@example.com'},
]

export default sampleData;

Let's go back to the datagrid.jsx file and begin to pull the data from this file.

Update the imports



import { DataGrid } from "@mui/x-data-grid";
import { json } from "@remix-run/node";
import { useLoaderData } from "@remix-run/react";
import sampleData  from "~/data/data"

Now create the loader function to load our sampleData



export async function loader() {
    return json(sampleData);
}

Perfect, now we can pull this loaderData by updating the Index() function as follows



export default function Index() {

    let data = useLoaderData();
    //row data contains a unique ID for each row and sample data with first name, last name, and email address
    // map through our loaded data and return the row fields
    let rows = data.map((user) => {
        return {
            id: user.id,
            firstName: user.firstName,
            lastName: user.lastName,
            email: user.email,
        }
    })
    // column fields must match row fields, you can assign a header for each column and size if desired
    let columns = [
        {field: 'firstName', headerName: 'First Name', flex: 1},
        {field: 'lastName', headerName: 'Last Name', flex: 1},
        {field: 'email', headerName: 'Email', flex: 1},
    ];
return (
... 
) 
}

Check your finished result, run npm run dev again if needed and you should have the exact same result.

Your code should now match that of the Master branch on the Github repo. If you are having difficulties refer to the master branch.

Conclusion

Material UI DataGrid gives you quick access to sorting, filtering, pagination, toolbar, searching, and so much more. Remix can be combined with DataGrid to instantly fetch and render data in a DataGrid providing lightning fast data grids. It only take a few quick changes to begin pulling in the data in Remix from another source and rendering that content in the rows.

I do hope that you find the tutorial useful, share it with your circle on social media! I'd like to hear any feedback, comments, criticism, suggestions, etc. that you can offer.

I found a use case for the JS Generator function

Chris Benjamin — Tue, 08 Nov 2022 15:54:14 +0000

Generator Functions

Over the years of reading material on JavaScript I've stumbled upon the use of 'Generator Functions' more than once, though each and every time I couldn't think of a solid use case where I would use them.

Generator Function is a JavaScript function that is used to as an iterator to execute the body of the function when called, pausing after execution each time and saving in memory the state of the function after each iteration. A generator function does not run continuously and only executes once each time it's called. A function is denoted as a generator function by the use of a '' after the word function. `function generator(){} `

Generators contain a yield property which specifies a value returned from the generator. To run the generator function again, you call it with the 'next()' method. This process will repeat indefinitely until the method of 'done' is called which changes the done boolean indicating the generator function is complete.

Below is a basic generator function that can be used to generate ID numbers for a userID value to insure it's always incremental from the last userID.

function* userIDMaker() {
    let id = 0;
  while (true) {
    yield id++;
    }
}

const iterator = userIDMaker();

console.log(iterator.next().value); // First call of the iterator returns '0'
console.log(iterator.next().value); // Second call of the iterator returns '1'
// Each .next() call will return in incremental value

My use case

My employer uses a custom web application software for all aspects of the company. This software runs as a ASP .NET web application with a SQL database back end. I write small apps that run inside the web application using HTML, CSS (and bootstrap), JS (vanilla and jQuery).

The project was to create a custom calculator that contained three calculator sections. The hinging point of the project was that it must contain an unknown amount of rows in each table subsection. Some use case examples referenced just 1-2 rows per table, and others up to 100 in each section. Each input in each row needed to have a unique ID so that it's value may be written to SQL by an update statement on change.

Generally if a web app needs 10 or less table rows, I will write the HTML for each table row, then use JavaScript to show/hide table rows. In this scenario since the number of rows would be different and varying each time the application were to be used, I needed a better way.

Project Approach and Generator Use

As I was brainstorming this project and creating mock layouts. One of the layouts included a JavaScript function that would inject HTML into the web application when clicked. This idea was great, however getting the proper ID values was complicated as rows would be removed/added and the iteration of the counter variable created far too many bugs, especially with inserting into the proper place in the table.

The idea of generator function popped into my head as a method of generating unique ID numbers for each row, every time since the generator function stores the current iteration value into memory.

Generator Function

Here is the generator function that I used. This addNewRow function is a standard function that accepts a buttonID and a tableID. One the UI, when the user clicks the add new row button, another function is used to grab the ID of the button, and the ID of the closest table to the button, then calls the addNewRow.

Code executed when clicking the green + button

$("#TextBox_222_900").each(function () {
  if ( $(this).val() > 1){
    let x = $(this).val()
    for (let i = 1; i < x; i++)
    {
    let tableID = $(this).closest("table")[0].id // get ID of closest table 
      let lastRow =  $('#' + tableID).find("tbody tr:last")[0].children[0].firstElementChild.id // Get last row of current table
      let buttonID =  Number(lastRow.slice(-3)) // grab the 3 digit row number such as 900 or 901
      addNewRow(buttonID, tableID) // call addNewRow function 
    }
  }
})

The addNewRow then calls the Generator Function below. This creates a new instance of the generator function, takes the current buttonID and passes it as an argument to the generator function g which will take the value and return the next incremental value. This value will always be based on the ID of the last visible table row in the respective table

function addNewRow(buttonID, tableID) {
  const GeneratorFunction = (function* () {}).constructor;
  const g = new GeneratorFunction('a', 'yield a + 1');
  const iterator = g(buttonID);
  let nextRowNumber = iterator.next().value;

// html template code
}

Final Generator Code

Here is the full generator code block, this has been modified from the actual code to show only 1 table section for brevity. This generator function is used to set the unique ID numbers of each textbox input for each table. This is important so that the functions that grab the values will grab and calculate the correct values for each section due to the formula used. The ID is also important since each input has an 'onChange' event handler which writes it's value into a SQL table as a method of saving data as it's entered.

function addNewRow(buttonID, tableID) {
  const GeneratorFunction = (function* () {}).constructor;
  const g = new GeneratorFunction('a', 'yield a + 1');
  const iterator = g(buttonID);
  let nextRowNumber = iterator.next().value;

  let regex1 = /^table_template_1\d\d/

    let planPaymentTemplate = `
        <tr>
        <td ><input class="form-control wsTextBoxClass" type="Text" name="TextBox_222_${nextRowNumber}" id="TextBox_222_${nextRowNumber}" onchange="update('TextBox_222_${nextRowNumber}',222,'','','*!wstype!*','|:ID:|',2,'False','0','tblCases')"></td>
        <td><input class="form-control" type="Text" maxlength="200" id="TextBox_222_${nextRowNumber+100}" onchange="update('TextBox_222_${nextRowNumber+1}',222,'','','*!wstype!*','|:ID:|',2,'False','0','tblCases')"></td>
        <td><input class="form-control" type="Text" id="TextBox_222_${nextRowNumber+200}" onchange="update('TextBox_222_${nextRowNumber+2}',222,'','','*!wstype!*','|:ID:|',2,'False','0','tblCases')"></td>
        <td><span id="subtotal${nextRowNumber}"></span></td>
        <td><span id="months${nextRowNumber}"></span></td>
        </tr>`   
// additional tables removed for example
    // Use regex to determine which table template to insert a new row and insert after the last <tr> in the table
    if (regex1.test(tableID)) {$('#' + tableID + ' tbody tr:last').after(planPaymentTemplate)}
// additional regex code removed for example 
  }

Conclusion

The Generator Function for iteration in JavaScript is extremely useful in select cases, and care must be taking to use it correctly as to preserve the generators state between executions. This worked perfectly for my use case, and it's the only time in my development career that I have intentionally written a Generator Function in production code.

For more information consult MDN here MDN - Function*

Start Teaching and Using Accessibility Early

Chris Benjamin — Mon, 07 Nov 2022 17:55:39 +0000

My 12 year old son recently came to me for assistance with adding a caption to a picture in a PowerPoint he was creating for a school assignment. He asked me, “is that what the Alt tag is for?”

I then explained to him the purpose of the alt tag and it’s necessity to include in all capacities. We also went over adding captions to answer his original question. He has committed to updating the ALT tag for all images in his digital media which will provide contextual information for those who may not be able to see the images.

Then the thought occurred to me.

If we begin teaching accessibility and it’s principals to children who are just being exposed to creating documents for others, we could create a foundation of accessibility for the future.

If kids are taught and exposed to accessibility options and understand them early, they may be more inclined to use them each time they create something. This doesn’t apply only to web development, or software development, but all produced media could benefit from accessibility in one way or another.

My son was exposed to Microsoft Office products in First Grade. He began typing up assignments and writings, before eventually progressing to adding images and other content to his work over the last few years. If we started teaching to add accessibility elements to this content from the very beginning, it would create an accessibility conscious future.

If you have the power to educate the youth, consider a few small lessons on accessibility to at least bring awareness to the tools and capabilities to ensure everyone can benefit from the content.

The sooner we all begin to understand and apply accessibility focused digital content, the sooner we can make an impact that will last generations.

Zero Based getMonth() Lesson

Chris Benjamin — Thu, 11 Aug 2022 20:09:00 +0000

A lesson learned in using getMonth() method of the Date prototype

Yesterday I had a project where I was adding a small feature to an internal website to alert the user if they had a date issue when completing a web form for current date. This feature compared local computer date/time to that of a HTML Date type input element entered by the user. If the date they entered was not today then it would give them a warning message about entering data for the wrong date. This was to correct a problem with users inputting incorrect data.

My approach to this was to do a quick compare of a new Date variable against the value in the input from the user and then trigger an alert() to the user if there was a mismatch.

let myDate = new Date()
console.log(myDate)
// Thu Aug 10 2022 12:39:27 GMT-0700 (Pacific Daylight Time)

Perfect, we have a variable that is pulling the correct date/time.

HTML elements store the date in YYYY/MM/DD format and getDate pulls in MM/DD/YYYY order so they cannot be directly compared.

My approach was to reverse the date string to quickly match that of the users. There's several ways to do this but this is the step I used.

  let myDate = new Date();
  let newDate = []
newDate.push(myDate.getFullYear(), '-', myDate.getMonth(), '-', myDate.getDay())
// -> [2022,-,7,-,3]
console.log('newDate.join()' + newDate.join(''))
// -> 2022-7-3

Do you see the errors? There were two, the first is the month, and the second is the day. In my haste to create this quick feature I used getDay() instead of getDate() which gave me the wrong calendar date.

I did not notice the errors and was quite puzzled that the getMonth() was pulling from the month prior but when I made other attempts to extract the date it was pulling month 8. So I went to Twitter and asked the community.

// Detect dark theme var iframe = document.getElementById('tweet-1557395578245369857-398'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1557395578245369857&theme=dark" }

Several helpful people quickly responded with the answer, which until this time, I had no clue getMonth() pulled zero-based months in all of these years working with dates. Several people referred me to MDN getMonth which clearly explains what is happening.

The other error is about the getMonth(). This function extracts the month from the date in a Zero Based Value. This means that for the calendar year, the months are numbered 0-11, whereas August is 7 instead of 8.

The getMonth() method returns the month in the specified date according to local time, as a zero-based value (where zero indicates the first month of the year). - MDN

In most cases when I'm extracting dates, I'm using myDate.toLocaleString() or myDate.toLocaleDateString() which mask the output and show the proper month because of locale.

myDate.toLocaleString()
// -> '8/11/2022, 12:54:53 PM'
myDate.toLocaleDateString()
// -> '8/11/2022'

Conclusion

I wanted to share this information with all of you as to help someone else avoid running into this same error in the future. Every time I work with dates I run into some type of issue and this just happened to be yet another lesson to learn in working with dates.

SQL DB Backup with PowerShell GUI - Tutorial

Chris Benjamin — Tue, 09 Aug 2022 15:24:01 +0000

Into

I have non-technical users in my company that must initiate a SQL backup at least once per month during a critical business process of printing millions of dollars worth of checks. We must make a backup prior to the start and then again immediately afterwards.

My predecessor had trained and taught the staff to utilize SQL Server Management Studio (SSMS) and to execute a maintenance plan that performed the backup. This process worked, however, 🚩 it always bothered me that non-technical staff were accessing SSMS where great harm could be done to the production database. While the chances of that happening were low, the risk was still there, and only trained database administrators should be accessing SSMS.

Project Idea

I wanted to make a quick and simple way for the staff to create the SQL backup without having to access SSMS. There are plenty of options such as 3rd party tools, PowerShell SQL commands, command prompt scripts, etc. The goal was for the staff to have a 1-click backup capability and to be able to retrieve the date/time of the last backup.

I first began to write a PowerShell Script that used invoke commands to execute the predefined maintenance plan. This worked and created a backup, or at least started the job. The problem was the user experience, the user did not get helpful error messages or any indication whether the job actually finished, nor a way to monitor progress since they cannot continue their job function until the backup is complete.

DBATools

After doing a bit of research, I stumbled upon DBATools, (https://dbatools.io/) which offers a PowerShell module with built in commands for creating the backup, but also provides progress and feedback to the user.

PowerShell GUI

In my experience, end users are not comfortable running scripts within PowerShell or Comment Prompt, any time they see the window open, they get nervous. End users prefer a graphical user interface with easy to click options to simply the process.

PowerShell GUI is quite powerful, reminiscent of creating windows forms applications in C#. You can add buttons, labels, progress bars, drop down menus, combo boxes, and so much more.

Solution

PowerShell GUI + DBATools = Amazing User Experience!

I was able to combine PowerShell GUI features to add labels and buttons to a form dialog which allows users to create a simple 1-click backup of the databases, and a 1-click button to view the last backup date/time

Tutorial

This project will require administrative access on your local system and the ability to install PowerShell modules, at least in current user scope. I recommend that you type all of these commands instead of Copy and Paste since your memory retention will be much better if you get used to typing these commands.

Assumptions

You have some familiarity with PowerShell
You have access to SQL Server Management Studio
Your Windows Account has access to run SQL Server Management Studio commands
You have administrative privileges on your workstation to install PowerShell Modules

DBATools Install

We will be installing DBATools.io using PowerShell Gallery and a simple install-module command. The DBATools.io website provides additional installation instructions should you need alternative installation or additional information.

One time install from Administrative PowerShell Window:

Install-Module dbatools

You will need to allow NuGet provider, if this is your first time installing a PowerShell module.
You will also need to indicate you trust the repository

Test if the install was successful:

To test if the install was successful we are going to try to import the module. If the installation was successful the prompt should simply return to a new line, if the installation was not successful then you will get an error message and you may need to repeat the steps or try another method.

One time install:

Import-Module dbatools

PowerShell ISE

For the rest of this tutorial we will be utilizing PowerShell ISE which allows you to view and test your code in a single window.
Note: If you do not have the script pane at the top, you can press 'CTRL + R' on Windows or go to View -> Script Pane at the top.

Build basic form

We are going to build our basic form and ensure we can get information to display.

Type the following information into your Script Pane which is the upper portion of your PowerShell ISE window with line numbers. This will give us a blank square window that's 300px by 300px and can re-size automatically if needed.

The final line is the most critical, if you do not tell the form to show up $main_form.ShowDialog() then nothing will happen when you run the script. I kept this at the bottom since it will always be our last line of code in this whole script.

#Setup Form
Add-Type -assembly System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$main_form = New-Object System.Windows.Forms.Form
$main_form.Text ='DB Backup Program'
$main_form.Width = 300
$main_form.Height = 300
$main_form.AutoSize = $true

#### Show Dialog
$main_form.ShowDialog()

Adding Labels

We are going to add 4 labels

Get Backup History - for our View Last Backup button
Last Backup Title- will show next to the last backup label that will be updated by the script
Last Backup - will show 'unknown' or the last backup date/time
Run Backup - for our Start button
Error - to show any errors on the screen for the user

When adding labels, you must define a variable for the label, give it some text, give it a location on the GUI, and then tell the form to add the control.

Add the following labels to your script pane, below $main_form.AutoSize = $true and above $main_form.ShowDialog()

### ... main form content

######## Add content to form
## Last Backup Label for the View Last Backup button
$LastBackup_Label = New-Object System.Windows.Forms.Label
$LastBackup_Label.Text =  "Get Backup History"
$LastBackup_Label.Location = New-Object System.Drawing.Point(10, 20)
$main_form.Controls.Add($LastBackup_Label)

## Last Backup Title Text Label
$LastBackupTest_LabelTitle = New-Object System.Windows.Forms.Label
$LastBackupTest_LabelTitle.Text =  "Last Backup:"
$LastBackupTest_LabelTitle.Location = New-Object System.Drawing.Point(10, 50)
$main_form.Controls.Add($LastBackupTest_LabelTitle)

## Last Backup Text Label - will be updated by this program to show last date/time of backup
$LastBackupTest_Label = New-Object System.Windows.Forms.Label
$LastBackupTest_Label.Text =  "Unknown"
$LastBackupTest_Label.Location = New-Object System.Drawing.Point(120, 50)
$main_form.Controls.Add($LastBackupTest_Label)

## Error Label
$Error_Label = New-Object System.Windows.Forms.Label
$Error_Label.Text =  ""
$Error_Label.Location = New-Object System.Drawing.Point(10, 120)
$main_form.Controls.Add($Error_Label)

## DB Backup Label for Start button
$Label = New-Object System.Windows.Forms.Label
$Label.Text = "Run Backup"
$Label.Location  = New-Object System.Drawing.Point(10,80)
$Label.AutoSize = $true
$main_form.Controls.Add($Label)

###.... bottom
#### Show Dialog
$main_form.ShowDialog()

Adding Buttons

We are going to add 2 buttons to the GUI. There will be a button to get the last backup and there will be a button to start a backup. I will explain what each button will be doing. Each button must be defined as a variable and given text, size, and a location. I will also be defining the background color though that's optional.

Type the following in your script pane below the labels but above the $main_form.ShowDialog()

### .... labels
# Last Backup Button
$LastBackupButton = New-Object System.Windows.Forms.Button
$LastBackupButton.Size = New-Object System.Drawing.Size(120,23)
$LastBackupButton.Location = New-Object System.Drawing.Size(200,20)
$LastBackupButton.Text = "Get Last Backup"
$LastBackupButton.BackColor = 'LIGHTBLUE'
$main_form.Controls.Add($LastBackupButton)

## Start Button
$StartButton = New-Object System.Windows.Forms.Button
$StartButton.Size = New-Object System.Drawing.Size(120,23)
$StartButton.Location = New-Object System.Drawing.Size(200,80)
$StartButton.Text = "START"
$StartButton.BackColor = 'GREEN'
$main_form.Controls.Add($StartButton)

#### Show Dialog
$main_form.ShowDialog()

Test GUI

Let's run the script to make sure that our content is showing correctly.

Adding Click Events

Right now our buttons are doing nothing, because we haven't told them what to do yet. Let's add functionality to the buttons. We will do these separately since they are running different functions.

adding some variables

Let's add some variables that we will use in the next steps. These values will be different for your setup for the Server Name and the Databases being backed up. Type these below the button code but above the ShowDialog.

## Variables
$db = Start-Job -ScriptBlock {Import-Module dbatools}
$server = 'SERVER NAME'
$databases = @('DB_1','DB1_Audit')

Get Last Backup Button Action

This button will comprise of a button click event which will run a function and if successful it will return the last backup. We need to define the 'Add-Click()' event with a try/catch/finally block. We will also make visual changes such as button color, button text, to indicate to the end user that the program is running.

 ## LastBackupButton Click Action Event - VIEW LAST BACKUP
$LastBackupButton.Add_Click( { 
try {

    ## We are going to indicate to the user the script is running by changing the button
    ## background color to yellow and the text to 'Processing...'. 
    $LastBackupButton.BackColor = 'YELLOW' 
    $LastBackupButton.Text = "Processing..." 
    # to get the update to show on the form we must tell the form application to update from the events
    [System.Windows.Forms.Application]::DoEvents()
    #Let's now run our getLastBackup function 
    getLastBackup
    }
    catch
    {
        #if an error happens we are going to let the user know
    $Error_Label.Text = "There was an error"
    [System.Windows.Forms.Application]::DoEvents()

    }
    finally {
        ### if the try block was successful then let's update the button back to normal so the user knows the program is done
    $LastBackupButton.Text = "View Last Backup"
    $LastBackupButton.BackColor = 'LIGHTBLUE' 
    [System.Windows.Forms.Application]::DoEvents()  
    }
} )

function getLastBackup {
    # we are going to update the label text by running the dbatools Get-DbaDbBackupHistory command on our databases
    # we only want the end date/time so we are going to extract just that information 
    $LastBackupTest_Label.text = Get-DbaDbBackupHistory -SqlInstance $server -Database $databases[0] -Last -DeviceType Disk | foreach {$_.End}
    # to get the label to update we must tell the form application to update from the text event
    [System.Windows.Forms.Application]::DoEvents()

}

###.... bottom
#### Show Dialog
$main_form.ShowDialog()

Testing

Let's run the script and click View Last Backup button.

Note: This will only work if you have updated the variables for your environment and your user account has access to SQL, which is outside the scope of this tutorial.

Start Backup Click Action

Now let's implement the Start Backup Click Action which will use DBATools BackupDbaDatabase command to initiate a backup. This tutorial will save the backup to a local disk, however it's advised your SQL backups be stored somewhere other than local server. The path must also exist.

The Start Backup is going to update the start button to indicate to the user that it's running. This process can take 5-10 minutes or longer so we want the user to wait until it's done. This will also call the View Last Backup button's function to update the last backup label with the most recent backup which was the one just performed.


## StartButton Click Action Event - START BACKUP
$StartButton.Add_Click( { 
try {
    ## Added to show end user that it's running by changing background
    ## to yellow and text to 'Running...' since this can take 5+ minutes
    $StartButton.BackColor = 'YELLOW' 
    $StartButton.Text = "Running..." 
    # tell form to update
    [System.Windows.Forms.Application]::DoEvents()
    # run startBackup function
    startBackup
}
catch {
    # If there is an error let's tell the user there was an error
    $Error_Label.Text = "error in application"
    [System.Windows.Forms.Application]::DoEvents()
}
finally {
    ## run getLastBackup function to update label with the backup that was just done
    getLastBackup

    ## set start button back to default 
    $StartButton.Text = "START"
    $StartButton.BackColor = 'GREEN'
    [System.Windows.Forms.Application]::DoEvents()
}
 } )

function startBackup {
ForEach($db in $databases){
                ## local variables so we can add date & time to backup file name
                $date = Get-Date -Format 'yyyyMMdd'
                $time = Get-Date -Format 'HHmmss'
                # set local path variable Note: this path must exist
                $path = 'E:\SQL\DBBackup\' + $server + '\' + $db + '\'
                # set file name
                $fileName = $server + '_' + $db + '_FULL_' + $date + '_' + $time + '.bak'
                try {
                    Backup-DbaDatabase -SqlInstance $server -Database $db -Path $path -FilePath $fileName -CompressBackup -IgnoreFileChecks
                    }
                catch 
                {
                    # write output to console screen which will show behind GUI when running
                     Write-Host "Error: $($_.Exception.Message)"
                }
}
}

###.... bottom
#### Show Dialog
$main_form.ShowDialog()

Conclusion

This was a fairly simple example of the PowerShell GUI capabilities and DBATools to perform a simple query on the last backup and a backup of the database.

The contents of this tutorial were modified from the production version of the script to show case a more simple version. The production version uses AES256 encryption via certificate and stores the backup on a different server. It also has cleanup capabilities to remove old backups after they are no longer needed. Additional error handling was also configured to show the output of the error in the bottom of the window instead of just indicating an error occurred. I encourage you to explore adding additional functionality if you use something like this in a business setup.

I’m on @buymeacoffee. If you like my work, you can buy me a taco and share your thoughts 🎉🌮

Small Features Matter

Chris Benjamin — Thu, 14 Jul 2022 14:49:48 +0000

Small features that are useful are worth adding

My day job uses a custom web application in-house that is used by all staff for their daily jobs, we use it more than Outlook.

I recently added a feature to our in-house application that was overwhelmingly accepted by staff, yet I thought it was such a small feature addition that no one would bother, and initially was only for myself.

Back Story

With the global pandemic and some people working in the office, others at home, some are on vacation, it was hard to know who was actually working before reaching out or sending them something to work on. Sure, our HR people knew, but from the IT/Dev side, I had no clue who was in or out and could wait hours only to realize they are off that day. Occasionally I delegate tasks to staff members and more often than not, I was sending requests to staff members that needed a quick response even when they were out of the office.

Idea

I had an idea to solve this for myself. Our custom web application features authentication where users login and logout. I wrote a small feature that only I could see, with a list of users who were currently logged into the system. This helped me know who was actually working that day.

Sharing

After using this personally for a few weeks, I offered the feature to the rest of the management staff that were complaining of similar issues. When we all work at home, it's really hard to know if someone is off that day or not aside from our HR department. The other managers were quickly excited and made use of the feature right away. Within 24-hours they requested that I make this feature available to all.

Initially this feature only showed users who had logged in each day but didn't check whether they had logged out. After sharing, some of the feedback requests were to indicate if a user logged out, such as for an appointment, then their name was removed from the list. This feature may never have been added if I didn't share this feature with others.

I immediately began hearing praise from staff members who were excited about a relatively simple feature that allows them to know someone is working that day before calling or emailing to avoid wasted time for our clients.

Conclusion

Small features are worth developing, but more importantly, they are worth sharing. If you create a feature that benefits you, think about whether or not someone else can benefit from the same feature. The feedback you receive from others can actually improve your design in ways you didn't initially realize.

To Confirm or Not To Confirm (Opinion on HTML Forms User Experience)

Chris Benjamin — Wed, 13 Jul 2022 16:31:10 +0000

Let's talk about website forms user experience

Website forms are something we complete all the time. Whether we are registering for a new account on a brand new service, completing digital paperwork for a medical appointment, or even applying for a job. We interact with forms a lot, and as a developer it's our job to create these forms, but we need to do better for the user experience.

Website forms are the digital replacement for paper forms. For example, to apply for a job more than 10 years ago, you would have to visit the company and ask for an application for employment. This paper form would then be completed and returned, usually along with a resume. This process today is entirely online for most employers.

One key difference from the transition to digital forms was the requirement to duplicate information.

Example of a Bad User Experience HTML Form

This example was taken from a HTML Forms YouTube snapshot where new developers are taught to duplicate registration fields.

Even Google requires duplicate password entry but has a 'Show Password' option.

Example of a Good User Experience HTML Form

Here is the current Sign Up page for SignUpGenius a website for gathering signups such as RSVP.

Here is Instagram's current signup page

What separates the good from the bad?

The User Experience
When a user only has to input the minimum necessary information to begin using your service or product, and doesn't have to duplicate any information, it's a great first impression and user experience.

At some point in HTML forms over the years, developers started requiring the user to enter some information twice. Every single week I come across a website that requires me to enter something two times, even when I can see the first one I typed on the screen. Most times, I'll just copy the first field and paste it into the second, but is that 'confirm' field even necessary?

The most common confirm fields that I find are Username, Email, Password, Account Number and Address. I've never filled out a paper form where I had to hand-write my email two times or my account number two times.

User Experience

Have you ever witnessed a user registering for a website or service and get frustrated when they click submit and then form validation runs and an error message appears that something they entered two times didn't match? Their first impression of your company has now taken a negative tone as they become frustrated just trying to register for your service.

Additionally, the size of forms plays a huge role in whether people actually complete the registration. A user is less likely to submit a registration form with 15 fields, then they would be with 2 fields.

Example

Let's have a look at an registration form design template example. This template is daunting to the end user, look at all the information that must be filled out just to register. There are also two confirm fields requiring duplicate entry.

Why is this?

In my opinion, this practice came about in the early days of website forms when the target audience was less tech savvy and prone to typos. Not to say that this is entirely resolved today, but the design fundamentals have come a long way where confirmation of input can be removed. A typo in an email can pose a problem as the user would not receive any email communication from the service, like an activation link.

Large companies such as Facebook only recently dropped the requirement to duplicate the email on their sign up form, but many websites still use this old logic.

Suggestions for Resolution

A large amount of forms these days are completed using auto-fill technology where users are on a personal device and a lot of the information is saved to their browser, or an extension, etc. I also personally copy and paste a lot of information from my password manager which eliminates the need for duplicate entry.

Some key changes in technology to reduce errors are the ability to reveal information typed into a password field, and auto-fill. Allowing a user to double check their password by revealing the input field eliminates the need to enter it twice.

Password Confirmation Field

All forms should have the option to reveal the password so that a user can double check the entered password if manually entering. The confirm password field should be eliminated.

Email Address Confirmation Field

The requirement to enter the email a second time should be eliminated entirely. If the email address is absolutely critical then perhaps a checkbox the user must click to acknowledge their email is correct but they should not be forced to re-enter.

Account Numbers / Routing Numbers Confirmation Fields

Online transactions via ACH or EFT require entry of financial institution routing and account numbers. Most sites ask for both fields to be entered twice, or at least the account number to be entered twice. If the information is revealed on the screen or can be revealed for confirmation, then there is no need to force a user to enter it a second time.

Conclusion

HTML forms are all about user experience. This is how we get information from users into our website and we want to make sure the users provide clear and accurate information without frustration.

If you are creating HTML forms, consider alternatives to adding 'confirm' input fields. Look at the end user perspective and determine if it's truly necessary to make your client enter the same thing multiple times. I personally despise having to type something two times in a row.

If you feel it's necessary that the user double-check entered information such as an account number, a checkbox is a simple alternative on the form to have the user confirm they entered it correctly, without having to enter it twice.

Go forth and create awesome HTML forms with great user experiences.

If you have thoughts on this, I'd love to hear your comments!

How to Grow as a Developer?

Chris Benjamin — Tue, 15 Feb 2022 21:23:04 +0000

Growth

When you have been a developer / programmer / software engineer for several years it can feel like you have a great grasp of your tools and languages. You only realize what you don't know when you change tools, try a new library, or change languages.

The same can be said for almost every career and profession in the world. You could be a mechanic that works on cars every day for 30 years and then a new 2022 vehicle comes out with a feature you've never seen before, time to learn 1% more!

There is always room to learn more in your existing tools, libraries, frameworks, and languages. There is where the 1% rule comes into place.

The 1% Rule

The 1% rule challenges you to take something that you already know, and learn 1% more about it.

Resources?

The resource itself doesn't matter. You can use books, social media, blog posts such as this one, MDN, YouTube, a colleague, a professor, literally anything! The only requirement is that you learn something that you did not know before!

How do you quantify 1%?

I quantify the 1% as knowing something more this week than I knew the previous week. For example, with JavaScript you can make an object immutable using Freeze. If you didn't know this before you read this post then you are already 1% better than you were last week. It's a gradual gain of knowledge or skills

Example using CSS

Let's say you feel confident in your CSS skills and use CSS every day in your front end dev role. Pick an area of CSS you don't use often and learn about it, well enough that you could tell a colleague or coworker about it and they would understand it as well.

Example using JavaScript

You have been a JavaScript developer for years, you have been using it in some form or another every day. There have been many changes and developments in JavaScript since you first learned about it, for example String.matchAll() is a recent addition to the language that wasn't possible until just a couple years ago. Pick one topic or area of interest and learn about it. Then share that knowledge with colleagues, friends, or your social media audience!

Share what you learn

When you share new things you learn, you are literally teaching other people! There are millions of people on Twitter, several of them are just getting started on their journey. Whether you are 1 step, 1,000 steps, or 1,000,000 steps into your coding journey, you can still learn 1% more!

Challenge

Spend time every single week educating yourself or improving by 1% in areas of your life that you care about. It's as easy as reading an article, a blog post, watching a YouTube video, or talking to a friend.

Understanding DNS (Domain Name Servers)

Chris Benjamin — Thu, 20 Jan 2022 22:52:26 +0000

Domain Name Servers (DNS) is a critical service that is part of every network and website in the world. DNS is crucial to how internet functions and allows your computer to find and access other computers and services across the world!

If you have ever visited a website or sent an email, you are using DNS! In most cases, it just works!

What is DNS?

DNS is like the Postal address system for the internet and local networks. For example, if you want to visit Twitter on your PC, you would type in Twitter.com in your favorite browser and will be almost instantly connected to Twitter's website.

How does your computer know that when you type in Twitter.com to take you to the right computer and server out of the millions across the world?

When you send emails, how does your computer know where the recipient is?

When you want to print to your wireless printer, how does your computer know where to send the print job?

The answer is DNS

Analogy - Postal System

Imagine you are sending a letter in the mail from your house in Seattle, WA to your friend in Nashville, TN 📬. When you mail that letter, your letter is carried by the postman back to the mail facility, it is then sent out with the rest of the outgoing mail to a sorting facility. These letters are then sorted and transferred to the next station, this happens multiple times until your letter finally arrives at your friends city. Your letter then is handed to a mail carrier who will bring it to your house. This whole process takes a matter of days. You don't have to understand how the layers of the postal routing system work to mail your letter and you can rest assured that it will reach the right place.

This same philosophy applies when you are visiting a website. You type in the name of a service you wish to visit such as Twitter.com. Your computer makes a connection behind the scenes to your Router, your Modem, then your Internet Service Provider, then a root name server to determine which server has the address information, then another request is made to the root hint server to get the actual IP address of the service. This serve has the address in the form of an IP address which is a series of numbers.

At this point, DNS has finished it's job and got the final address of the destination, routing kicks in and your computer makes a connection to the IP address that it received, your computer is connected to this numerical address behind the scenes where computer will perform a Get request to the web server and request the content on that server which you will see in your browser.

As an example, one of the IP addresses that resolves from Twitter.com is 104.244.42.193. Could you imagine having to memorize the IP address number for each website that you wanted to visit?

Say you wanted to go to Facebook.com but you had to know the IP of 157.240.3.35, you could open your browser and type in http://157.240.3.35 and it would connect you to Facebook.com (go ahead, try it!).

Routing

Your computer makes several connections behind the scenes when you visit a website, and all of this happens almost instantly. The very first time you access a website, your computer has to load all of the content but it saves the DNS information, route information, and some of the content in various caches on your computer so that subsequent requests happen much faster. In the below example. you can see what is called a trace report showing that there was 15 separate connections that it took to reach my website! It's incredible that all of this happens magically in the background for you.

Email uses DNS

Email 📧 is the electronic version of a letter that we use to mail. Thankfully this service also uses DNS to route your email and ensure that it ends up in the right mailbox. It takes a similar journey as your physical mailed letter. Have you ever sent an email and received back a Postmaster rejection error that your email wasn't delivered? That's the same as getting a returned letter in the mail.

Business Networks use DNS

All business networks use DNS the same way that the internet does for finding information. For example. if your company has a central file storage where you may save or access information. Your computer uses DNS to find the server that has the requested information and then connects your computer to that server or resource and provides the information.

DNS Inventor

We can thank Paul Mockapetris (@svnr2000) for the invention of DNS and the fact that we don't have to remember these IP addresses to visit the websites that services that we use every day. Source: Internet Hall Of Fame

DNS Record Types

DNS also handles the routing for everything with the domain name using different types of records. Every domain has DNS settings, which is how root hint servers get their information for your website. These settings can route your website, your email, sub-domains, and multiple other services. Some of the most common ones are listed below along with the services that they are used for.

A Record - This is used to tell DNS root servers the IP address for your domain.
C Name Record - This is used for subdomains. Say you own example.com but you also want to have blog.example.com. the "blog" portion of this address is a CName record which can point this part of the website to a different IP address in the event your blog is hosted somewhere other than your website. I use this for my own domain (chrisbenjamin.dev and blog.chrisbenjamin.dev)
MX Record - This is used for mail routing. When someone sends you an email to your domain such as (user@example.com) then DNS is used by the email servers to determine where to send that email
TXT Record - Text record, typically used for administrative notes, validation, or other security features for the domain.
NS Record - Name Server Record lets DNS servers know which server is responsible for resolving records on your domain name.

Testing DNS

You can check out DNS information for any website, it's all publicly accessible information. You can use built in tools on your computer such as nslookup to find information from a command line, but if you prefer the graphical approach then my recommendation is MXToolbox SuperTool available here. You can use the drop down to search through all available records for a domain and see what you can learn!

NSLookup

MXToolBox

Changing DNS Records

When a DNS record is changed, such as moving your website from one hosting provider to another, or simply adding a new C Name record for your blog site. These changes go through a process called propagation. During this time, many DNS root servers will still be pointing to old records because of DNS caching, or may not have the record yet. This process can take up to 72 hours to propagate around the whole world so it's important to plan your changes wisely. Some servers will pick up the change as soon as 5 minutes, while others will take several hours.

DNS Problems

When DNS problems occur, there is usually a failure along one of the many paths that the computer uses to resolve the domain name. When troubleshooting DNS it's best to follow the chain and see where the problem occurs. There are numerous errors caused by DNS since it's the heart of almost all network communication between computers and servers around the world.

Conclusion

My goal was for you to learn at least one thing new in this article regarding DNS. This article has covered some of the basics of what DNS is, why it's important, the basic principals of how it works, and how to check DNS records yourself. If you are a web developer, spend some time familiarizing yourself with DNS if you are not conformable with it, it will be important when it comes time to deploying your website on the internet.

I’m on @buymeacoffee. If you like my work, you can buy me a taco and share your thoughts 🎉🌮

Solving A Real World Business Problem using Web Dev

Chris Benjamin — Wed, 19 Jan 2022 22:12:52 +0000

I decided to write about a real world problem solving experience using web development and programming skills in my day job. This project is one of my favorite because I was able to identify a business problem, propose an idea, and implement a solution all on my own over the course of a few short months. Working as the solo developer allows me great flexibility, but also requires a lot of time invested in a project such as this one. Continue reading to learn about my process and approach.

This is not a complete representation of the code that it took to solve the problem, any code shown has been modified to redact or remove any potential sensitive information.

Background

In my day job, I work with attorneys at a small legal firm as the sole IT Manager / Developer / System Administrator. The software we use allows me to create custom web apps that interface with our software and database to solve problems. When other attorneys file documentation with our office, our staff reads that documentation and creates a summary narrative of all the content on that documentation and enters that as text content into our software.

Problems Identified

There are several problems with this approach that are causing problems throughout the organization. These documents being filed are crucial to the administration of the case and the summary document is referenced many times over the lifespan of the case which is 3-5 years for most cases.

Time

Our staff spends several hours a week reading the documentation and creating summary narratives.

Consistency

With multiple staff members reading these documents, they had had their own flavor of creating the narrative. Each staff member had a unique way of writing, formatting, and styling their summary which created a variety of different styles that our two attorneys had to decipher each week.

Tracking Changes

The other attorneys may submit the same form, with subtle or major changes to the document between the last version and this version. Some cases have 6 or more versions of the same document filed over the lifetime of the case. Our staff must compare, line by line, the previous and current document and manually highlight the changes.

Human Error

Since the staff are reading, interpreting and writing their own version of the form, this introduced a lot of human error, resulting in the attorneys just reading the document themselves instead of the narrative.

Project Idea

While observing this business process, I came up with the idea of a web form that the staff could fill in the important details that change on these forms, and have the narrative completed automatically. Our primary case management software is a local web based application allowing full HTML, CSS, and JavaScript pages to run that can also connect into the SQL database where the data was stored which made a web based approach perfect.

Project Proposal

I met with the owner and proposed my web form idea on the basis:

I can create a streamlined approach that all staff will use for each form submitted, that will provide us the following benefits.

Faster Summary Narratives
Consistent Entries
Streamlined Formatting
More Accurate
Automatic change tracking

The business owner agreed to commission this project immediately. This project will take approximately 3 months and will be my main focus outside of daily job duties and IT priority issues that come up along the way.

Project Stakeholder Buy-In

The most important part of any major business project that will affect all staff is to get buy-in from the stakeholders. This ensures the greatest project success and is achieved by making the stakeholders feel that this project is going to benefit them and allow them to shape the final result of this project.

To accomplish buy-in, I held meetings with each staff member to understand how they created the narratives, what information on the form is important, the types of data that will be submitted, and expectations for the generated narrative.

Each staff member had a list of items that were important to them, based on how they were taught to do these narratives. I reviewed all my notes and found consistent items were the data being entered and the types of data. I used this to hold a series of compromise meetings to determine what would work best for the company and establish a baseline rule for the content, the format, style of the notes, and layout of the web form.

The staff feeling at this point is a little bit nervous because of the pending change to their workflow, but also excited about the idea of saving time and freeing up their time to be better spent elsewhere.

Project Steps

To start this project, I created a simple mock-up of the design that will be used for the various types of content. Some sections had check boxes, others had select drop downs, and others had tables of content being filled out. I used a blank copy of the form that attorney's fill out to give me an idea of the base line content that I needed to plan.

My code base allows for HTML, CSS, JS, jQuery, SQL and Bootstrap by default, and this project doesn't require anything more than these technologies to accomplish this project.

There are 20 sections to this document that hold various data from the plan number, to the options and tables of data contained within. I sorted my HTML code into 20 separate sections and started coding these sections one at a time. These sections are critical to the design and are a basis of tracking changes later in the project.

This layout gave me an idea for what type of content that I would be storing in the database. I designed and created a database table for storing all of the content and how it would relate to other tables in the system for consistency with how the other tables are stored.



Create Table tblCustomNarrativeData(
    PlanID INT IDENTITY(1,1) PRIMARY KEY, -- Auto incrementing primary key ID number 
    PlanNumber INT, -- Establishes PlanNumber for reference, defaults to 1 or value of amendeded plan doc number to track changes 
    PLanDate DATE,
    CaseID INT, --Foreign Key with tblCases to join on CaseID 
    SECTION INT, -- Section of worksheet variable that is updated throughout script 
    tblRow INT,
    COL1 VARCHAR(MAX), 
    COL2 VARCHAR(MAX), 
    COL3 VARCHAR(MAX), 
    COL4 VARCHAR(MAX), 
    COL5 VARCHAR(MAX), 
    COL6 VARCHAR(MAX), 
    COL7 VARCHAR(MAX),
    COMMENT VARCHAR(MAX),
    AMENDED INT,
    CONSTRAINT FK_CaseID FOREIGN KEY (CaseID) REFERENCES tblCaseData(CaseID)); -- Establishes FK for CaseID

After having the initial layout requirements and database ready it was time to start coding the layout. I started with HTML and setting up all of the content for each section of the form. There are 20 sections and each one holds a different set of content. I used Expanding and Collapsing sections to allow the staff to expand only the sections that required filling out and avoid overloading the screen with too many input fields. I also used jQuery to limit the table rows of each table section to just display 1 row by default, and expand up to 10 rows as needed for each section.

I tested sending data to the database and ensure we had information flowing as far as text box content ending up in the temporary table and permanent storage tables.

Saving Data (temporary)

To save the data as the user fills out the form I created an OnChange event handler that would write content to a temporary database based on the ID of the input field. This way, if the user experienced any issues or needed to close and re-open then all content will be saved until submitted.

Submitting Data to Database

At the conclusion of completing all of the appropriate fields, the user then clicks Update which executes a SQL script on the server. This script takes all of the data from the temporary database, checks through every section for content, formats data as needed into Dates, Money, VarChar, and other data types, and pushes into my custom table in the database.

The narrative text is stored in another table of the database. I wrote a second script that runs at the conclusion of the previous script to gather all of the database records from the data just entered, and then pushes an update to the narrative text table. This data had to be formatted different based on how it will be displayed in the software. The software is able to render pure HTML content for displaying narrative text to this user which allows me to customize how I present the data and create a standard format for all narrative notes. Using HTML combined with SQL, I pushed a full HTML body section to the narrative table that includes tables, checkboxes, and calculations, and other values to be rendered. This ta6ble stores it as a varchar(max) but when ready by the software it's converted into HTML so all of my HTML tags work.

Example of mixing HTML with SQL Query to generate a table, using inline styling because that's the only option that works when this is rendered in the software.



(SELECT 
    '</br><table style="width:auto;margin-bottom:4px;">
        <caption style="text-align:left;font-size:.8rem;font-family:Arial,Helvetica,sans-serif;"><strong> Plan Step</strong></caption>
        <thead>
            <tr>
                <th style="width:auto;text-align:left;padding:2px;border: 1px solid black;background:lightgrey">Start Month</th>
                <th style="width:auto;text-align:left;padding:2px;border: 1px solid black;background:lightgrey">End Month</th>
    <th style="width:auto;text-align:left;padding:2px;border: 1px solid black;background:lightgrey">Amount</th>
            </tr>
        </thead>' + 

(SELECT '<tr>' + 
            '<td style="width:auto;text-align:left;vertical-align:top;padding:2px;border: 1px solid black;">' + COL1 + '</td>' + 
            '<td style="width:auto;text-align:left;vertical-align:top;padding:2px;border: 1px solid black;">' + COL2 + '</td>' + 
            '<td style="width:auto;text-align:left;vertical-align:top;padding:2px;border: 1px solid black;">' + COL3 + '</td>' +
        '</tr>' FROM tblCustomNarrativeData  WHERE PlanNumber = @PLANID AND CaseID = {Params.CaseID} AND SECTION = 40 FOR XML PATH (''),TYPE).value('.', 'VARCHAR(MAX)') + '</table>' )

Tracking Changes

This was by far the most difficult part of the entire project, but also the most rewarding to actually solve. The concept here is that when a new version of the form is filed with our office, we need to know what changed from the document, and bring attention to these changes so when the attorney reads the updated forum note, they know exactly what changed in the form without having to read the whole note. My idea for this was to use the HTML <mark> tag which adds a highlight, default Yellow, to all fields that changed.

Using SQL I needed to be able to look at the last form filed by form number, and the new form filed by form number, and compare the two sets of table data and see what changed. I also needed to know which sections had been changed, or if a section that previously didn't have content, now has content. To accomplish this, The data had to go through a series to checks to determine what changes happened if this was an amended form.



--Add Date to Plan for reference purposes 
Update tblCustomNarrativeData SET PlanDate = GetDate() where CaseID = {Params.CaseID} and PlanDate IS NULL; 

-- Need to determine if previous plan exists in tblCustomNarrativeData for this case. 
-- If no plan has been filed in new table then we don't want to track changes since it will highlight everything. 
Declare @PrevPlanV2Exists INT;
Set @PrevPlanV2Exists = (Select Case when (Select DISTINCT CaseID from tblCustomNarrativeData where CaseID = {Params.CaseID}) = {Params.CaseID} then '1' ELSE  '0' end)

--Change the PrevPlanID to account for the random plan numbers being saved in database
 SET @PREVPLANID = (Select TOP 1 PlanNumber from tblCustomNarrativeData where section = 111 and CaseID = {Params.CaseID} and PlanNumber <> @PlanID order by PlanID desc)

--CLEANUP PREVIOUS PLAN IN tblCustomNarrativeData to remove <mark> and </mark> for previous highlighting before comparing fields 
 Update tblCustomNarrativeData set COL1 = Replace(Replace(COL1, '<MARK>', ''),'</MARK>', '') where PlanID in ( Select PLanID from tblCustomNarrativeData where COL1 like '%<MARK>%' and caseid = {Params.CaseID} and PlanNumber = @PrevPlanID)

--Table Comparison
--Using Inner Join on same table by matching CaseID, Section, and Row, check to see if the data is different
--If the data is different, concatenation is done to add html '<MARK>' and '</MARK>'
Update tblCustomNarrativeData set COL1 = CONCAT('<MARK>', COL1, '</MARK>') where PlanID in(
Select d1.PlanID from tblCustomNarrativeData d1
INNER JOIN tblCustomNarrativeData d2 on d1.CaseID = d2.CaseID and d1.SECTION = d2.SECTION  and d1.tblRow = d2.tblRow
where @PREVPLANID <> 0 AND d2.col1 <> d1.COL1
and d1.section <> 111 and d2.section <> 111
 and d1.CaseID = {Params.CaseID} and d2.CaseID = {Params.CaseID} 
 and d1.PlanNumber = @PlanID and d2.PlanNumber = @PREVPLANID)

 --This function checks for new sections that were not part of previous plan and sets Amended to 1. 
        --Only works if not original and a previous plan exists in table  
        Declare @Amended VARCHAR;
        Set @Amended = (SELECT CASE WHEN dbo.udfGetWorksheetData({Params.CaseID},@WORKSHEETID,'CheckBox',112,0) = CAST(1 AS BIT) then '1' ELSE  '0' end) 
        Select CaseID, PLanNumber, Section into #scrows from tblCustomNarrativeData where CaseID = {Params.CaseID} and PlanNumber = @PlanID group by CaseID, PlanNumber, Section
        Select CaseID, PlanNumber, SEction into #sprows from tblCustomNarrativeData where CaseID = {Params.CaseID} and PLanNumber = @PREVPLANID group by CaseID, PlanNumber, SEction
        --Create 2 temp tables, using an EXCEPT clause, determine new sections and Set Amended to 1 
        Update tblCustomNarrativeData Set Amended = 1 where CaseID = {Params.CaseID} and @Amended = '1' and @PrevPlanV2Exists = '1' AND @PREVPLANID <> 0 AND PlanNumber = @PlanID and Section IN 
        (
            SELECT Section from #scrows
            EXCEPT
            SELECT Section From #sprows 
   );

    Update tblCustomNarrativeData 
    SET Amended = 1
    where PlanID in(
    Select d1.PlanID from tblCustomNarrativeData d1
    INNER JOIN tblCustomNarrativeData d2 on d1.CaseID = d2.CaseID and d1.SECTION = d2.SECTION and d1.tblRow > d2.tblRow
    where d1.CaseID = {Params.CaseID} and d2.CaseID = {Params.CaseID}
    and d1.tblRow NOT IN (Select tblrow from tblCustomNarrativeData where section = d1.SECTION and PlanNumber = @PrevPlanID and tblrow <> d2.tblRow)
    and d1.section <> 111 and d2.section <> 111
    and d1.PlanNumber = @PlanID and d2.PlanNumber = @PrevPlanID
    )

   --For rows with Amended (meaning new sections or rows, add <mark> </mark> to all column fields since they are all new)
 Update tblCustomNarrativeData 
 set COL1 = CONCAT('<MARK>', COL1, '</MARK>'), 
 COL2 = CONCAT('<MARK>', COL2, '</MARK>'), 
 COL3 = CONCAT('<MARK>', COL3, '</MARK>'),
 COL4 = CONCAT('<MARK>', COL4, '</MARK>'),
 COL5 = CONCAT('<MARK>', COL5, '</MARK>'),
 COL6 = CONCAT('<MARK>', COL6, '</MARK>'),
 COL7 = CONCAT('<MARK>', COL7, '</MARK>')
 where PlanID in(Select PlanID from tblCustomNarrativeData where AMENDED = 1 and PlanNumber = @PlanID and CaseID = {Params.CaseID} and Section <> 111)

The result of this code is that all modified data from the form is properly tracked and highlighted when it's rendered for display for the users. A full comparison is performed on every character of every box so all the user has to do is fill out the form, update any changed fields, and let the system do the rest of the work for them.

Summary Section

On the web form that I created, I also created a Summary Section, using JavaScript and JQuery, to build a live rendered view of what the narrative text would look like when it submitted. This allowed the staff to do a quick check and determine if they missed anything when completing the form before the information was submitted and saved to the database. The yellow highlights here are similar to what will appear in the narrative text section of the software once completed. This section live updates as fields are modified in the web form.

The End Result

This project was a success! The proposed project came together great and was rell received by the staff that use it every single day to fill out these forms when our office receives them.

Project Conclusion

This project went through several revisions and updates from first draft to production, and 5 major updates were derived along the journey, and over 5,000 lines of code were written to achieve the result.

The business and staff now benefit from the increased efficiency, improved accuracy, and consistent summary narratives. The attorney's are happy that now all notes going forward will be consistent and uniform, along with tracked changes to make amended forms clear on any changes made.

Tracking of changes is the single most important benefit of this entire project. Comparing two versions of a form, line by line, is very time consuming and often leads to missed changes. This new method ensures that all changes to the form are caught each and every time, and attention is drawn immediately to all changes to eliminate any guess work.

Overall, everyone benefited from this project and now over 75,000 lines have been written to the custom table full of content from this web app being used in less than 1 year. The staff have said this cut their daily work on filling out and comparing plans down from 20-30 minutes each document to 5 minutes or less for each document. If we look at this as 4-10 forms per day this is hours worth of saved time!

The best part about being a developer is being able to create a custom solution to a problem that isn't possible using something off the shelf. There is not a product that existed that we could have purchased that would have worked directly in our software, with our database, and achieved the same result as this custom development project has for the business.

I’m on @buymeacoffee. If you like my work, you can buy me a taco and share your thoughts 🎉🌮

Cover Photo Credit:
Photo by Sebastian Herrmann on Unsplash

Tutorial: Add Sitemap.xml and Robots.txt to Remix Site

Chris Benjamin — Wed, 19 Jan 2022 19:02:34 +0000

Purpose

I recently converted my website to Remix and needed to setup my Robots.txt file and Sitemap.xml for Google Search Console and SEO in general in the Remix method for serving up files.

This process was a bit different from previous static generated sites where I created and added the files to the build. In Remix, you must use their loader function and return the content in a specific format.

Examples:

This tutorial will guide you on a very basic Robots.txt and Sitemap.xml file for a Remix site. I am not generating or creating the content of my Sitemap dynamically, I am using static content at this time.

This tutorial assumes you already have a Remix app setup such as using the npx create-remix@latest bootstrap method.

This tutorial covers the JavaScript method, but using this for TypeScript would only require a few changes on the loader function for importing the type, the Remix documentation convers this nicely at the link below.

Remix Resource Routes

Remix uses Resource Routes to serve files via Get, Push, Put, Post, Patch, and Delete. These are not UI (User Interface) routes and they will not render the rest of the UI components when the route is loaded.

These Resource Routes are great for Sitemap.xml, Robots.txt, dynamic created files such as PDF's, webhooks for 3rd party services, and many more services. For full documentation, head over to the Remix Docs and read about Resource Routes.

Sitemap.xml Setup

For the Sitemap.xml we need to create a special file in our routes folder. Since we want the period (.xml) as part of our actual route name, we will have to escape it so that Remix will allow it to be part of the route.

Create a new file:
This can be done 1 of 2 ways, either escaping just the period character or the whole file name.

Period Escape: sitemap[.]xml.jsx
Full Escape: [sitemap.xml].jsx

This file will only contain a remix loader which will return a Response with our content. Below I will show both the JavaScript and TypeScript methods.

In the sitemap file that you added under routes. We are going to add a basic Remix Loader. This example includes a single URL in the list pointing at my business website, you would replace the url content with your own sitemap which should contain multiple URL's unless it's a single page app.

Add the following content:



export const loader = () => {
  // handle "GET" request
// separating xml content from Response to keep clean code. 
    const content = `
        <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
    <url>
    <loc>https://www.taco-it.com/</loc>
    <lastmod>2022-01-08T00:15:16+01:00</lastmod>
    <priority>1.0</priority>
    </url>
    </urlset>
    `
    // Return the response with the content, a status 200 message, and the appropriate headers for an XML page
    return new Response(content,{
      status: 200,
      headers: {
        "Content-Type": "application/xml",
        "xml-version": "1.0",
        "encoding": "UTF-8"
      }
    });
};

Perfect, now you will want to run your site npm run dev and make sure that your sitemap is rendering when you browse the route http://localhost:3000/sitemap.xml

You should see something like this:

Robots.txt Setup

The Robots.txt setup is going to be pretty similar to the Sitemap.xml file, instead we are serving up plain text and not XML content.

Create a new file:
This can be done 1 of 2 ways, either escaping just the period character or the whole file name.

-Period Escape: robots[.]txt.jsx
-Full Escape: [robots.txt].jsx

Sweet, now we just need to add our loader and return content for the Robots.txt file.

*Note this is a basic Robots.txt file as copied from Google Search Console and updated with my Sitemap URL, you will need to generate your own Robots file with appropriate settings, and update your Sitemap URL. *



export const loader = () => {
// handle "GET" request
// set up our text content that will be returned in the response
    const robotText = `
    User-agent: Googlebot
    Disallow: /nogooglebot/

    User-agent: *
    Allow: /

    Sitemap: http://www.taco-it.com/sitemap.xml
    `
  // return the text content, a status 200 success response, and set the content type to text/plain 
    return new Response(robotText,{
      status: 200,
      headers: {
        "Content-Type": "text/plain",
      }
    });
};

Sweetness, you should now have a Robots.txt route in your app.

Run your site npm run dev and make sure that your robots file is rendering when you browse http://localhost:3000/robots.txt

You should see something like this:

Conclusion

You should now be able to add your Sitemap.xml and Robots.txt files to your Remix website so you can begin the journey of implementing SEO and SEM to get your Remix site to show on search engines.

Note: Additional research is needed into setting up a proper Sitemap.xml and Robots.txt file. This is not a one size fits all solution, and I do not recommend using these basic settings for all websites.

Cover Photo Credit:
Photo by NeONBRAND on Unsplash

I’m on @buymeacoffee. If you like my work, you can buy me a taco and share your thoughts 🎉🌮

Tutorial: Add TailwindCSS to your Remix Project

Chris Benjamin — Fri, 14 Jan 2022 21:50:48 +0000

TailwindCSS 3.0 Setup with Remix

This is a super quick tutorial to get TailwindCSS up and running in Remix! I wrote this when TailwindCSS3.0 was released on Remix V1.1.1

This tutorial assumes you have already created your Remix app using the npx create-remix@latest command, if not, do that first then follow these steps.

Open your terminal and let's install TailwindCSS

npm install -D tailwindcss

We also need Concurrently for running TailwindCSS in our dev environment.

npm install concurrently

Perfect, now lets initialize Tailwind to create our tailwind.config.js file

npx tailwindcss init

Let's update our tailwind.config.js file in our application for their purge function for any javascript .js or .jsx file.

module.exports = {
  purge: ["./app/**/*.{js,jsx}"], // Here we are going to tell Tailwind to use any javascript .js or .jsx file
  theme: { extend: {

  } },
  variants: {},
  plugins: [], 
};

Perfect, now we need to update our package.json file with scripts to generate our tailwind.css file.
Update your package.json scripts section to match this

  "scripts": {
    "build": "npm run build:css && remix build",
    "build:css": "tailwindcss -o ./app/tailwind.css",
    "dev": "concurrently \"npm run dev:css\" \"remix dev\"",
    "dev:css": "tailwindcss -o ./app/tailwind.css --watch",
    "postinstall": "remix setup node",
    "start": "remix-serve build"
  },

Now when we run npm run dev it will generate a tailwind.css file in the root of our /app/ folder. We need to tell Remix that want to use this stylesheet. I'm going to set this up in our Root file so that TailwindCSS styles are imported to the entire site. Remix does this by importing our styles and using their links function to apply the stylesheet to the head of the HTML file.

Open your root.jsx file under (/app)
Add the following import statement and then update the exported links function:

import tailwindstyles from "./tailwind.css";

// https://remix.run/api/app#links
export let links = () => {
  return [
    { rel: "stylesheet", href: tailwindstyles }
  ];
};

Perfect, TailwindCSS is all setup in our Remix app!!!

Go forth and style beautiful apps and sites with amazing user experiences, because that's what Remix is all about!

I’m on @buymeacoffee. If you like my work, you can buy me a taco and share your thoughts 🎉🌮