7 Replies Latest reply on May 8, 2009 12:47 AM by pz736

    Relational lookup from one db to another

    pz736

      Title

      Relational lookup from one db to another

      Post

      I have been trying to solve this for a few weeks and I am sure it is simple but I think I am missing the point, any help would be appreciated:

       

      I have 2 db's one is for holding information about material, its purchase price and purchase size, this then does a simple calculation and works out the cost per sq cm.

       

      The second db is for job's and the amount of material needed, I am trying to then take the amount of material needed, select from a drop down the material name (from the 1st db) this the takes the cost per sq cm from the first db and then calculates the cost of the total material used.

       

      Can someone please point me in the right direction as this is driving me mad.

       

      TIA

       

      -Gary

        • 1. Re: Relational lookup from one db to another
          philmodjunk
            

          Please describe your tables and layouts in more detail. There are several possible methods for doing what you want. I can't tell which would apply to your database.

           

          How many tables do you have? Describe any relationships you've created, what fields are used in the relationship?

           

          Are you using a portal in this layout? If so, describe it in detail.

          • 2. Re: Relational lookup from one db to another
            comment_1
               It looks like you are missing a third table that would join between Jobs and Materials.
            • 3. Re: Relational lookup from one db to another
              pz736
                

              Hi there and thanks for the prompt response, when I refer to db's I actually mean tables, so there are 2 tables:

               

              tbl1 has 7 fields, serial number, name, length, width, total area, sheet cost, cost per sq cm.

               

              Total area is a calculation (l x w) and cost per sq cm is also a calc field (sheet cost / cost per sq cm)

               

              tbl 2 currently has not a lot in but this is where I create a job with the size of material I need and then I want to select from a drop down field the material name from table 1 and then calculate the cost of the material needed based on the table1 material cost per sq cm (of the selected material type, i assume from a look up).

               

              I have not used any portal and the current relationship is name from table 1 to material name in table 2.

               

              I hope this makes some sense. 

               

              • 4. Re: Relational lookup from one db to another
                philmodjunk
                  

                I agree with comment, you need at least one more table. This sounds like your basic invoicing database and if so, you need one table as a "price list" that lists the materials available and their unit prices. That would appear to be your table 1. You need another table where one record represents one "job" that would appear to be your table 2. Your third table should list each item and the quantity to be used. Calculations in this third table will compute cost and other values you might need. In this third table, 1 record represents one item for one job.

                 

                Typically, you create a layout that displays one invoice record with a portal that lists each related record from our third table. When you enter data in your portal, you select from a drop down menu of values from your price list table (table 1) and unit price values are copied/auto-entered into the record from a related record in the price list table.

                 

                I could go on in more detail, but will stop here so that you can consider what I've posted and see if it makes sense to you.

                • 5. Re: Relational lookup from one db to another
                  pz736
                    

                  Phil, much thanks for this and it sort of makes sense but I am still a little confused about how the relationhip works and the lookup, any chance you could through together a simple example for me to walk through.

                   

                  my email is: gary at bt dot net

                  TIA

                   

                  -Gary

                  • 6. Re: Relational lookup from one db to another
                    philmodjunk
                      

                    Use private messages so your email can't be "scraped".

                     

                    Expect an email with "The sample filemaker file you requested." in the subject line.

                    • 7. Re: Relational lookup from one db to another
                      pz736
                        

                      Thanks and point noted about email address - I should know better :-)