1 Reply Latest reply on Apr 26, 2011 1:57 PM by philmodjunk

    Choose a field contents from a related table and display the corresponding value in another field

    JerrySiler

      Title

      Choose a field contents from a related table and display the corresponding value in another field

      Post

      My database has among others two tables, Materials & Products. The Materials table is where we store stock information in the field Stock_Number and calculate its square footage and price based on what we pay for the material and store this in the "Cost_Per_SqFt" field. I want to be able to build products in the Products table by selecting the appropriate Stock_Number from a drop down list and have the corresponding Cost Per SqFt show in the field in the Products table.

      I am able to access the Stock numbers from a dropdown list but for the life of me I cannot get it to display in the related "Cost_Per_SqFt" field in the Products table.

      Any help would be greatly appreciated.

        • 1. Re: Choose a field contents from a related table and display the corresponding value in another field
          philmodjunk

          How have you structured your tables. One item per record in Materials and one item per record in Products?

          You might need a third Bill of Materials table (like lineItems in an invoice) for listing multiple materials for a given product. A portal to the BOM can then be placed on the Products layout and used to link by Stock_Number to records in the Materials table.

          Products----<BOM>-----Materials
          Products::ProductID = BOM::ProductID
          Materials::Stock_Number = BOM::Stock_Number

          with those relationships, you can select a Stock_Number from a drop down in the BOM portal and see data from materials either be copied via looked up value field options or you can just include the field from the Materials table.

          Here's a thread that discusses the two different approaches and why you would use one or the other: 

          Auto Fill