Our Blog

How to use dependent dropdowns in Microsoft Excel

By Nicholas Piano
August 2, 2023

Dropdowns are a way to constrain the input of a cell to a set of values. This can be useful for data validation, or for making a spreadsheet more user-friendly. In this article, we will look at how to create two types of dropdowns in Microsoft Excel: the basic dropdown and the dependent dropdown.

Using this, we can expand to more complex dropdown dependencies and spreadsheet generation, including validation using the Axlsx gem in Ruby.

The basic dropdown

A dropdown takes its data from a list of values specified by a range. This could be a range of cells, or a named range. To create a dropdown, first select the cell you want to add the dropdown to. Then, go to the Data tab, and click on Data Validation.

Data Validation Menu

Specify the range, e.g. =Sheet1!$A$1:$A$5. Then, select the “List” option under “Allow”. You can also specify an input message and error message, which will be displayed when the user selects the cell. Note that the range can come from another worksheet.

Basic Range

And that’s it! You can now constrain the input of the cell to the values in the range.

The dependent dropdown

Things get a little more complicated when we want to …

Text manipulation using regex replace in VS Code

By Kevin Quaranta Jr.
July 31, 2023

Regular expressions are incredibly powerful tools that can make life easier for any developer. Being able to quickly and precisely parse text with the syntax you specify has kept regexes relevant from the ’80s through to today.

Visual Studio Code is an excellent modern editor and development environment. According to the Stack Overflow Developer Survey of 2022, it was far and away the most popular IDE for both professional developers and those learning to code—more than twice as popular as the second-place choice.

Like any great editor, VS Code supports using regexes to find and replace text. In this article, we’ll go over how to use this powerful regex mode.

To demonstrate, we’ll extract code values from a JavaScript object so that we can query a SQL database for content that contains one of the code values. We can use standard SQL query WHERE clause multiple matching:

WHERE code IN ('code1', 'code2', 'code3')

Or we can add the values to a regex alternation group separated by pipes (vertical bars) that we can use as a string match:

WHERE code ~ '^(?:code1|code2|code3)$'

Each has different query plans and performance impacts in SQL, but for …

Achieving Healthcare Data Interoperability

By Jarrett Smolarkiewicz
July 28, 2023

(Co-authored by Jon Jensen.)

Background

For many years, healthcare providers, public health experts, and software developers have been hard at work on standards and methods for interconnecting systems to share vast and ever-expanding patient and lab data.

In the late 1980s the non-profit organization Health Level Seven International released the HL7 data standard. That was updated many times up to the year 2007 with HL7 v2.5.1 which is still widely used today.

New healthcare data standards have emerged, including HL7 v3, the Consolidated Clinical Document Architecture (C-CDA), the Fast Healthcare Interoperability Resource (FHIR, pronounced “fire”), and more. These are not yet as broadly adopted as HL7 v2.5.1.

Under the Health Insurance Portability and Accountability Act of 1996 (HIPAA), most of this data is classified as PII (personally identifiable information) and/or PHI (personal health information) and must be kept secure and protected at all times, so that it will not be accessed without proper authorization or patient consent.

The data dilemma

Healthcare data systems now routinely communicate in many ways, but there are sometimes incompatibilities when patient …

SSH and Old Servers

By Josh Ausborne
July 21, 2023

We recently updated one of our backup servers from Oracle Linux 8 to Oracle Linux 9, a free rebuild of Red Hat Enterprise Linux 9, also known as RHEL 9. As with any newer OS, there are bound to be unexpected little differences which crop up and which need to be handled.

In the case of our backup server, we found that it could no longer SSH to older servers, which includes a couple running CentOS 5 (yes, there really are a few of those out in the wild), some CentOS 6, and one Debian 8. We saw similar connection problems when we first went from CentOS 7 to Oracle Linux 8, and we addressed the issues by creating a Host entry for the servers in our ~/.ssh/config file.

Here is an example of of the entry that we used previously:

Host old-server-1 old-server-2 old-server-3
  User root
  Hostname %h.example.com
  KexAlgorithms +diffie-hellman-group-exchange-sha1

This worked fine for a couple of years until we upgraded from Oracle Linux 8 to 9. At this point we started to see a different problem when trying to connect.

Unable to negotiate with <nnn.nnn.nnn.nnn> port 22: no matching host key type found. Their
offer: ssh-rsa,ssh-dss

Troubleshooting

One of the good troubleshooting …

CSTE Conference 2023 Retrospective

