1 Reply Latest reply on Jul 28, 2014 10:02 AM by philmodjunk

    Layout to calculate one value on tables

    jackmac

      Title

      Layout to calculate one value on tables

      Post

           Hi,

           I'm trying to create a layout that will add the values in a field on three diff tables. I have three tables:

           Boxes (Box_ID, Box_Cost, Quantity, Total)

           People (Staff_ID, Hr_Rate, Quantity, Total)

           Widgets (Widget_ID, Widget_Cost, Quantity, Total)

           I want to be able to create a quote based on the info held on these layouts. The three tables have say 4 records in each. I want to be able to select which boxes are needed and total them. The the same for staff and finally for widgets. The quote table will have total Box cost, total People cost and Total Widget cost.

           I've tried using a solution I already use which involves a 'Line Items' layout which effectively adds products then totals below but as I'm drawing from three different tables, I can't get that to work.. any ideas?

           Am using FM12 Pro Adv

        • 1. Re: Layout to calculate one value on tables
          philmodjunk
               

                    I want to be able to create a quote based on the info held on these layouts tables.

               

                    I want to be able to select which boxes are needed and total them.

               

                    I've tried using a solution I already use which involves a 'Line Items' layout which effectively adds products then totals below

               A related lineitems table is exactly what you need. You may ultimately find it a better data model to put people, boxes and widgets in the same table. You can link in different "detail tables" to record data specific to people, boxes or widgits, but keep ID and cost fields in a common table.

               Using your current structure, you'd need these relationships.

               Quotes----<LineItems------<boxes
                                              ------<people
                                              ------<widgets

               A portal to LineItems would be filled out where a field with a value list is used to specify whether that lineitem is a Box, people or widget record. Fields that look up data (such as cost data), would use an auto-enter calculation with this case function:

               If ( ItemType = "Box" ; Boxes::Box_Cost ;
                   ItemType = "People" ; People::Hr_Rate ;
                   ItemType = "Widget" ; WIdgets::Widget_cost )

               But note that you have to make an additional data entry selection and adding a 4 item type requires making quite a few design changes to your database. If you have single table of with all Box, People and Widget costing data in it, adding a 4th type of item to the system is strictly a data entry task.