4 Replies Latest reply on Jul 13, 2015 11:05 PM by philmodjunk

    Grouping costs...

    DavidWood

      Title

      Grouping costs...

      Post

      I built a database a few years ago to document ergonomic evaluations.  Each record has employee information, ergonomic information, but my problem is with the ergonomic items that I purchase for the person.  I originally created the database with repeating fields for Qty, Item#, Item Description, Price and Total cost.  It's worked out just fine the last few years but now I need to produce cost information.  The problem, as you might have already guessed, it that when I conduct a query in the item description field for say, "chair", it finds the records that contain "chair" just fine but the "Total" cost (which is what I'm after) includes the cost of all the other items purchased for that person on that record, other than just "Chair". For example, line item# 1 might be a chair, line item# 2 is a footrest, line item# 3 is a keyboard, etc.  It gives me the total for the entire record rather than just a total of the cost of the chairs.

      I'm hoping that someone can give me some general pointers regarding how to better build this database so that I can perform queries to group costs by item purchased (total cost of chairs or total cost of keyboards, etc).  Below is an example of a common record to help clarify the mess that I've created. 

      Thank you in advance. Dave

      sample.jpg

        • 2. Re: Grouping costs...
          philmodjunk

          What you show as repeating fields at the bottom of your layout should instead be a portal to a related table. That makes each row of data a separate record and then you can set up a report on a layout based on the portal's table where you can group the records by Item number or Items Description and use summary fields inside sub summary layout parts to compute and display subtotals for each group. Other report formats are also possible once you have made this design change.

          And FileMaker makes it easy to make this design modification. Once you have set up the needed table, relationship and match field values in the table on which the layout shown is based, you can use import records to copy the data from this set of repeating fields into the new related table and split the repetitions into individual records in the portal table at the same time.

          • 3. Re: Grouping costs...
            DavidWood

            Thank you!  I'm going to export the data and start all over again.  Time to learn the fundamentals.  Thank you so much for your guidance.

            • 4. Re: Grouping costs...
              philmodjunk

              You do not need to export, just import.