13 Replies Latest reply on Aug 1, 2011 1:07 PM by philmodjunk

    FM 11 adv. Conditional Value List with Depth

    mihaly

      Title

      FM 11 adv. Conditional Value List with Depth

      Post

      Hi all,

      need some help with the following problem. I am making a database with products and invoices. For a costprice calculation, i need the price for the shipment of a container.

      Products are shipped from a port to a destination in a specific container. 

      There are serveral ports (Shanghai, Hong Kong etc), with several destinations (Rotterdam, Hamburg etc) in different containers (40' dry cargo, 40 feet high cube etc).

      My take on this is:

      Product Table:

      • __pk_product_ID
      • _fk_transportation_ID
      • Port
      • Destination
      • container price

      Transportation table with

      • __pk_TransportationPrice_ID
      • Port (value list with all values from Port table)
      • Destination (value list with values related values from Products Table)
      • container (value list with values from Container Table)
      • Price (inserted with above specifications, port and destination)

      Port Table with:

      • __pk_Port_ID
      • Port

      Destination Table with:

      • __pk_Destination_ID
      • Destination

      Container Table with:

      • __pk_container_ID
      • container
      • specifications (like cubic meters)

      My problem is the relationship to make it work.

      product table -->transportation table

      _fk_transportation_ID <-----------__pk_transportation_ID

      transportation table -->port table

      _fk_port_ID <-----------__pk_Port_ID

      transportation table --> destination table

      _fk_destination_ID<------------__pk_destination_ID

      transportation table --> container table

      _fk_container_ID<------------__pk_container_ID

      After that i am pretty must lost...got it to work with 1 level....but after that nothing. Any help on this would be much apreciated....

      btw....i read the several info on it...but its not working for me

      thxs

      mihaly

        • 1. Re: FM 11 adv. Conditional Value List with Depth
          philmodjunk

          Nothing wrong jumps out at me when I look over your relationships. What's the question about them that you have? Where does it break down for you?

          To sum up your post, you have:

                           Container
                                  |
                                  ^
          Product>-----Transportation>----Port
                                   v
                                   |
                              Destination

          Making Trasnportaion a "star join" table linking the other 4 tables.   (>---- means "many to one" )

          • 2. Re: FM 11 adv. Conditional Value List with Depth
            mihaly

            hi Phil,

            thxs for your answer.

            I have in my Product:port field a valuelist. It should state only the Ports I made in the Transport table...but its not working. The Transport table will have all the combinations stored, and the price. So my value list should for Product:Port is a valuelist that uses from Transport:Port (all values)...but nothing is showing and changing it to Port:Port ( related values from Transportation) is not working either. So I am making a big/small misstake sowhere....

            The same goes for the Destination field in the product table. It should state the destinations related to the port I choose...

            My guess was I am missing some direct relationship between the product table and an other one. (like Product:port |-------|Transport:port)....

            • 3. Re: FM 11 adv. Conditional Value List with Depth
              philmodjunk

              "I have in my Product:port field a valuelist. It should state only the Ports I made in the Transport table...but its not working."

              So you want a list of all possible ports for the current Product? (If there's a transportation record for this product to Shanghai but not Hong Kong, you want to see Shang Hai as an option but not Hong Kong.)

              That specifies a completely different relationship.

              As I understand the relationships that you've specified, a Product record is linked to Transportation by TransportationID from the transportation table. That indicates that one and only one transportation record can be specified for any given product, but may products can link to the same transportation record.

              What does one record in products represent? If you ship "Left Handed Widgets", do you have one and only one record for "Left Handed Widgets" or do you create a new Products record every time you ship them?

              • 4. Re: FM 11 adv. Conditional Value List with Depth
                mihaly

                "So you want a list of all possible ports for the current Product? (If there's a transportation record for this product to Shanghai but not Hong Kong, you want to see Shang Hai as an option but not Hong Kong.)

                That specifies a completely different relationship."

                That is correct....

                The cost of a container had nothing to do with the product (only the port, destination ans size container). That was my problem...i knew the diamant structure was correct, but apperently this is not the right relationship with the product.

                Product:__pk_product_ID -----> Transport:_fk_product_ID?

                • 5. Re: FM 11 adv. Conditional Value List with Depth
                  philmodjunk

                  It depends on how you set up the data in your tables. That's key to identifying whether relaitonships are one to many, many to one, one to one or many to many.

                  That's why I asked this question at the end of my last post:

                  What  does one record in products represent? If you ship "Left Handed  Widgets", do you have one and only one record for "Left Handed Widgets"  or do you create a new Products record every time you ship them?

                  • 6. Re: FM 11 adv. Conditional Value List with Depth
                    mihaly

                    No, the product will stay the same, but the quanty of the product ( small or big container) or destination will change...and this will effect the costprice.

                    Thanks very much btw...really appreciate it...

                    I think it will be easier if I change the 3 tables to fixed list, and keep the transport table. The records in transportation wil give me the specifiek port/destination/container and price.

                    relationsh:

                    Product Table --->Transport(by product ID) <------>Transport Port (port selfjoin transport Table)<--->selfjoin transport Table, field: destination <--> Transport Container Table, selfjoin with container

                    I cant get past the second level...so the conditional value list is working for destination....but fails with container.

                    • 7. Re: FM 11 adv. Conditional Value List with Depth
                      philmodjunk

                      "No, the product will stay the same, but the quanty of the product (  small or big container) or destination will change...and this will  effect the costprice."

                      But will you just edit the quantity field each time or create a new record with the same product but a new quantity?

                      I'd think you'd want a new record for each such quantity shipped, but am having trouble seeing how your current list of tables can do that. Looks like you need a table where you have one record for each product that you might ship, a table for each shipment and a table that itemizes each type of product that is part of that shipment.

                      • 8. Re: FM 11 adv. Conditional Value List with Depth
                        mihaly

                        I think you are right....however, they way it is suppose to work:

                        product and quotations are send to retail/distributors. When order is made, it is handled in a different program. So FM is gonna be used to calculate margins etc (and transportation is a part of that) & send the quotations to the retail/distr.

                        So a lot of different quotations are made.

                        A distributor wil receive quotations for several products.....(sorry for the mess....i thought it would be relatively easy)...

                        products table + quotations table?

                        "a table for each shipment and a table that itemizes each type of product that is part of that shipment." this wont be necessary.

                        • 9. Re: FM 11 adv. Conditional Value List with Depth
                          philmodjunk

                          I think you are right....however, they way it is suppose to work:

                          Huh? We're discussing how to design the database. "They" should be concerned about the results they can get out of it, not the specific structure of the database that we use to produce the results that they want.

                          I'm just trying to nail down the function of each table you use in the system and still don't know that in sufficient detail for us to get anywhere with the conditional value list issue.

                          "a table for each shipment and a table that itemizes each type of  product that is part of that shipment." this wont be necessary.

                          Sorry, but I think I still disagree at this point. Your "quotations" table would seem to represent the "shipment" table I am describing. In order to construct such a quote, you have to document all the details that make up that quote and this would seem to require a way to break down that shipment into its various parts. The only exception to that I can conceive is if each quote is to ship only a single type of item in one or more identical shipping containers.

                          • 10. Re: FM 11 adv. Conditional Value List with Depth
                            mihaly

                            Dear Phil,

                            thxs again for the help. It was not possible for me to overthink your ideas this weekend. 

                            Note:

                            A container shipped will carry only 1 product. The Port is fixed per product, so I came up with the following:

                                                                 Customer Table
                                                                             ^
                                                                             |
                            Product table(incl Port) <------- Quotations Table ------> Transport Table -----> Destinations Table
                            ...............................................................................................
                            |. .|
                                                                                                                            |   v
                                                                                                                            | Container Table
                                                                                                                            v
                                                                                                       Different Cost Table

                            note: Customer Table is linked 2 Quotations Table, Container Table is linked 2 Transport Table, Different Cost Table is linked 2 Quotations Table

                            Is this correct for:

                            A customer receives a pricelist for several/all product with specific margin calculations (from Different Cost Table) & Transport Price calculations, depending on Destination and Container Size.

                            Much appreciated!

                            grtz

                            mihaly

                            • 11. Re: FM 11 adv. Conditional Value List with Depth
                              philmodjunk

                              That makes sense to me, but keep in mind you know your business practices and I don't. As I read the above chart, (I edited it for you to make it easier to read), a record in Transport Table represents shipping one product to a specific destination in one or more containers of an identical type. Your Quotations can list more than one Product, each from (potentially) a different port, but shipped to teh same destination but all in the same type of container.

                              • 12. Re: FM 11 adv. Conditional Value List with Depth
                                mihaly

                                Dear Phil,

                                again thxs for your answer. I believe this is right for 1 thing....

                                If I change the connection of the Container table, and connect it 2 the quotations table instead of the transport table....then it would be possible 2 change the size of the container for different products (and that would make the transportation table absolete).

                                Correct?

                                thxs a million

                                • 13. Re: FM 11 adv. Conditional Value List with Depth
                                  philmodjunk

                                  It would certainly allow you to specify different containers as part of the same quote.

                                  Whether you still need a "transport" table depends on what you intend to do with that "Different Cost" table.

                                  Sounds like we renamed the "transport" table to be the "Quotations" table. Wink