Note Tracker Template 2.0 User Guide

Introduction

The Note Tracker Template 2.0 added new features such as the data connection from the TOSA to Note Tracker and the auto-generated introductory language and notes placement XML tags.

It aims to reduce the following:

Manual transfer of data from the TOSA to Note Tracker
Manual writing of introductory language
Manual transfer of written notes from Note Tracker to Note Creator / EDEN Editor

In order to achieve the following:

More accurate data and output
Faster processing time
Less manual tasks for POCs and editors

You will need to set up the following:

Extractor - Data Connection and Power Query
Introductory Language Formulas
Notes Placement Formulas

Get started

Download the template from the Menu, under CI Projects, select Note Tracker Template 2.0 then click the download button.

Or you can download it from here:

Download Template

Or copy this link to your browser's address bar:

Copy

Click to enlarge

Sheets

The default sheets available in this template are the following:

XML - contains the XML tags for notes placement; hidden by default
Summary - contains a summary of note writing-related tasks
Note Tracker - the main repository for written notes

Deleting or renaming the default sheets might cause an error in the formulas.

Columns

If one of the default columns is not needed for your respective jurisdiction, you can:

Hide the column
Rename and reuse the column

Heads up! Do not transfer an existing column to an unused column. (e.g. Transferring the Effective Date column to column H - Extra.) That might cause problems with the formulas.

The column H - Extra column is an extra column that can be reused.

Before adding a new column, see if there's an unused column you can reuse. If none is available, start in column Q.

Deleting or reordering the default columns might cause an error in the formulas.

A. Session
B. HB/SB
C. Act Chap
D. Act Sec
E. Effect
F. Effective Date
G. Normcite
H. Extra
I. Intro Language
J. Note Text
K. Note Writer
L. Date
M. Validated
N. Note Placed
O. Date Placed
P. XML-Notes

Formulas

The formulas you need to add are the following:

Introductory language for cell I2
Notes Placement XML tags for cell P2

To easily apply the formula to an entire column, you can do any of the following:

