Network Issue

[James Bott]

Jeff,

My advise is to avoid filters. Filters require sending the entire database across the network--even if you only have 1 record in the filtered data.

It looks like your example filter can be easily changed to an index, then you use scopes. This will only send the filtered records across the network. This could be hundreds, or even thousands, of times faster.

Also, I would not use a temp index. Again, building an index requires sending the entire database across the network. And as Tim said, eliminate the trims. I would also eliminate the alias references in the index. This allows you to open multiple copies of the database in different workareas (why is another topic).

Code: Select all  Expand view

index on upper(LAST)+upper(FIRST)+ID+MRN to ...
...
cLast:= upper(cLast)
cFirst:= upper(cFirst)
...
set scopetop to upper(last)=cLast .and. upper(first)=cFirst .and. ID==cID .and. MRN==cMRN
set scopebottom to upper(last)=cLast .and. upper(first)=cFirst .and. ID==cID .and. MRN==cMRN

go top

Other general points:

1) Insead of using upper(cLast) in the scope just convert it first. This only only requires one call to upper() rather than once for each record.

2) For ID numbers such as ID and MRN, these should be converted to all upper case in the GET and stored in the database in all upper case. This prevents having to call UPPER() in the filter and index. In the GET you use the PICTURE clause to force all input to upper case.

Upon reflexion, I'm not clear on exactly what you are trying to filter out. It looks like you have a screen that allows partial data entry--one or more fields of 4 fields. Is that what you are doing? Please clarify.

Also I am not sure why you are doing trimming. Is this to allow partial data entry? If so, you could do this preprocessing instead:

cLast:= upper(rtrim(cLast))
cFirst:= upper(rtrim(cFirst))

If you want to allow partial data entry of ID and MRN then also do:

cID:= rtrim(cID)
cMRN:= rtrim(cMRN)

And you will have to elminate the == in the scopes. However, I am not sure how valuable partial ID numbers are. I would think that someone either has the entire number or none of it.

OK, this is probably TMI (too much information) for one message.

Regards,
James

[James Bott]

Jeff,

OK, now that I have had my first cup of coffee, I realized my example scopes in my previous message are not right for partial matches. I will rethink this and post a new example when I am up to speed.

James

[TimStone]

James,

An index with softseek on should work just fine ...

Tim

[James Bott]

Elvira,

What is tdata class?.

TData is my enhanced database class. For more info go here:

http://gointellitech.com/program.htm

Regards,
James

[TimStone]

tData extends the tDatabase class provided within FWH. Used with tRecord, and you will find the simplicity of working with databases to be so powerful you won't ever use any other method.

It works perfectly with all FWH controls.

Tim

[James Bott]

Jeff,

Well, I'm embarassed. In my first post in this thread, I wasn't fully awake and my scope examples were a combination of scope and filter syntax which, of course, can't be done. So please ignore my earlier post.

For complete data, i.e. complete last name and complete first name, you could do something this simple:

index on upper(last)+upper(first) to whatever
set scope to upper(cLast+cFirst)
go top

And this would show all people with the given first and last name.

But with only partial matches finding the records you want gets complicated. Since you can't use syntax similar to filters with scopes, then you have to scope on one field only then filter the rest. Determining which field to use as a scope can be complicated. You could build four indexes, one for each piece of data. Then you have to pick which index to use as the scope. I guess just using the first non-empty field, something like this:

Code: Select all  Expand view

cLast:= upper(rtrim(cLast))
cFirst:= upper(rtrim(cFirst))
cID:= rtrim(cID)
cMRN:= rtrim(cMRN)

do case
   case ! empty(cLast)
      set index to last
      set scope to cLast
      set filter to FIRST=cFirst .and. ID=cID .and. MRN=cMRN
      go top
   case ! empty(cFirst)
      set index to first
      set scope to rtrim(upper(cFirst))
      set filter to LAST=cLast .and. ID=cID .and. MRN=cMRN
      go top
   case ! empty(cID)
      set index to id
      set scope to rtrim(cID)
      set filter to LAST=cLast .and. FIRST=cFirst .and. MRN=cMRN
      go top
   case ! empty(cMRN)
      set index to mrn
      set scope to rtrim(cMRN)
      set filter to LAST=cLast .and. FIRST=cFirst .and. ID=cID
      go top
endcase
 

