2 Replies Latest reply on Jan 24, 2014 3:19 PM by philmodjunk

    Intraocular lenses

    dsimonson

      Title

      Intraocular lenses

      Post

           My solution is going to be used to match intraocular lenses (IOLs) with patients.  I have a question of how best to structure the tables.  Here is the workflow:

           We have a large consignment of IOLs from several different mfrs, which contains most common styles and diopter powers, so in most cases, when we get a patient name and lens order, we search our stock and it is here.  However, sometimes it isn't and we have to special order it, and then of course we need to order replacement stock.

           So - I have a table containing all of our lenses in stock (LENSES), and when one is placed in a patient’s eye, I just change its attribute from “in stock” to “implanted”, and match its key field with a patient’s key field in the patient’s table.  This works great for lenses in stock and implanted.

           However, I am wondering how to treat these “pre-orders” - where I get a surgeon order for a particular patient coming up for surgery (my main table - APPOINTMENTS) and a particular style and power of lens, but no lens yet.  My staff currently creates printouts of patient appointments (which contain the lens order), and then checks our inventory and either notes that we have it or not on the printouts.  Very inefficient.

           In this pre-surgical stage, we are not identifying a particular lens (each lens has a unique serial number given by the mfr), but only a style and power.  

           Here is my question: should I create another table, called LENSES_ORDERED, that I use to handle these lens orders that don’t have serial numbers yet, and only exist as orders and requests?  

           My current relationships and proposed ERDs can be seen in the Lenses ERD screenshot. 

           Then, when the patient (APPOINTMENTS) actually gets a lens implanted, switch the lens id from LENSES_ORDERED to LENSES?  I’m stumped.  Thanks for any help!

           —

           Dan

      lenses_ERD.jpg

        • 1. Re: Intraocular lenses
          dsimonson

               Hmm.. I can't see my screenshot - it is telling me that I need to login to view it, even though I am already logged in.  

               --

               Dan

          • 2. Re: Intraocular lenses
            philmodjunk

                 It's there. I see it. It sometimes takes longer for the image to appear even though we can open and see the text.

                 It sounds like you mainly need to make one small change:

                 Don't link your table of lenses by the manufacturer's ID number for the lens. Use an auto-entered serial number field for that purpose. Now you can create a record for your pre-orders and it can be correctly linked where needed. When you receive the lens, you then update your record and record the manufacturer's ID in a separate field in the lenses table.

                 And you'll be able to easily find all such records by performing a find for Lenses records where the Manufacturer's ID number field is empty.