Comparison of Fields of Different Lengths

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Comparison of Fields of Different Lengths

Postby Graham Smith » Mon Mar 21, 2016 4:09 pm

I ran across something that I'm not sure I had seen before. A query that was comparing a text field in two different forms was failing to spot a mismatch. Here is the scenario:

Field1 in FormA (imported data) was 10 characters long while Field1 in FormB was only 3. A query was looking for any records in FormA where Field1 didn't have a match in FormB. The data in FormA was from an import and Field1 should only be 3 characters long but it turns out a few records were longer.

The query failed to find these records when it did a match. It seems that the first three characters of the longer records in FormA were the same as a record in FormB and in a case like this, with mismatched lengths, Ffenics was only looking at the first 3 characters.

The problem was easy to solve by checking FormA first for any records where the field data was too long - something that was overlooked.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Comparison of Fields of Different Lengths

Postby Gil Fleming » Mon Mar 21, 2016 4:32 pm

Graham Smith wrote:The data in FormA was from an import and Field1 should only be 3 characters long but it turns out a few records were longer.


Rubbish in, rubbish out..... #-o

The weakness probably lies at some point where humans interact with the system. We try to place lots of control on data-entry and then use that verified data as much as possible throughout our workflow. As soon as someone has to key something, you have the possibility of error.
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Comparison of Fields of Different Lengths

Postby Graham Smith » Tue Mar 22, 2016 12:37 pm

For reasons that elude me, I've been involved with a lot of projects over the years that involve imports and/or exports. As a result, I'm pretty good at being able to set up things that are reasonably bullet proof. This particular case is a rather ugly import because it's coming from a spreadsheet that one or more people prepared. There are a number of cases where the data has to be converted, this is one of those.

It's really not necessary to go into all the details other than to say that something unexpected showed up in the data and a process that had been working well for three years broke. And it broke for the odd reason mentioned above.

What I'm not sure of is whether this is a bug (I don't think so) or my design problem (the likely problem). What makes this worth mentioning is that it's something I wasn't expecting and I'm quite sure I've done the same thing many, many times over the years without ever having a problem.

In a nutshell, "ABCDE" was evaluating as equal to "ABC" because the fields on the two sides were not the same character length. I have not taken the time to fully explore this and work things both ways but it's something for people to be aware of and watch out for. This was only caught because someone noticed something odd in the final import report.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 
 

Return to Ffenics 1.x

Who is online

Users browsing this forum: No registered users and 9 guests

cron