Adding case numbers after the fact (Lance, help!)

[DLDavis]

I have a client database with several thousand records in it now. Should have done this at the outset, but didn't think of it.

Among the fields I have in the database...are DOB (date of birth) and Date_Seen. I was thinking of making a case number for each record by combining the digits from each of those 2 fields. RAther than doing this individually for several thousand records...was wondering if there is some sort of code that could do it for me? I would need it to:

1. Create a new field (case_no)
2. Read the entire database one record at a time.
3. Take the data from each case and cram it into case_no
as follows:

0304196401072004

This would indicate that the client's date of birth was 3/4/64 and the date I saw the client would be 1/7/04. I suppose I should add one more digit just in case, tho extremely unlikely, I saw 2 people on the same day who were born on the same day...so I could insert "a" or "b". Is there a way to do this via code...or am I in for a long, error prone...data entry day?

Thanks,

Dan

 

[Lewy]

Dan,

I seem to remember doing exactly as you described a long while age (by the way my birthday is actually April 3rd, spooky).

I think you should find that you can easliy combine the two fields using a query. Start a new query on the table, with all the fileds checked,putting example elements in the fields you wish to combine. Then in another field put in the calculation you wish to perform. ie. Calc XXX + YYY, as CaseNo. Then run the query. Then rename the answer table and all being well you have a table with all the information including a case number. Don't forget if you are renaming the answer table to the original table, ensure you have made a copy of the original!!!

On thinking through your problem, I also remember that there is a better way of ensuring table/record integrity. I cannot remember all the details but I am sure that Lance does - -it could well be his method of generating a key field rather than a simple autoincrement field. Mind you I couldn't understand how to set it up at the time.....

Hope this is of help,

Regards,

Lewy

Hope this h

You

 

[Langley]

I would use a tCursor. The test.db table has 3 fields: A (date),B(date), and Case(string)

var

tc	tcursor	
zMonth, zDay string
xMonth, xDay string

endvar


tc.open("test.db")
tc.edit()

scan tc:

if month(tc.a) < 10
	then	zMonth = &quot;0&quot;
		else	zMonth = &quot;&quot;
endif
if day(tc.a) < 10
	then	zDay = &quot;0&quot;
		else	zDay = &quot;&quot;
endif
if month(tc.b) < 10
	then	xMonth = &quot;0&quot;
		else	xMonth = &quot;&quot;
endif
if day(tc.b) < 10
	then	xDay = &quot;0&quot;
		else	xDay = &quot;&quot;
endif

tc.&quot;case&quot; = zMonth+string(month(tc.a))+zDay+string(day(tc.a))+string(year(tc.a))+xMonth+string(month(tc.b))+xDay+string(day(tc.b))+string(year(tc.b))

endscan

tc.endEdit()
tc.close()

Of course, you would want to add some error checking for blank records etc.. - but you get the idea.



Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

Dan,

Lewy and Mac have already provided good direction; however, I do have a few thoughts to add:

1) The query-based approach may work, but you'll have little control over the format of the combined date values. Because of this, I'd use a script implementing a technique like Mac's TCursor approach.

2) Mac's technique for converting the date values to strings can be simplified. Compare his stacked IF statements with the use of format in the following:

method run(var eventInfo Event)
var
   dtDOB,              ; Patient's DOB
   dtVisit     Date    ; Patient's Visit Date
   strCaseNo   String  ; Final Case No
   strFmt      String  ; Date Format String
endVar

   dtDOB = dateVal( "3/4/64" )
   dtVisit = dateVal( "1/7/04" )

   strFmt = "DO(%Y%M%D),DY3,DM2,DD2"
   caseNo = format( strFmt, dtVisit ) +
            format( strFmt, dtDOB )

   msgInfo( "FYI", CaseNo )

endMethod

Now, this takes your sample data values and then uses a format statement to present your date values in a format like the one you posted. I did change the order of things, though. In my experience YY, MM, DD order is more useful for searching. Also, I placed the visit date before the DOB so you'll have your records organized roughly by the date they came in, rather than their DOB. I think it'll make the data easier to use in the long run.

3) Now, your concern over duplicate values is a good one, for it is well within the realm of possibility that two paitents may share the same birthdate on the same date of service.

I typically handle this with a few different techniques, though the most appropriate one depends on how you're using the data. Are you placing all patient information (including their contact information) with the data documenting their visit?

If so, then normalizing the data may be a wiser approach, one that makes things easier on your in the long run.

4) Identifying a good primary key can be difficult at times. If there's no easy way to uniquely identify each record in a table, I generally use what are called arbitrary keyes, that is, I assign integer values for each record as they're entered.

I don't use autoincrement fields for this, primarily because they're no multi-user aware and they can be problematic when tables are repaired. RDA's Autokey product (

http://rdaworldwide.com/RDAprod.htm#autokey start)

is an inexpensive--and good--way to work around these problems.

Tell you what, consider what's been posted so far and then bring us up to date with where things stand.

Hope this helps...

-- Lance

 

[DLDavis]

Thanks so much, guys, for your instructions. I'm not a programmer, tho. I use P9 interactively. If you can tell me where to cut and paste the code to, I can do that. I have so much respect for you code guys. All that seems so complex to me. I can usually figure out how to get a program to do what I want it to to if I tinker with it long enough. I'll dump your text to a floppy and take it to the office to tinker with...2c if I can figure out where to plug in the code.

I'm assuming you guys were trained in this. It's not my profession..but was wondering where the average joe could go to learn some of it...the syntax and all. I can do a lot with a database if I know my way around it pretty good. I cut my teeth on PC-FILE, which I used for years. My philosophy is to find a few programs I can learn fairly well...and stick with them..instead of switching all the time and/or upgrading ad infinitum.