Numerous questions on FIND and SCRIPTS

[DonH]

I have a db for medical tests. It has the patient number, date of test, and results of the test.

I want to get the results for a date range with a certain result for the test.

Questions

1) How do I do a multi-field search?

2) Why when I enter a date range do the results fall outside that range?

ie 1/1/2003...12/31/2003 I get 1999 results. I've also tried >=1/1/2003 <=12/31/2003

Just as a note, I inherited this whole mess and the original coder (wasn't a programmer that's for sure) did not put constraints on dates to ensure valid dates so I have things like 3/1/0307 and 6/5/2009 as dates in here. I am working on fixing the bad dates.

Anyway, that is my first round of questions.

[-Queue-]

It sounds like that 'date' field may not be a date field but a text field, in which case 'range' has a completely different meaning.

[Lee Smith]

I'm a little unclear as what the expected results of your finds are suppose to be, or what you expect them to be anyway.

Am I correct in assuming that you have some bad dates you are trying to find and fix? The calculations you posted should all work for find valid dates in 2003. However, you appear to be wanting to find dates like 3/1/0307 and 6/5/2009.

The easiest way would be to do your find as before and then either click the Omit button in the find mode or after the find, use the Show Omitted in the menu. This will provide you with any records whose dates don't match your find request, such as 3/1/0307 and 6/5/2009.

HTH

Lee

[DonH]

Actually, I am trying to find all dates in 2003. I was just explaining why I was using the upper end date. If I didn't have any bogus dates then I could just look for anything >= 1/1/2003

However, either way, I am getting dates outside the range I am trying for. I did check and it is a DATE field.

I think it may have something to do with trying to search on multiple fields. Is that possible with FM?

[Lee Smith]

All request on the one form equals AND finds, each request on a separate form equeals OR finds.

So, provided that Queue isn't correct? Than I'm guessing you are doing OR finds.

Lee

[DonH]

What seperate form? All one page. I have the fields patientnumber, date, testresult. I go to FIND. Plug in a date and a result of say "POS". I get garbage out.

Are you saying I have to break it up into 2 FIND operations? How?

[Lee Smith]

G4qgetting no where fast. If your fields are Date format. and you are doing a one find in one field for the dates. What are you getting as a result, or what are you expecting to get that you are not getting.

Yor last reply stated "I think it may have something to do with trying to search on multiple fields" which means to me that you are seaching in seval fields at once. No?

[Lee Smith]

The first sentence should read

This is getting .....

[DonH]

Hmmmmmm Let me see how much clearer I can make the situation.

I have 3 fields

PATIENTNUMBER

DATE

LABRESULT

I want to find the patients who have a "pos" result in field LABRESULT with the DATE being in 2003

If it was in SQL it would read

SELECT patientnumber

FROM labresults

WHERE date between 1/1/2003,12/31/2003 AND LABRESULT = 'pos'

Is this possible?

[-Queue-]

Enter Find Mode

type pos in LABRESULT and 1/1...12/31 in DATE.

Perform Find (press Enter)

The year isn't necessary since you're looking for the current year.

If this doesn't work, perhaps your file was created in another region and you need to Set System Formats or enter your find as dd/mm instead of mm/dd.

[-Queue-]

'OR' finds are done using new requests for each selection (Records->New Request from the file menu). If 'omit' is checked, then it becomes a NOT (for single requests) or AND NOT (for multiple requests). Of course, it can get more complicated from there, but if you're an SQL'er, I don't doubt you can figure it out.

[DonH]

Okay, that is what I did and it doesn't work quite right.

Maybe what I need to do is check for NULL. How do I say NOT NULL in FM? I tried <>'', <> NULL and a few other variations.

I'm still getting records from 2002 and getting records that the results are blank or neg. But I'm only getting 51 now instead of 15,000

I'm real tempted to export the results and do this in SQL Server.

[-Queue-]

Null is "=" (as in equal to nothing), so enter that in Find Mode and click the 'Omit' box.

How are your dates formatted? Set them to show the four-digit year, if they aren't already. This might help you see something that's odd.

[Lee Smith]

Hi Don and Queue,

I see you made some progress on this while I was gone.

DonH said:

I'm still getting records from 2002 and getting records that the results are blank or neg. But I'm only getting 51 now instead of 15,000

And This:

I want to find the patients who have a "pos" result in field LABRESULT with the DATE being in 2003

So, you now have 51 records that meet the criteria of pos in LABRESULT and were returned as part of the find that you used with a date range of 1/1/2003..12/31/2003? Is that correct?

Is there anything different with these dates from 2002 when you eye ball them?

What happens if you sort them, do they sort in the date order?