drag the fill handle to as many rows as possible
double click the fill handle (this won't work if adjacent cells are blank)
press the following shortcut keys:

Click on cell I2 or P2
Ctrl + C
Ctrl + Shift +
Ctrl + V

Check the formula bar to verify if the formula is applied correctly.

For example, this is the content of cell I2:

If you click on cell I3, it should be

Notice that $A$2 didn't change. That's because it's an absolute value and a reference to the session year in cell A2.

The formula for the introductory language goes to cell I2 and the formula for XML tags goes to cell P2.

Session Year

The session year in A2 determines the year that would be generated in the auto-generated intro language.

Be sure to replace the default 2022 for succeeding years.

There's no need to fill the entire column with the session year. Only A2 will be referenced by the formulas. This helps reduce the volume of data in the tracker.

Before getting started, make sure that the source document (TOSA) is uploaded to the Sharepoint folder. We won’t be able to establish the data connection if the source document is not in the Sharepoint folder.

From the JX Note Tracker template, see below steps:

Step 1.

Click Get Data->From File->From Sharepoint Folder.

If a pop-up window appears regarding the use of access for Sharepoint content, please select Microsoft account and log in with your legal ID and password.

Click to enlarge

Step 2.

*Copy and paste the Site URL below then click OK.

Copy

Click to enlarge

Step 3.

Click Transform Data.

Click to enlarge

Step 4.

Power Query Editor window will open. In the Extension column, select only .xlsb or .xlsx to narrow our search.

Power Query Editor – Extension column: as our data source gets bigger, we need to click on “Load more” to make sure we can see all available formats since the .xlsb and .xlsx files are usually at the end.

Click to enlarge

Step 5.

From the Name column, select the JX TOSA, then click OK.

Click to enlarge

Step 6.

In the Content column, Row 1, hover on the word “Binary” until you see a pointing finger cursor and left-click on it.

Click to enlarge

Step 7.

Look for the TOSA under Name Column and in the Data column, hover on the word “Table”.

Click to enlarge

Step 8.

Click the Table icon, from the dropdown menu, click on Use First Row as Headers.

Click to enlarge

Step 9.

Select the necessary columns (may differ for other JXs): HB/SB, Act Ch, Act Sec, Effect, Effective Date, Normcite, and As Entered Order. Then click on Remove Other Columns to keep only the selected columns.

“As Entered order” column is only applicable to CA and NV. For standard JX, the TOSA is updated by chapter number. The columns you select will be extracted by the PowerQuery Editor, so make sure to apply the necessary filters before closing and loading.

Click to enlarge

Step 10.

In the Effect column, we will filter and select only amended and/or amendedAndRenumbered.

Click to enlarge

Step 11.

As Entered Order should be automatically arranged from lowest to highest.

Click Close and Load

Once you click Close and Load, a separate sheet will be created with the extracted data from the TOSA. Use this as a separate sheet just for data connection and extraction purposes. Use the note tracker sheet for writing and updating notes.

Click to enlarge

Step 12.

The data connection will be established, and the selected columns will be extracted from the TOSA.

Rename the sheet and Query1 as AmNotes Extractor.

Click to enlarge

Step 13.

Get the latest data by clicking on Refresh. The latest data should appear after the last row.

Changes in data entries will also be updated when you click Refresh.

Click to enlarge

Step 14.

Copy the latest data from the AmNotes Extractor sheet to the Note Tracker.

Click to enlarge

For the introductory language, copy the formula for your respective jurisdiction then paste it in I2.

AK

AL

AZ

CA

CT

FL

HI

IA

IL

IN

KS

LA

ME

MI

MN

MO

MT

NE

NH

NJ

NM

NV

NY

OH

OK

OR

PA

SC

TX

UT

WA

WI

WV

WY

RSV

If a jurisdiction isn't included in the list, it's because the intro language or note tracker of that jurisdiction is complex/non-standard (formatting is less predictable or there may be too many variations).

For notes placement, there are two options: Standard or w/ SubType Heading.

Most jurisdictions usually use the Standard XML tags. But if a jurisdiction (e.g. CA) needs a SubType Heading, copy w/ SubType Heading and paste it in P2.

Standard

W/ SubType Heading

Before placing notes in an NCD job, make sure the formulas and data in the note tracker match the correct rows and are not misaligned.

The following are the common errors during notes placement:

Unable to complete an NCD job. Error message: Missing LNI. Fix by saving the changes to generate the LNI for newly placed notes.

Unable to paste notes from the Note Tracker to NCD job. Look for a line break or hard enter in the note/s (Column J) and delete it.

If the jurisdiction's note tracker already uses auto-generated intro language, then it should no longer be manually written as part of the note. Editors should start by describing the amendments.

Add a comma at the beginning of the note, as needed (e.g. “..., in (a)(1),”).
Heads up! If the auto-generated intro language already has a comma after it, then it shouldn't be added.

To place notes, copy the XML tags from column O for notes that you're going to place then paste it in a Create Note Collection Job.

For any concerns regarding this project, kindly reach out to your respective supervisors, project coordinators, or POCs. Alternatively, you may directly reach out to us via MS Teams or Email:

Aldrin Renz Cruz | ---------------------------

This project is constantly being improved as we gather more information from user experience and feedback. If you've found any bugs or errors, it will help us immensely if you'd let us know about it. Suggestions are very much welcome!

#	Edited By	Date Edited	Comments
3	Aldrin Renz Cruz	December 1, 2021	Revision of document content and structure
2	Aldrin Renz Cruz, Roy Wilson Villafania	January 25, 2021	Addition of Extractor; updated formulas
1	Aldrin Renz Cruz	September 12, 2020	Document created

Note Tracker Template 2.0 User Guide

Getting Started

Introduction

It aims to reduce the following:

In order to achieve the following:

You will need to set up the following:

Get started

Components

Sheets

Columns

Formulas

Session Year

Extractor - Data Connection and Power Query

Step 1.

Step 2.

Step 3.

Step 4.

Step 5.

Step 6.

Step 7.

Step 8.

Step 9.

Step 10.

Step 11.

Step 12.

Step 13.

Step 14.

JX-specific intro language

AK Copy

AL Copy

AZ Copy

CA Copy

CT Copy

FL Copy

HI Copy

IA Copy

IL Copy

IN Copy

KS Copy

LA Copy

ME Copy

MI Copy

MN Copy

MO Copy

MT Copy

NE Copy

NH Copy

NJ Copy

NM Copy

NV Copy

NY Copy

OH Copy

OK Copy

OR Copy

PA Copy

SC Copy

TX Copy

UT Copy

WA Copy

WI Copy

WV Copy

WY Copy

RSV Copy

RSV Copy

Notes Placement XML Tags

Standard Copy

W/ SubType Heading Copy

Troubleshooting

For note writers

Contact

Continuous Improvement

Document History

AK

AL

AZ

CA

CT

FL

HI

IA

IL

IN

KS

LA

ME

MI

MN

MO

MT

NE

NH

NJ

NM

NV

NY

OH

OK

OR

PA

SC

TX

UT

WA

WI

WV

WY

RSV

RSV

Standard

W/ SubType Heading