Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    Unanswered: Creative methods of counting folders containing a large number of records

    Due to very good guidance from Pat P., I am using the new medical code sets in my still-in-development project. The new medical code sets are rather large (71,924 unique codes for procedures and 69,823 unique codes for diagnoses). I need to count each type of procedure and diagnoses that the doctors perform. Given the size of the folder containing each type of record, using a Recordset to hold this group and scripting using 'for each.... next' is just not going to work. Couple that issue with each doctor assigning as many as 12 procedures and seven diagnoses to each patient and this has become a rather large mess.

    Does anyone have any wisdom that they would be willing to share regarding how to count this many different types of records (approximately 70K)?
    Daryl G

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unfortunately I don't know enough of the "BD Speak" to express what I want to say, but in a Relational database (like DB2, Microsoft, Oracle, PostgreSQL, etc.) this would be handled as a Foreign Key. The user would enter a value, and the database or front end application (in BD these are combined) verifies that the value entered exists in another table.

    I'm not sure what BD calls a Foreign Key, but I'm convinced that it has them and that they'll make this task trivial.

    If push comes to shove and you have to kludge things, you can almost certainly pare down both lists to a few dozen or hundred entries per employee... Most people in healthcare use between 2 and 25 of the codes for 99% of their entries.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    226
    Hi,

    I'm not 100% clear on your question, but to count the number of records in a recordset you can use a mathematical formula variable and one of the built-in Data Functions - "Number of Records (from recordset)"

    Also, in many cases using a query within your script is much more efficient then going through each record in a recordset using a cycle. Depending on what your needs are.

    Hope that helps

    Matty

  4. #4
    Join Date
    Apr 2013
    Posts
    226
    Further thoughts on this.

    I'm assuming that on the patient record you would have a many to many relationship field linked to the procedures form. The doctor would then add the relevant procedures to each patient as required.

    You now have a relationship field within the contact record containing the procedures relevant to that patient.

    It is quite easy to work with those records by creating a recordset variable of the form "relational field content".

    Here is how the script might look - Patient_Procedures is the relational field on the patient record that contains the list of procedures:

    1 [$patients] = Records from Folder("Patients", "No limitation")
    2 For Each Record From [$patients]
    3 [$content_of_rf] = Records from Field("Patient: Patient_Procedures", "All records in the field")
    4 [$count] = setRecN(|content_of_rf|)
    5 Show Message (This patient has [$count] procedures, , Ok)
    6 Next Item

    Or something along those lines. Hope that is of some help?

  5. #5
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    Matty,
    I have to be able to count each occurrence of the 71,924 unique procedures and 69,823 unique diagnoses records. A group of 36 doctors will generate approximately 8,000 records each quarter - this project captures a rolling three-year time window. Each of these records represent an individual patient visit. Each individual patient record can have 12 different procedures performed (not always is it 12 though it tends to average around five procedures per patient per visit) and a maximum of seven diagnoses (i.e., some patients have multiple health conditions such as diabetes, high blood pressure, fever... etc). In the case of diagnoses, the objective is to find the total number of patients that the doctor has seen a patient with diabetes, the number of patients with high blood pressure, the number of patients with fever, ...etc).

    I am looking at each doctor individually by each of the unique procedures records by filtering that doctor's individual patients and then counting all the matches. This worked fine until I added the entire list of records (71,924)... this volume brought Brilliant Database to a standstill. I spoke with my wife last evening and she, too, agreed with the wisdom of Pat Phelan and said that it is doubtful that they would even use any more than 100 different procedures and diagnoses.

    I certainly appreciate your assistance and one of the things that you mentioned in your "further thoughts on this" has provided another method that may pave the path to an eloquent solution. I am going to investigate that avenue later this morning! Many thanks.
    Daryl G

  6. #6
    Join Date
    Oct 2013
    Location
    Philippines
    Posts
    103
    Hi Daryl,
    One solution to start with is to create a many to many relational field in the patient record to store the procedure and maybe another for the diagnosis. In that way, things will be simplified.
    Thanks
    Ernie
    Last edited by mcmichael; 03-29-14 at 23:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •