5 Replies Latest reply on Sep 1, 2009 2:43 PM by jdhelle

    Lookup Help



      Lookup Help


      I have a database with 3 tables - 1 Main Table, 1 contains lookup data for a Diamond Disc Recordings, and 1 contains data for Blue Amberol Recordings.


      My Main Table has multiple tabs - one for each type of recording.


      On the Diamond Disc Tab of the main table, I have a lookup field called 'Matrix Number' which searches a table for Diamond Discs and if a match is found pulls data into some of the Main Table fields one of which is a field called 'Number'.  In this case 'Number' would be the actual Album Number.   


      On the Blue Amberol Tab of the main table, I want to setup the a field called 'BA Number' to be the lookup field.  However, the 'BA Number' is the same field as 'Number' in the main table.


      Since 'Number' is defined as an 'Auto-Enter Calculation' for the Diamond Disc search, how can I also use 'Number' as a look-up field for the Blue Amberol Table? 



        • 1. Re: Lookup Help

          Perhaps your could merge your two secondary tables into one table? Just going by the brief description in your post, it seems possible that you could do that fairly easily. You might not need to do much more than add one more field to your merged table that shows something like "Diamond" or "Ambersol" to distinguish between the two sets of records.


          Otherwise, you're going to need two fields. One field would receive the lookup from the "diamond" table and the other from the "blue ambersol" table. A calculation field could then select which field displays the data if you need a unified field to show information from both sources.


          The calculation might not need to be anymore sophisticated than:


          Table1lookupField & Table2lookupField.


          Assuming that the same record never has looked up data from both tables.

          • 2. Re: Lookup Help

            What I have done is add a field to the lookup table called Type to give me two fields to match; both type of media and the media number.  


            Diamond Disc's have two numbers - Matrix and Coupling while Blue Amberol's only have one number.  So, I combined the Matrix Number and Blue Amberol numbers into one field - thank goodness they are not even close to being the same.  Now when I enter the Blue Amberol number it pulls data just fine.  However, I have a problem when it comes to the Diamond Discs.


            This is the calculation used or the Album_Title (Blue Amberol) If ((Medium=BA_DD::Type and Number=BA_DD::Matrix ) ; BA_DD::Title 1 )


            However, now I need to check Album_Title for a Diamond Disc - If ((Medium=BA_DD::Type and Number=Coupling_DD ) ; BA_DD::Title 1 )


            How can I put both these calculations into the Album_Title's calculated value field so that if either of these conditions is met, the title is pulled from the look-up table?  I have tried using both with an 'or' but it does not work.

            • 3. Re: Lookup Help

              Coupling_DD is a field with a Lookup auto-enter option? If so it should be empty for Blue Amberol and show a value for Diamond Disks.


              You can nest your calculations as shown:


              If ( IsEmpty ( Coupling_DD ) ; If ((Medium=BA_DD::Type and Number=BA_DD::Matrix ) ; BA_DD::Title 1 ) ; If ((Medium=BA_DD::Type and Number=Coupling_DD ) ; BA_DD::Title 1 ) )


              This may be overkill as you say the values for Blue Amberol and Diamond Disk are very different, but it should do the job if I understand your design/implementation correctly.

              • 4. Re: Lookup Help

                Here is an example of both:


                Blue Amberol:


                'Number' is the lookup field - looks for a match in 'Matrix'                         Number = 1501

                'Medium' can have a value of 'Blue Amberol' or 'Diamond Disc'                   Medium = Blue Amberol

                          Title = Semiramide overture 


                Diamond Disc:

                'Number' is the lookup field - should look at 'Matrix' for a match                  Number = 2789-B

                  'Medium' can have a value of 'Blue Amberol' or 'Diamond Disc' Medium = Diamond Disc

                                                                                                                                Title = Blumenlied 

                               CouplingNumber = 50052-L 


                When I enter a new record, the first two items requested from the user are the Medium and Number.  The requested Number and Medium must match in the lookup table to be valid.


                Hopefully this helps. 


                • 5. Re: Lookup Help
                     I found a solution - one table for Blue Amberol look-up and one table for Diamond Disc look-up - works perfect!