Intraocular lenses - Round 2
Thank you so much, Phil, for your answer to my previous post.
Based upon your suggestion, I now have one LENSES table with three types of records, each of which has a unique serial number that I have auto entered upon creation. They also have a field for a mfr serial number, but that only gets filled when I have physical possession of the lens (or we have inserted it into a patient’s eye).
Here are the three types of records that will exist in the LENSES table, which I will assign a letter to distinguish them:
“A” lenses that have an appt id but no mfr serial number. These records represent lenses ordered for patients but not yet matched to a physical lens.
“B” lenses that have a mfr serial number but no appt id. These are lenses that we have in stock but have not implanted.
“C” lenses that have both a mfr serial number and appt id. These are lenses that have been implanted and are no longer in stock.
1. “A” lenses: When my staff gets a batch of new appointments containing lens orders, they create new LENSES records for each one, with a style and diopter power (and the primary key auto entered SN), but no mfr serial number.
2. “B” lenses: When I get a bunch of new lenses to put in consignment, we enter them via their barcoded serial numbers. Each one gets a new record, and the mfr serial number, style, and diopter power are entered. My thought is to have the shipment entered into a separate "import" table, have my staff review the new batch for data entry errors, and then they click OK and the table records are imported in as a batch into my LENSES table (with my primary key auto entered SN added at that time)
3. “C": lenses: Only after a lens has been implanted does it get an appt id assigned (we can’t do it before surgery is done - too much could happen. It could get dropped, cut out and replaced, surgeon changes mind, etc.)
As you can see, I need a process that, after surgery, combines the A lens record with a B lens record to create a C lens record once the surgery is done and the lens implanted. Until that point, I will have extra records, and then after that point, I need to delete either the A record or B record and keep the one that has been converted to a C record. I am imagining a script that matches them up and deletes the excess record.
I also need a method that allows us check to see if we have the B (in stock) lenses we need. It should reflect the state of the LENSES table, of course, but I also want them to be able to do a physical check in our lens room (that has about 2,600 lenses in racks). I would probably make it accessible to a FileMaker Go iPad which they could take into the room. I don’t want us to rely solely on the computer.
It is important to add here that for a single cataract surgery, the RN will probably have at least two or three lenses (sometimes as many as 5) to be ready for any eventuality. Always we have two of exactly the same style and power, in case something happens to the first one, and then one or two to use if they must put in an anterior chamber lens or something like that. These are usually rubber-banded together with the patient’s label and brought in to the OR with the patient. As you can imagine, in the majority of cases, only the one lens is needed. At the end of the day, the unused lenses are returned to the lens room.