7 Replies Latest reply on Aug 25, 2012 10:12 AM by philmodjunk

    Calculations with related tables



      Calculations with related tables


      I've an administration system for the reception of different shipments that permits the registry of different providers and their respective materials that they send to us and also the registry of every shipment, we charge the providers for the service a different quantity per ton even if they send us the same material, my problem its that in the shipment registry view i can choose the provider, i register the weights of the shipment and the material they sent but i want that when i select the provider automatically i can view the materials we have registered to him and after choosing the material the system takes the cost per ton and the tons registered and calculates the cost of the reception. How can i do that?

      Thanks for the help and sorry for my bad english!

        • 1. Re: Calculations with related tables

          Your English is excellent but many details are lacking from your post. We do not know all the procedures your business uses to calculate these costs. We do not know what tables and relationships already exist in your database. If you haven't yet created any, then you need to describe what you would do if you simply wrote it all out on paper records.

          • 2. Re: Calculations with related tables

             Well the procedure its very simple we register a provider in a specific providers table (fields:name, business name, client number) after we register the provider we procede to register all the materials he ships to us in a separate materials table with the fields: name of the material, type of material, cost per ton (we establish that cost) and provider (related field with the provider table) and last we have another table for the registry of receptions in that table we have the fields: date, number of reception, brute tare and net weight of the shipment (net weight its the one that really matters), provider (related field with the provider table), cost per reception and a material and cost per ton field, what i want its that when i choose the provider the materials we have registered to him and their respective costs appear in the material and cost per ton field then when thats done the cost per reception will take the cost per ton and multiply it for the net weight registered.


            Hope this is more clear for you to help me and as always lof of thanks.

            • 3. Re: Calculations with related tables

              So you have this relationship:


              Providers::Provider = Materials::Provider

              and last we have another table for the registry of receptions

              I would read that as a table for logging (registering) each shipment as it is received. Correct?

              Seems like you need two tables for this, one where you have one record for each shipment received and one that lists the materials in that shipment. Or does each shipment consist of only one of the listed materials?

              You might then have these additional relationships:

              Providers----<Reception----<ReceivedMaterials>------ProviderMaterials   (Provider materials is a second occurence of materials.)

              Reception would record the Provider, the date and time received and so forth. A portal to ReceivedMaterials would then list each material received in that shipment.

              I think you are asking for two things:

              1) a conditional value list that lists only the materials for the selected provider.

              2) a "lookup" that copies the price per ton (Unit price) from Materials into a field in this table so that calculations can use the quantity recieved and this unit price to compute a cost for this shipment of material.

              All of this can be done. Let me know if I have it correct or not and I'll provide the needed details.

              Note if my relationship notations are new to you and you can't understand them, see this thread: Common Forum Relationship and Field Notations Explained

              • 4. Re: Calculations with related tables

                 Yes! i think you're right that's the two things i need.

                Here's a picture of the relations i created.


                • 5. Re: Calculations with related tables

                  I can't read Spanish, but those relationships do not look correct.

                  You haven't answered a key question:

                  Does a shipment consist of a single material or a combination of multiple materials from the same provider?

                  • 6. Re: Calculations with related tables


                    Residuo, nombre del residuo = Material

                    Costo por tonelada, costo aplicable = Cost per ton of material

                    The relations are that Proveedor from the Residuos and the Pesos table are the same thing but they cant register any new provider, for that we have the Proveedor table that table is related to the Proveedor field by the field Nombre corto (short name of the provider).

                    The table Residuos has the field Nombre del Residuo (name of the material) as i said in the past post thats the table where we register the materials we receive Nombre del residuo has a relation with the Pesos table (the table where we register the shipments) with the field residuo (material) the same thing happens with the cost per ton field.

                    And the answer is yes we can receive different materials in one shipment but we register them separately because we weight them separately.

                    • 7. Re: Calculations with related tables

                      I still recomend the table structure and relationships that I posted earlier:


                      One record in Reception for each shipment received from a given provider. The date, provider and any other data (such as a total cost) that applies to the total shipment are recorded or calculate in the Reception table. (I'd call it Shipments.) Then a portal to ReceivedMaterials lists each material recieved and it's weight in individual portal records. One key advantage to this setup is that you only need select the provider once for each shipment instead of one for each material received.

                      The relationships used would look like this:

                      Providers::ProviderID = Reception::ProviderID
                      Reception::ReceptionID = ReceivedMaterials::ReceptionID
                      ReceivedMaterials::MaterialID = ProviderMaterials::MaterialID

                      Boldface fields in the above list of match fields would be defined as auto-entered serial numbers and serve as the primary key for the table in which they are defined. The fields of same name listed on the right are number fields and serve as foreign key fields.

                      Please note that these relationships are in addition to:


                      Providers::ProviderID = Materials::ProviderID

                      and Materials and ProviderMaterials are two different occurrences of the same table. To produce:


                      For your first request, you can set up a conditional value list by selecting the "use values from a field" option. List Materials::MaterialID as the first field and Materials::MaterialName as the second field. Then select the "include only related values starting from Providers.

                      For your second request, you can define a CostPerTon number field in ReceivedMaterials and give it a "looked up value" auto enter field option that copies this value from a corresponding field in ProviderMaterials. You can set up ReceivedMaterials::MaterialID as a drop down list with the conditional value list from your first request and when you select a material, the matching unit cost will be looked up.