Lee

[-Queue-]

If you could attach your file, we might be able to figure this out before Christmas, if you haven't resolved it already.

[Ugo DI LUCA]

If this is a frequent find, why not script it using a Self-relationship ?

g_Year (global)

g_LABRESULT (global)

cg_MatchResultAndDate = g_LABRESULT&"-"&g_Year

c_MatchResultAndDate = LABRESULT&"-"&Year(DATE)

SelfJoinMatch ---> cg_MatchResultAndDate::c_MatchResultAndDate

Enter "pos" in the g_LABRESULT and "2003" in the g_Year.

Draw a portal with this relationship or perform a script

GoToRelated Records(::SelfJoinMatch- show)

If this doesn't work....

[-Queue-]

Sure, scripting it will be cake once we figure out why a manual find fails to perform as desired.

[Lee Smith]

Hi Queue,

You and I are thinking along the same lines today (you took the words right out of my mouth here and I almost suggested the posting of the files earlier). Once we hear back from Don, maybe we can put this one to bed.

Lee

[-Queue-]

Yeah, Lee, this one is really bugging me. I'm thinking 'user error', but without a look at that file, I can't think of any instance where this would occur that we haven't already addressed. It must be something so simple we haven't been able to grasp it. The complicated stuff is what's usually easiest to resolve.

[DonH]

The file is huge and actually involves 3 different db. I just simplified it here to try and figure it out. Also, there is the problem of patient confidentiality. Can't let real data get out there.

I did find 1 of the dates as mm-dd-yyyy vice mm/dd/yyyy. When I tried doing it without a year I rec'd an error saying that the year had to be in the range of 0001 to 3000 and in the format mm/dd/yyyy. I also get a lot of mm/dd/yy results.

Well, I'm thinking this is a problem that goes back to the author of this whole thing. He was a horrible db designer and a marginal programmer (I'm being generous). Anybody who has had to pick up the pieces left behind by somebody else knows how difficult it can be to find little bugs like this one and squash them.

I'm looking to see if I have or can create a totally blank application. Of course I would have to get permission to post any part of it since it is proprietary to this hospital. (as if anybody would want to steal it )

Okay. I'm also thinking that maybe I am getting the results I am due to a repeating field. People who have multiple tests. But when I look at the results I don't see the mutiple entries, just the earliest dated one for that patient.

I exported my results and imported it to an Access db and I think that this may be the case. Many of the patients have had the tests repeated. So even if it is negative once it may be positive later, but I only see the early negative.

Whatcha think?

If so, why can't I see the rest of the results for each patient? I bet this is a DOH question. LOL I'll probably figure that one out before anybody answers.

[-Queue-]

Aha! Multiple dbs AND repeating fields. I KNEW you were withholding crucial information to solving this. Without seeing these dbs, they sound like a true cluster$%#*. What's the probability you can make these relational and much easier with which to work?

Regarding attaching the files, you only need post clones of the relevant dbs for us to be able to narrow the possibilities. You could even post clones stripped of all fields except those necessary for your function (and the natural coherency of the dbs). We don't require data to investigate the problem. We can create our own.

[DonH]

I will see what I can do. I've begged to have permission to redo the whole thing from scratch, even if it is in FM. (Personally I would go with Cold Fusion on a SQL Server db, but that is neither here nor there )

I will definitly have to strip it down. If I didn't, you'd take days figuring out just where to look even with me telling you. I think the author's name was Frankenstein. :lol

[Lee Smith]

Bingo

BTW, I don't see where you mentioned what version of FileMaker you are using.

Repeating fields are a holdover from the days before FileMaker became relational.

Lee

[DonH]

This is FM3.0.

I do have a single user copy of FM6 running for a different project that requires an ODBC connection to SQL Server. The hospital is actually changing over to a big ORACLE db with ASP front end, but when that will go into affect is anybodys' guess now days. I keep hearing "right around the corner". Meantime I have to try and keep this old FM3 on a Win2k Server running. A real adventure. Especially since the author was basically clueless about db design.

[jeffer]

But what happens when you do a scriptable find? Let's say that I want to find all records with an empty date field? Inserting the "=" symbol isn't gonna work:( It almost feels that filemaker has a difference in searching when it comes to scriptable searching against manual searching:(

greetz,

Jeffrey

[-Queue-]

Insert Calculated Result [datefield, "="] works just fine, actually.

[DonH]

My solution was to FIND all records within the date range. Then export them and import them into SQL Server and run SQL queries to get what I needed.

[Anatoli]

Maybe if you hire someone with good FM skills... And use some more up-to-date FM. This version is like DOS 5.1 old.