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

    Lookup problem

    mark_d2x

      Title

      Lookup problem

      Post

      Hi,

       

      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

       

      DESCRIPTION

      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

       

      Mark

       

       

       

       

       

       

       

       

       

        • 1. Re: Lookup problem
          philmodjunk

          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
            mark_d2x

            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:

             

            EXAMINATION::Description

             

            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

            OR

             

            Liver ultrasound                            Normal Liver

                                                                     Liver metastases

                                                                     Liver uncertain

             

            OR

             

            CT CAP                                           Normal

                                                                     Abnormal (cancer detected)

                                                                     Inadequate (incomplete or technically unsatisfactory)

             

            Makes sense?

             

            Mark

            • 3. Re: Lookup problem
              philmodjunk

              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.