An overview and end user troubleshooting guide

On Lok Importer

The importing tasks used to integrate the On Lok Pace Link Scheduler with Easy Rides is performed by the ERWebServer.exe which is a service that runs on the Production Web Server environment and has no end user interface except for the Resolve Addresses tool.

There are some basic things to know about how the importer works, and it is imperative that anyone troubleshooting import issues has a good understanding of these basics to avoid incorrect conclusions about the cause of the issue.

Overview

The Importer is a function of the ERWebServer.  For this document (and communications about the importing tool) we’ll call this instance of the ERWebServer the “Importer”.  The Importer is running as a separate service from another copy of the same program and folders that is handling traffic between the tablets and the database.  For simplicity we’ll call that one the “Tablet Server”.  They are both running on the same environment (Production Web Server).

The importer connects to the Easy Rides database and monitors two tables for data coming in from On Lok’s Scheduler software:  The Client Intermediate table (ol_intermediate_client) and the Trip Intermediate table (ol_intermediate_trip) which are inside and part of the Easy Rides database.  The Intermediate tables serve as a translator or a transfer point between the two systems (Easy Rides and Scheduler).  Records in the intermediate tables come from another utility or automated tool that is writing from On Lok’s Scheduler software.

People who receive transportation services are called “Participants” by On Lok and “Clients” in Easy Rides, but both names should be thought of interchangeably.

When a new participant is added in Scheduler, the record is added to the intermediate clients table.  The importer is looking for records with a modified time that is greater (later) than the last time the importer finished processing records or auto import time stamp check, so when it sees this new record with a newer modified time it looks for a matching client (Name AND Person ID). 

If no match is found then it checks the addresses and if an exact match is found in Easy Rides, it imports the client.  If the address isn’t found, then the Resolve Address tool pops up to the Easy Rides user and the user must resolve the address (give it a geocoding) and “Resolve Address” which adds it to the database.  If a matching client is already in Easy Rides, then the existing Client is updated according to the data that’s in the intermediate table.

When a trip record is added in Scheduler, a copy of that record is added into the trip intermediate table.  The Importer looks for the modified time of those records to see if it’s greater than (or later than) the time stamp of the last job that the importer completed.  If the modified time of a record is later than the Importer’s most recent times tamp then it updates a matching record (if found) or adds a new one.  This could cause duplicates if you have edited the requested times, trip date or addresses – the importer then no longer sees a match for the trip in the intermediate table.

Critically Important Points You Must Understand to Troubleshoot

• The Importer matches a trip (of which there are usually many copies or version in the intermediate table) to a trip in the trip tickets table based on name, PU Request Time, DO Request Time, PU Address and DO Address. In most cases this is enough information or “circumstantial evidence” to say “yes, this is the same trip” but it’s not DNA evidence or proof positive. 
  • Let’s say that someone added a duplicate trip by mistake in Scheduler. The name, PU and DO times and PU and DO address all match (but for two different records in the intermediate table).  The Importer would import the first record it came across and then when it sees the second record it might say “I’ve already got that trip” and do nothing else, or it might update the already existing trip. 
  • Let’s say that someone in Scheduler later realizes the duplicate and deletes one of the trips. Which record in the intermediate table should be deleted?  If one of the records is marked as deleted in the intermediate table, it is possible that the Importer could delete the only trip it has for the participant in Easy Rides instead of leaving one good trip and one deleted trip because two records in the intermediate table could belong to just one trip record in Easy Rides. 
  • If we had a unique trip identifier in the intermediate table, it would be much simpler, faster and reliable to import and update trips. Because we don’t, there will be a potential for missed or duplicate records depending on many factors including user error or timing issues that cause the importer’s ability to match records to fail.  The Easy Rides importer cannot be expected to perform perfectly with an imperfect data set, but most of the time it does very well.
• The Intermediate table is “The Source of Truth” – meaning that if there is a conflict between a record in the intermediate table and a record in Easy Rides, then the Intermediate table wins, and that data is used to update the matching record in Easy Rides. This can cause something an Easy Rides user modified to be overwritten by the importer. 
  • Let’s say for example, that you moved a trip from the pool to a route. Later, an update is made to that trip from the Scheduler software – maybe the purpose, a note or whatever that is sent to the intermediate table with a new modified time.  The matching ticket in Easy Rides would be moved back to the Pool if that’s what the record in the intermediate table says.  This can also happen if the importer is processing a large amount of data and it takes a very long time to get to that record.  You might have edited the trip 5 minutes ago, but a change made in Scheduler hours ago that is just now getting processed could revert your changes to whatever is in the intermediate table.
