3 Replies Latest reply on Jul 5, 2011 10:16 AM by philmodjunk

    Related fields from two tables

    dennizzlegbizzle

      Title

      Related fields from two tables

      Post

      Sorry if this is a repost, but this has been tricking me for a little while now.

      I have an orders database that created orders for customers and is specified by the orderID field. In the layout there is a portal going to a table that lists all the products for this order. These are related by orderID. 

      I have another field that describes whether or not the order will be shipped.

      I pretty much want to create a field in the products table that reflects what the shipping method in the orders table is. So if it changes in orders, it will change in products.

      Is this done through a calculation or a lookup?

      Am I way off base?

      Thanks,

      Dennis

        • 1. Re: Related fields from two tables
          philmodjunk

          Does that mean you have this set up for your tables and relationships?

          Orders---<OrderItems>-----Products

          Is Products the table where you have one record for each product?

          When I read your post, it seems like you would specify options in Products (or a table related to products) to control what shipping options are available for that product when it is added to OrderItems, but that is the reverse of what you are requesting here--if I understand your post correctly.

          • 2. Re: Related fields from two tables
            dennizzlegbizzle

            Phil,

            There is the customer orders table, a orderItems table, and a products Table.

            The customer Orders layout has a portal that creates records in the orderItems table. Basically what I want to do is have a ship method field in the customerOrder table and a deliveryMethod field in the orderItems table as well. THe reason being that I want to be able to do inventory of certain products that need to be shipped. For example, we could do a search for "MacBook" and ship in the orderItems table and return results. 

            I cannot have a related field in there, as the way this inventory control was designed (Way before I took this job) refers to a lot of relationships and this seems like the only logical way to do this (without redoing the whole system).

            Thanks,

            • 3. Re: Related fields from two tables
              philmodjunk

              I'm afraid I don't understand here. In you first post, you said:

              I pretty much want to create a field in the products table that reflects what the shipping method in the orders table is. So if it changes in orders, it will change in products.

              Why do you want a change in orders to change a value in products? Couldn't that be different for every order?