This should still be quite fast--way faster than a filter on the entire database. Even if you just enter a name (first or last) you should only get a few dozen records in the scope (depending on the database's size), then you filter those.

Let's digress for a minute. The problem seems to be how to find a patient's record. Let's consider this in generalities.

Most common last name in the USA is Smith. It is 1 percent of the population.
http://names.mongabay.com/most_common_surnames.htm

So out of a million records, 1 percent, or 10000 records, will be Smith. But if you add the most common first name, James at 3.318% (http://names.mongabay.com/male_names.htm), you would get only:

10000 * .03318 = 331 records of James Smith's

OK, 331 names is still a lot for a user to browse through looking for one record. And without the ID or MRN, how would they determine this?

Let's consider DOB (date of birth). Assuming everyone lives to be 100 or less, we have:

1/365 * 100 = 0.0000274 or, 0.00274% chance of having any given DOB

So out of 1 million records, there will be 311 James Smith's, and 331 * .00274 = 0.906 James Smith's will have any given DOB. Now we have narrowed it down to 1 person out of a million. With a larger database, say 10 million, you will still have 9 people named James Smith with a given DOB so you will have to narrow those down using additional data, such as street address, phone, etc.

So, last name plus first name plus DOB seems to be a good set of information to find any given record out of 1 million records. Every patient will know these bits of information. However, the chance of a patient knowing their ID or MRN is probably slim to none. If the ID or MRN is known to the user these may be useful and they should be unique numbers so either one should be the only information needed. Actually, I am not sure of the difference between ID and MRN?

Anyhow, using my above scopes and filters, the user should be able to find anyone using either last, first, and DOB, or ID, or MRN. If they are entering ID or MRN, then that is the only field needed. This is assuming they have the entire ID or MRN.

Note that my above example of a DO CASE with scopes and filters will have to be modified to add DOB.

I am not sure how often the user doesn't have the patient's full name so I don't know how much of an issue it is to provide partial matching. Perhaps you could get even more sophisticated and try something like this:

Code: Select all  Expand view

index on upper(last)+upper(first) to ...
set scope to upper(cLast+cFirst) // assume complete full name
go top
if eof() // then must not be complete full name
   // add the DO CASE in my example above
else
   // set ID and MRN filters here
endif

Another idea. Once the user starts typing in either ID or MRN all the other fields are disabled. Then you don't need to program for all the different possibilities. If they have the ID or MRN they don't need anything else to find the record.

So many possiblities. Like I said, it can get complicated, but you could really improve the speed by programming for all the possibilities.

You might want to create some use cases before programming.

Regards,
James

[TimStone]

I would suggest a different idea that simplifies the search.

Add one field to the database. In that field put zipcode, last name, first name. This can populate automatically. Create your index on that. Then when searching type in zipcode, last name ... in a browse ... it should refine it quite nicely. In fact, just use the last two digits of a zip code.

Tim

[James Bott]

Tim,

Add one field to the database. In that field put zipcode, last name, first name. This can populate automatically. Create your index on that.

I like your idea, but I don't see the need for another field. Just index on the three fields:

index on zip + last + first to ...

However, if Jeff's need is to be able to search for partial data from any of the four fields, then this won't help. Also, zip won't help after someone moves to a new zip code area (and before the database is updated). Of course, you can have the same problem if someone changes their name but this is less likely than changing zip code.

James

[TimStone]

James,

I interpret his need differently. He is looking up a patient, and he doesn't want to fill in ALL the data. Thus, a browse works well as I showed in my example.

I added the zipcode after the numbers you provided.

In my case, I use a browse to do the search. Each of the columns is in an index. One field is Company, and if it is just a client, I have the system store Last + First names in that field. If I want to do a search on name, I click on the header ( thought it is in the name by default ) and start typing the last name. If I want to use phone number, I click on the header for that column and start typing the number. If I want to use account number ... well you get the picture.

With the browse, I can quickly find the client. I can double click on it to select the one I want.

I understood his need was to get a progressive search which is included in the xBrowse methods. If he needs to look up Samuel Dobrienskivask, he may find the record in the list with just Dob. However, James Smith will likely require him to input far more.

Anyway, that is the way I read the request and how I responded.

Tim

[Jeff Barnes]

Tim / James,

Lots of good ideas here... I'm going to have to play around to see what works best.
It would have been so easy just to do an INDEX ON ... FOR but with ADS Local (used for data encryption) the FOR clause just won't work.

To clarify, this is what I am wanting to do and why:

I would like to give the user the ability to quickly search by any (or all) of the four fields. An incremental search just makes it a quicker as they don't need to enter full names etc... (everyone is always in too much of a hurry these days)

While it's true that the patient will know their first name, last name and DOB, the patient will not always be on site when the user searches the data.
In many cases, the doctor will go back into the patient file after the patient has left the office. They will remember a name but not the DOB.

I would like to be able to keep it working as it did with filters but this is only an option for local installs not for network installs.

Tim, with your browse and clicking on the header to start typing.... Does this only allow for searching on one field at a time or can you search on multiple fields at the same time?

I'm going to try the Scope/Filter idea to see what kind of speeds I get. It would let me keep things the same for my current users, however I kinda like this browse search idea.

Thanks for all the valuable input guys ... it is very much appreciated

[James Bott]

Jeff,

Thanks for the clarifications.

Tim, with your browse and clicking on the header to start typing.... Does this only allow for searching on one field at a time or can you search on multiple fields at the same time?

You could use a seek and filter with incremental searching so it would work on multiple fields. The downside is that it is doing a seek and filter for each keystroke rather than once after the user presses a button. The key issue is speed, not possibility. The speed must be acceptable to the user. Or, you could provide two methods, one for speed and one when the fast method doesn't turn up the desired result. The method could be user selected, or automatic via programming (if no result, then try a different method).

You have the right idea--you need to do some testing of different methods. Let us know your findings.

What is the record size and count for a typical database you are using?

James

[Jeff Barnes]

James, for an average clinic it would be about 400 patients per year (for use with my app)... Some of these 400 patients would be repeat patients.
For an average hospital I would say the numbers would be more like 1000 patients per year. This number could increase if they had multiple systems connected.

[James Bott]

Jeff,

What is the record length? Structure?

James

[Jeff Barnes]

The dbf is not too large ... 17 fields, total field size is 453 chars.

[TimStone]

I'm sorry guys, but I'm confused as to why there is a problem here.

My customers may have up to 40 customers in a day. Some are new, and some are repeat. The numbers are in the thousands for customer records, and searches are all instantaneous.

Jeff, my clients are auto repair shops. They need to track customers, verhicles, workorders, history and even accounting, and it must all cross reference. Searching for customers, or vehicles, is all incremental. Often the shop forgets the name, and even the license. So they have to locate a customer by type of vehicle. Its all quite doable, and searches are instantaneous with the browse.

I can connect online if you want. I can show you how it works and tell you how its done. You'll find my contact info at www.AutoShopWriter.com

Tim