• To import a record, an exact match for the address must be found in the Easy Rides database, and that address must have a lat/lon, a site and match the city on the addresses of the record being imported.
• When a record is modified in Scheduler, the existing record’s status in the intermediate table is marked as deleted and a new record is added.
• Although records in the intermediate table have a unique ID, a trip for a participant does not. When a trip is edited in Scheduler (and subsequently deleted and re-generated in the intermediate table) it has a new record identifier.  This means that there is nothing to absolutely guarantee that a direct link a specific record in the intermediate table to one in the Easy Rides Trip Tickets table can be made.
• Bad data or format of data (such as a line break in an address or name, etc.), improper date format, extra spaces, invalid characters, etc. could cause a record to fail to import because the Importer cannot process the data properly. While this is increasingly rare and isn’t normally found it can happen and should be considered as a possible cause of a problem.
• If an Agent in Easy Rides edits an imported trip and changes the PU or DO address or the Requested time (PU or DO) then the importer will lose its “connection” or match for that record in the intermediate table and it will likely not be handled properly if something changes for the trip in the intermediate table. This could cause duplicates (or what appear to be duplicates to the user but are correct handling of a record by the importer).
• Records that humans tend to automatically recognize as being the “same” may in fact NOT be the same at all to the importer. For example, “Sister Margaret Mary St Cloud” is NOT the same as “Sister Margaret  Mary St Cloud”, “Sister Margaret Mary St. Cloud”, “Sister Margaret Mary Saint Cloud”, “Sister Margaret-Mary St Cloud”, etc.  Could you spot the differences?  You will need to be able to for troubleshooting.  Furthermore, by rule duplicate names are allowed in the database so you will need to check other information to find a match such as MRN, Person ID, Home address, phone, DOB, etc.
• If the importer finds an exact match for a trip in the intermediate table and a trip in Easy Rides trip ticket table (Client name and Person ID, Trip Date, PU Time, DO Time, PU Address & DO Address all the same) it will not import another trip, but rather update the existing match found. The importer will add its name “Import” to the Agent in the Trip Audit but will not modify the original Scheduled by Agent field.  Trips imported will have the Scheduled by Agent field of “Import”.  This can be useful when troubleshooting the history of a trip.

Troubleshooting

Checking The Functionality of the Autoimporter

Inside of the  \\SFC01-1-VERAP01\EasyRides$\Reports folder called Importer Troubleshooting.mdb. MS Access file, There is a query called "1 Check | Autoimport Timestamp Checks" that replaces the old Autoimport TimeStamp Check query.  Additionally, I have numbered the queries to provide a clear path for troubleshooting.  The 8 and 9 queries are numbered that way to all room for expansion after 4.

When you open the first query, you will see three timestamps. 

Here is an explanation of what they mean and the order of check:

1. autoimportcheckTmSp:  This timestamp is recorded as a reference for the autoimporter to check for newer timestamps in the intermediate tables.  If any modified times in the Client or Trip intermediate tables are newer than that timestamp, then those records still have to be handled.  If the timestamp is recent (within the last minute or two) then likely there is nothing in the intermediate tables that is remaining to be processed.  If the timestamp is older than a few minutes, then it's likely processing records.  If it's very old (more than a  few hours) it is likely that it's processing a large batch job such as the end of day jobs or the "Stargate" weekly job.  If the timestamp is old, look at the other timestamps in this query.

2. importLastActionTmSp:  This timestamp is recorded every time the autoimporter processes either a trip or a client record.  It is the time on the clock that the last action took place.  If there is nothing left to process, then this timestamp will start to age.  In this case,  you should see that the autoimportcheckTmSp is staying current (within a few minutes and updating regularly when you refresh the query).

