3 Replies Latest reply on Mar 4, 2010 8:20 AM by philmodjunk

    Lookup problem



      Lookup problem




      I am struggling with a lookup.


      I have created a table called IMAGING that has the following fields:


      DATE            PROCEDURE            RESULT 1     RESULT 2       RESULT 3


      I have set up a table for the lookup for procedure _procedure



      Bone Scan

      Liver Ultrasound

      CT CAP


      This is the short version for result 1


      INVESTIGATION                       RESULT  1                                  

      Bone scan                                 No boney metastases

      Bone scan                                 Boney metastases

      Bone scan                                 Uncertain boney metastases

      Liver ultrasound                        Normal Liver

      Liver ultrasound                        Liver metastases

      Liver ultrasound                        Liver uncertain

      CT CAP                                       Normal

      CT CAP                                       Abnormal (cancer detected)

      CT CAP                                       Inadequate (incomplete or technically unsatisfactory)


      Result 2 would be


      CT CAP                                      T0

      CT CAP                                      T1

      CT CAP                                      T2

      CT CAP                                      T3

      CT CAP                                      T4

      CT CAP                                      TX


      with no results  for bone scan or liver ultrasound


      I am not sure how to set this up so that the drop down  for PROCEDURE shows the investigations and Result 1 only shows the results for the according to the PROCEDURE.


      My first problem is with the lookup and the second with the relationships.


      Thanks for helping out with this












        • 1. Re: Lookup problem

          You refer to Procedures, Investigations and Descriptions in your examples. Are these all one and the same item or different in some way?


          You describe one table: Imaging, but then refer to other tables and fields without describing them. How do they need to be related?


          I need a more complete picture here to understand the problem.

          • 2. Re: Lookup problem

            Hi Phil,


            What I am trying to do is create a lookup for INVESTIGATION  and then the RESULT drop down is dependant on that investigation. 


            This is why I am struggling....  I can do a lookup ok for INVESTIGATION butwhen it comes to doing the lookup for the RESULT 1 I am lost.


            I thought that i needed to create a lookup table with the investigation and results in a second field with a record for each one.



            The variables (cut down version) for EXAMINATION are:




            Bone Scan

            Liver Ultrasound

            CT CAP


            Each of these has specific results that need to be in RESULT 1 viz:


            RESULT1::INVESTIGATION        RESULT1::RESULT  1     

            Bone scan                                      No boney metastases

            Bone scan                                      Boney metastases

            Bone scan                                      Uncertain boney metastases

            Liver ultrasound                            Normal Liver

            Liver ultrasound                            Liver metastases

            Liver ultrasound                            Liver uncertain

            CT CAP                                           Normal

            CT CAP                                           Abnormal (cancer detected)

            CT CAP                                           Inadequate (incomplete or technically unsatisfactory)


            So what i want in RESULT 1 dropdown is the appropriate results.


            Now relationship wise


            IMAGING:INVESTIGATION is a lookup to EXAMINATION::Description


            RESULT 1::INVESTIGATION is also a lookup into EXAMINATION::Description


            IMAGING::RESULT1 is a lookup to RESULT 1::RESULT 1


            So the effect I want is


            RESULT1::INVESTIGATION        RESULT1::RESULT  1     


            Bone scan                                      No boney metastases

                                                                      Boney metastases

                                                                      Uncertain boney metastases



            Liver ultrasound                            Normal Liver

                                                                     Liver metastases

                                                                     Liver uncertain




            CT CAP                                           Normal

                                                                     Abnormal (cancer detected)

                                                                     Inadequate (incomplete or technically unsatisfactory)


            Makes sense?



            • 3. Re: Lookup problem

              You are describing a conditional value list. There are several threads and a knowledgebase article on the concept.


               Here's a thread on setting up conditional value lists:

              Custom Value List?


              Check that out first and then let us know if you still have questions about how to do this.