By Samuel Stern
July 14, 2023

End Point is proud to have been an exhibitor at the 2023 Council of State and Territorial Epidemiologists (CSTE) conference in Salt Lake City, Utah.

We are grateful to have had the opportunity to showcase our public health software suite offering, CasePointer, to numerous state, tribal, territorial, and international epidemiologists.

The conference served as a meeting place to hear about the latest research in the fields of informatics and disease surveillance, and discuss the future of data management on state and national levels, with many talks exploring the technologies that will become future standards and practices.

The End Point booth was abuzz with visitors getting hands-on demos using the EpiTrax software as well as learning about the other aspects of the CasePointer suite, such as the Electronic Message Staging Area (EMSA), our new disease reporting portal, and the EpiTrax NMI module.

The CasePointer demo highlighted its capability of streamlining the management of epidemiological data with a user-friendly and highly customizable interface.

The CSTE conference provides a valuable opportunity to engage with epidemiologists and gain insights into the daily obstacles they …

Unix tools & tips

By Muhammad Najmi bin Ahmad Zabidi
July 5, 2023

The command line interface (CLI) programs/​tools found on UNIX-like (*NIX) systems are among the most useful tools available to system administrators. CLI tools allow a system administrator to handle the *NIX systems either remotely or locally without needing to install Graphical User Interface (GUI) packages.

Almost twenty years ago, I bought two O’Reilly books: “sed & awk, second edition,” written by Dale Dougherty & Arnold Robbins, and “Mastering Regular Expressions,” written by Jeffrey E.F. Friedl. These two books were printed in the late ’90s, and I bought them in 2004. I remembered them recently when my co-workers and I held a few study sessions to learn regular expressions. When a page and chapter are mentioned in this post, I’m referring to the editions of these books printed in that particular year.

In this blog post, I’ll detail some common use cases I encounter day to day, as well as some related tools that help me handle them.

sed

During my regular work as a system administrator, I usually use sed (“stream editor”) to do string replacement across files and awk for log files or file analysis with arbitrary strings …

Rocky Linux 9 at Hetzner Robot Made Quick and Easy

By Jeffry Johar
July 1, 2023

Image by Jeffry Johar

Introduction

In my last blog post, I shared my experience of installing Rocky Linux 8 on my Hetzner robot server and subsequently upgrading it to Rocky Linux 9.

Rocky Linux project manager Brian Clemens said that method is not recommended and suggested using a boot kickstart for an automated installation. Thanks, Brian!

Despite my attempts to utilize kickstart, I encountered difficulties in booting my NVMe disk. During this process, I discovered another workaround for installing Rocky 9 using the installimage script. This method is also experimental, just like the previous one.

The Steps

1. Access the rescue mode (refer to my previous blog post if you need guidance).

2. Copy the existing Rocky Linux 9 image to Rocky Linux 8 and place it in the root directory with the following command:

   cp /root/images/Rocky-91-amd64-base.tar.gz /root/Rocky-87-amd64-base.tar.gz
   

The filename should adhere to the required naming convention for the installimage script to function correctly.

3. Launch the installimage and select the Custom Image option.

   

4. Configure the disk settings as required, and at the end of the script, select the image accordingly.

   IMAGE …

Migrating Rails 6 React to Rails 7 React

By Indra Pranesh Palanisamy
June 26, 2023

CasePointer’s disease reporting portal is built on React and Rails 6, and it’s time for an upgrade to Rails 7. This blog post will cover the steps, benefits, and challenges of migrating from Rails 6 to Rails 7, and offer valuable insights into the world of Ruby on Rails.

With the recent release of Rails 7, there are many new features and improvements to explore. One of the biggest changes in Rails 7 is the retirement of Webpacker in favor of using the native webpack for bundling JavaScript.

For those who are not familiar, Webpacker is a Rails gem which is a wrapper around the webpack build system that provides a standard webpack configuration and reasonable defaults.

Steps for migrating Rails 6 React to Rails 7 React

To migrate a Rails 6 React application to Rails 7 React, follow these steps:

1. Update the Rails Gem in the Gemfile

In your application’s Gemfile, update the Rails gem version to Rails 7:

-gem "rails", "~> 6.1.4"
+gem "rails", "~> 7.0.0"

2. Upgrade Rails packages

Upgrade the Rails packages using Yarn:

yarn upgrade @rails/actioncable --latest
yarn upgrade @rails/activestorage --latest

3. Run the Rails update task

Run …