3. importMaxTripTmSp:  This is the last modified time on the last trip in the Intermediate Trips table that has been processed by the autoimporter.  It is NOT the time on the clock that the autoimporter handled the trip.  This way, you can narrow down where the importer is (or has stopped if it's not updating) in the Intermediate Trips table.   This timestamp could be a bit old if the autoimporter is processing clients or has finished processing the trips or has gotten stuck on bad data and could give you a starting place of intermediate trip records to start looking at.

Here is how to assess the different combinations of these timestamps:

Autoimporter is functional

• autoimportcheckTmSp current:  The importer is working and is caught up.

• autoimportcheckTmSp is old:  The importer could still be busy processing records or it might have crashed.  Look at the other timestamps.

• autoimportcheckTmSp is old and importLastActionTmSp and importMaxTripTmSp are both current:  The importer is still working on processing trip records.

• autoimportcheckTmSp is old and importMaxTripTmSp is old and import LastActionTmSp is current:  The importer is processing Client records and is not processing trips (because it's busy with Clients or it could be stuck on a trip with bad data).

In Short, if any timestamp is current then the Autoimporter is functioning.​

Autoimporter is not functional

• autoimportcheckTmSp, importLastActionTmSp and importMaxTripTmSp are all old and not updating:  The autoimporter is not functioning and may need to be started or restarted on the Web Server environment.

There are queries to check for problems.  If you find a problem in a query or suspect a problem, go through the troubleshooting paths below and in the order of the numbers on the query names.  Note that depending on the nature of the problem, the steps or order may be different.  You may have to check all paths or items and not just the first one that has a correct or incorrect result.

• Remember that the importer’s job is to replicate what’s in the Intermediate Tables into Easy Rides. Most of the time when something is unexpected, appears wrong or is missing in Easy Rides it’s because that’s what the intermediate table has and it’s the defined “Source of Truth”.
• Bad addresses are the number 1 cause of actual importing issues.

1. START ALL TROUBLESHOOTING STEPS WITH RESOLVING ALL ADDRESSES WAITING TO BE RESOLVED.
2. Check the Pace Link Scheduler to verify that the expected record is in there.
3. Next, look for the problem record (usually a trip) in the intermediate table. Use the troubleshooting queries (see Query Tools) as needed to help you find the right record in the intermediate table.
4. If the record is NOT in the intermediate table for the correct person, date, times, addresses and such and does or has a trip_status of “Deleted”, then check with Travis or designated On Lok IT person on discrepancies between Scheduler and the Intermediate table.
5. If the record IS in the intermediate table for the correct person, date, times, addresses and such and does not have a trip_status of “Deleted” and it still isn’t in Easy Rides then proceed to troubleshooting topic below.

Missing Clients

Potential Causes:

• No exact match for client’s address in Easy Rides.
• Field in intermediate table with invalid data or formatting (such as a line break (return), invalid date format, etc.).
• Client already in Easy Rides with same or similar name and same Person ID.

Things to Check

• Search for the last name in Easy Rides and if not found, search for the first name. Generally, it’s best not to type in all the letters in the name because you might not see the results you need.  If no apparent match exists, proceed to the next step.  If a match is found, edit the client in Easy Rides to see if the name, Medical ID, address, phone number matches the information in Scheduler or the intermediate table.  Medical ID in Easy Rides = MRN in the intermediate table.  If it all matches, that’s probably why it didn’t import.
• Use Query: Check | Missing or No Match in ER. This query will show you clients in the Intermediate Clients table that are not in ER or have no match for name.  If the client you’re looking for is on the list check the Address of the client.  If not, check intermediate clients to see if you can find a match there.
• If Client is in Intermediate Table but not in Easy Rides, check the following:

Troubleshoot Addresses

• Use Check | Intermediate Clients Bad Address. If anything shows up in the query, it must be fixed at the source (usually Scheduler).  After it’s fixed open or refresh the query to make sure the address is no longer in there.
• Use Check | Addresses Broken ER. If there are any addresses in that query, you must fix the bad address in ER:  Open ER and go to Tools>Addresses and paste the address into the Place Name field.  You may need to widen the field so that you can see the full address (because it might appear that there is a match if an address in ER matches what you’ve pasted on the first part of it but it could be longer).  Edit the address (or Add if there is no match) to make the Place Name an exact match and make sure the city, geocoding and site is valid.

If the address does not show in any of the check queries, copy the address from the intermediate table record you’re using.  Open ER and go to Tools>Addresses and paste the address into the Place Name field.  You may need to widen the field so that you can see the full address (because it might appear that there is a match if an address in ER matches what you’ve pasted on the first part of it but it could be longer).  Fix or add the address as needed in ER.

Check/Edit Modified Time Stamp

• Modified time stamp in intermediate table older than autoimport time stamp check. This will also be the case for all clients imported, but sometimes just updating the last modified time is enough to get it to import (maybe since the last attempt to import, an address was added or something else fixed that will allow the record to import).

Use the AutoImport Timestamp Check query to see when the last time the Importer finished a job was.  If the time is within a few minutes and it updates (with a refresh or re-open) it’s caught up and there are only the regular intermittent ongoing imports taking place.  If the timestamp is older it’s probably working on a bigger batch of records to process.  If the end of day or end of week batch is running (or has run recently to rebuild the intermediate records then the importer will take more time to process a large volume of records and your missing item may just have not processed yet.

• After adding the address or fixing any other problems found in the intermediate client record, you’ll need to carefully modify the Updated time in the record directly in the Info | All Intermediate Clients query with the exact same format and the time to a few minutes into the future. The format looks like this: “11/15/2017 2:34:25 PM” but without the quotes.  You must have the same single spaces and hour format (NOT 24 hour) so as you can see in the example, it’s not “11/15/2017 14:34:25 PM”.  It’s best to double click on each item in the timestamp instead of swiping to select.  That way you won’t accidentally select the space that’s needed between characters.  After you make your edit, you’ll need to click out of the field into another field or record to post the change to the database.

Missing Trips

Users must NOT change imported trips Requested Times or addresses.  Only change Scheduled times, because then the importer and check queries can’t match the record properly.  Changing the address of a trip from the original it was imported with causes it to no longer match the intermediate table which could result in a duplicate or extra trip and will fail to get updated with any trip changes/rebuilds from the Intermediate table.

Potential Causes:

• No match for the expected trip in Intermediate Trips table (or the matching trip is marked as Deleted). This is the most common cause of missing trips.
• No match found for client in Easy Rides.
• No exact match found for one of the Addresses.
• Trip ticket for same client, date, PU and DO times and addresses already found in ER (even if created by an Agent and not imported).

Things to Check

• Use the Check | Intermediate Trips Not in ER – Future query to see if the missing trip is in there. If your trip is there and the Modified time is old you might just have to find the trip in Info | All Intermediate Trips query and modify the last_modified time stamp as described in Check/Edit Modified Time Stamp  If the trip still doesn’t import after modifying the time stamp to a few minutes into the future and waiting until the AutoImport Timestamp Check is past the time you set as the modified time, then proceed to the next steps.
• If the modified time is from very recently today, try Check | Intermediate Trips Not Handled by Importer Yet query.
• Go through Missing Clients steps
• Go through Troubleshoot Addresses steps

Duplicate Trips

This should be rare, but usually occurs when an Agent has added a very similar trip (recognized as the same by a human but not the same exact PU/DO times and addresses).

Check the duplicates to see who created the trip originally – Import or an actual person.  Delete one and keep the best version of the trip.  Understand though, that if the trip’s Requested PU/DO times or addresses change at all, you might get another duplicate from the importer.

Trips marked Deleted

This usually happens when a record in the intermediate table has been changed to deleted (thus deleting your trip) but the replacement trip has not yet been imported. 

Check the AutoImport Timestamp Check and see if it’s only a few minutes old and updating every few minutes.  If not, your deleted trip may show up again.  If it is a current timestamp that is updating every few minutes, and your trip never showed up then contact Easy Rides support.

Troubleshooting for Bad Address:

• Exact match of address not in Easy Rides (Matches on Place Name and City)
• Address not Resolved (or can’t be resolved because it is too long or has invalid characters). Currently, addresses can have up to 80 characters.
• Address in Easy Rides but not geocoded (-1,-1 Lat Lon).
• Address in Easy Rides and geocoded but no Site (Site is “NA” or Site ID -1).
• City is not correct.
• No Address in intermediate trips table in origin_name or destination_name fields. This could indicate a line break.  Try copying the whole field then pasting it into a text editor or search field or something.  You may find that there is an “invisible” address in there.
• No City in intermediate table (origin_city or destination_city).

• Use the Addresses – Broken query, Intermediate Trips W Address Too Long,
• Copy the address from the query or table you’re using. Open ER and go to Tools>Addresses and paste the address into the Place Name field.  If an exact match is found in the database, check the Site.  If the Site is NA then edit the address and get it geocoded.  If the address is not there, delete the pasted address out of the Place Name and paste it into the Street Address field to add the address (Place Name must be an exact match).
• After adding the address or fixing any other problems found, you’ll need to carefully update the Updated time in the record directly in the Intermediate table with the exact format and the time a few minutes into the future.

Missing or Incorrect Information in Easy Rides

Sometimes the trips are there but there is something missing such as Provider or another item from a dropdown list.

Potential Causes

• Item in Intermediate record missing. If something is missing like an address or note it might not be in the intermediate table either.  The Provider (Center) in the Easy Rides trip tickets doesn’t come from intermediate trips however.  It loads from the Easy Rides Client ticket default for Provider.  And that comes from the Intermediate Client table.
• No exact match for the item on the dropdown list in ERUtility for that list (Funder, Provider, Route, etc.).

Things to Check

• Use Info | All Intermediate Trips to find the record. Make sure it’s not one that’s marked Deleted.  Check to see if the expected item is there (except provider).  If it is not, check Scheduler and check with Travis to make sure the intermediate table has the right information.  If it is there, proceed to the next step.
• Open the ERUtility and go into the Admin menu and locate the list you need to check. Look for an exact match of the missing item on the proper dropdown list and make sure that it is Active and not Inactive.  Add/edit or correct as needed.
• If the Provider is what’s wrong or missing use the Info | All Intermediate Clients to find the person and see if the Provider is set properly there. If it is, but the last modified time is old and this might be a recent change, you might need to Check/Edit Modified Time Stamp on the client to get the importer to re-evaluate and update the client in ER.  If it’s only affecting a small number of clients, you can just edit the client in ER and set the provider as needed rather than wait.  Future trips imported should be correct.

Notes

Data in an Intermediate Table that will cause problems

• Line Breaks in fields.
• Invalid formatting of data (dates, timestamps, number VS text, etc.)
• Duplicate records
• Null values or blanks in critical key fields (names, addresses, times, cities, person id, etc.)

The 6 fields on Matching Trips

• Intermediate client_name to ER trName (with a query – not a direct match)
• Intermediate person_id to ER cl_Item9 (with a query – not a direct match)
• Intermediate origin_name to ER trPUAdss
• Intermediate destination_name to ER trDOAdss
• Intermediate origin_rqst_depart_time to ER trPUTmOrgnl
• Intermediate destination_rqst_arrive_time to ER trDOTmOrgnl

The 2 fields on Matching Clients

• Intermediate last_name and first_name to ER clName (with a query – not a direct match)
• Intermediate person_id to ER cl_Item9 (with a query – not a direct match)

To match clients between an intermediate tablet and an Easy Rides table we need to first format the person ID from the intermediate table to a truncated format with brackets.

Query Tools

There is an MS Access File inside of the \\SFC01-1-VERAP01\EasyRides$\Reports folder called Importer Troubleshooting.mdb.  Inside of the Importer troubleshooting file there are a bunch of queries that can help identify a problem or help zero in on a potential cause.  They are grouped by name and here is a description of the query types:

Base – you usually don’t need to use these directly.  They are queries that gather data and put it into format that is used for another query.  You can open and view a Base Query if it helps you, but do not change anything in them or save any changes to the design if you’re prompted to when you close it.

Check – These queries are designed to help you check different things.  Use the one with the name that seems closest to what you are looking for.  If you don’t find what you’re looking for, try another query.

Clients – are centered around client tables and not trips.

Info – These queries have information that may (or may not) help or provide additional information that you can use.

Join – Like Base queries, these queries are used as foundations for other queries but may be useful to view.

Update – Update queries will edit/change/update records in the database.  Some update queries are harmless to run and might only change something like a trip note color.  Others have a more significant effect on records and should only be run by someone who has been trained on the specific update queries.

The queries and types of queries could be dynamic if we need to change or add to the list as needed, so we won’t try to list each one and describe what it does.  While the queries are designed to narrow down the data as much as possible for you, sometimes you need to use a pair of pliers as a wrench if the right wrench isn’t available.

Related articles

• Page:
  Troubleshooting Common Issues on Tablets
• Page:
  Missing Trips or No Notifications