1 Reply Latest reply on Jul 2, 2015 6:58 AM by philmodjunk

    New Table Help

    RichardStrohman

      Title

      New Table Help

      Post

      Here is my situation:

      I need to make one field take the average of some of the numbers in the chart on the attached picture.  The way the table works is this, the dia. is diameter of a cylinder.  The straight wall is how tall the cylinder is.  What we use this for is to get a tapering thickness of a certain size cylinder.

      You would read the chart like this: If you need a 6' dia. x 20' tall cylinder, find the 6' dia and the 20' straight wall and drag your fingers until they meet.  That number is the bottom thickness.  In this case it is 2.5.  Now the cylinder tapers as you get taller and ends at the 2' straight wall for any given size.  In the case of the 6' x 20' the tapering sequence would be this:

      2.5, 2.5, 2.5, 2.5, 2.5, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1.5, 1.5

      What I am trying to do is make a table with this chart data (the chart is in excel now) and have a field gather all of the numbers in a tapering sequence and get the average of those numbers.  It would also need to allow a user to enter any cylinder size up to 14' x 40' and find the average thickness of that size. I haven't the slightest clue how to make the table or get all of the numbers in a sequence.  Any help would be greatly appreciated.

      Screen_Shot_2015-07-02_at_8.35.14_AM.png

        • 1. Re: New Table Help
          philmodjunk

          While you would present the data visually on your layout in this format to make the most sense of it. In FileMaker, it would make more sense to store this data such that you get one record for each cell shown with at least these three fields:

          ThicknessTBL:
          Diameter | Height | Thickness

          This then allows you to define a relationship that allows you to access the set of records in the table that represent the data series for which you want to compute an average:

          AveragesTbl::Height > ThicknessTBL::Height AND
          AveragesTbl::Diameter = ThicknessTBL::Diameter

          Then a calculation field in AveragesTbl can be defined as:

          Average ( ThicknessTBL::Thickness )

          SInce you won't be able to simple drag and drop or use import records to convert your excel spreadsheet into such a table, you'd need to import this table into an intermediary table and then use a script that loops through the resulting data in order to generate the table that I am describing here, one cell/record at a time. (This one record to a cell table can still be used to present its data in the 2 dimensional format shown in your screen shot, though the layout design takes a bit of work to do so.)