13 Replies Latest reply on Jan 24, 2017 8:16 AM by peakest

    Data Modeling - Multiple Attributes

    peakest

      I have an item that has multiple attributes that is related to one data point.  I have a "BUILD" (assembly).  For instance a wall being built for a new home.  Say I have 100 Lineal Feet of Exterior Wall.  This wall has multiple attributes, ie. 2x4 Plate Stock at a rate of 4 lineal feet of every 1 foot of wall.  Then there is plywood sheathing, where I have 9 square feet of plywood per every 1 foot of wall. etc... how can I get the results for each "BUILD" attribute based from the single data point of 100 lf.

       

      Here is where I am at:  Please look at the portals data line 11 (1st Wall - XW26104 - 100 LF)

      data model question 2016-4-14.png

      Now I have related DETAILS or ATTRIBUTES of the build XW26104

      data model question image 2 2016-4-14.png

      What I need is the "EXTENDED" material list for the "BUILD"

      100 EA 26104STUD

      400 LF 2601 PLATE

      900 SF WALL SHEATHING

      ETC...

        • 1. Re: Data Modeling - Multiple Attributes
          bigtom

          You can do this with calculated fields.

           

          Plate = table name::stud *4

          Sheating = table name::stud * 9

           

          You can also do this with a script using the same logic.

          • 2. Re: Data Modeling - Multiple Attributes
            peakest

            Sorry, really new at this.

             

            Is this done in a new Table or Table Occurence?  As I create a new layout I either get the BUILD with only the first of the DETAIL ATTRIBUTES.

            data model question image 3 2016-4-14.png

            • 3. Re: Data Modeling - Multiple Attributes
              bigtom

              I see. Do you want these things automatically generated? Will you always need sheeting with the studs or do you only want the proper calculation when you decide to add sheeting to the list. More simply will all walls have sheeting

               

              When you are creating a new attribute based on a current attribute things can get complicated. You could do it in the same table or another table.

              • 4. Re: Data Modeling - Multiple Attributes
                peakest

                A "BUILD" is a set of given attributes that is repeated.  ie. BUILD_ID "XW2408"  This wall and its attributes can be used on the 1ST FLOOR of a house, the 2ND FLOOR of a house and on multiple houses.  The XW2408 will most often have the same attributes:  PLATE x RATE, STUDS x RATE, SHEATHING x RATE etc.  One house may have 70 lf of XW2408 while the next might have 120 lf of XW2408.  So the material list is adjusted with just the one data point.  I have this working with a flat excel sheet but want to migrate to a database.

                • 5. Re: Data Modeling - Multiple Attributes
                  peakest

                  I have it working in MS Access  Here are the tables and queries.  I can also show in Excel.

                  data model question image 4 2016-4-14.png

                  data model question image 5 2016-4-14.png

                  data model question image 6 2016-4-14.png

                  data model question image 7 2016-4-14.png

                  • 6. Re: Data Modeling - Multiple Attributes
                    mikee

                    From your last post a query has been used.  Thus in Filemaker you need a Build_Estimate table to do the calculations for each build.  The records for this table would need to be created by a SCRIPT that takes data from Build_Details and relates it Takeoff_Build records. This can be done by taking each Build-Data record as it is created and going to the related Build_Details records and looping through them to create the appropriate record for the Build_Estimate table.

                    TakeOff.tiff

                    Click on image to see full diagr

                    • 7. Re: Data Modeling - Multiple Attributes
                      peakest

                      Thank you!  I will run through this schema and try the scripting and see what I can accomplish this evening.  I will let you know what I come up with.

                      • 8. Re: Data Modeling - Multiple Attributes
                        mikee

                        Had a few minutes and set up a script to build the records for the Build_Estimate table data. try this link for a sample file with the script.

                        The Script is launched from the Takeoff layout via a button on the portal line.

                         

                        https://www.dropbox.com/s/rwh5cvpc6d5xhlk/Build%20Takeoff.fmp12?dl=0

                        • 9. Re: Data Modeling - Multiple Attributes
                          erolst

                          Mike Easter wrote:

                           

                          Had a few minutes and set up a script to build the records for the Build_Estimate table data. try this link for a sample file with the script.

                           

                          Nice, but note that you can avoid the frequent layout switches like so.

                           

                          Loop

                            Set Variable [ $Build_Part_Id; Value:List ( $Build_Part_Id ; Build_Details::_fk_Build_Part_Id ) ]

                            Set Variable [ $Construction_Use_Id; Value:List ( $Construction_Use_Id ; Build_Details::_fk_Construction_Use_Id ) ]

                            Set Variable [ $Build_Part_UOM; Value:List ( $Build_Part_UOM ; Build_Details::Build_Part_UOM ) ]

                            Set Variable [ $Rate; Value:List ( $Rate ; Build_Details::Rate ) ]

                            Go to Record/Request/Page [ Next; Exit after last ]

                          End Loop

                          Set Variable [ $theCount; Value:Get ( FoundCount ) ]

                          #

                          Go to Layout [ “Build_Estimate” (Build_Estimate) ]

                          Loop

                            Exit Loop If [ Let ( $counter = $counter + 1 ; $counter > $theCount ) ]

                            New Record/Request

                            Set Field [ Build_Estimate::_fk_Build_Id; $Build_Id ]

                            Set Field [ Build_Estimate::_fk_Takeoff_Id; $Takeoff_Id ]

                            Set Field [ Build_Estimate::_fk_Takeoff_Data_Id; $Takeoff_Data_Id ]

                            Set Field [ Build_Estimate::_fk_Build_Part_Id; GetValue ( $Build_Part_Id ; $counter ) ]

                            Set Field [ Build_Estimate::_fk_Construction_Use_Id; GetValue ( $Construction_Use_Id ; $counter ) ]

                            Set Field [ Build_Estimate::Build_Part_UOM; GetValue ( $Build_Part_UOM ; $counter ) ]

                            Set Field [ Build_Estimate::Rate; GetValue ( $Rate ; $counter ) ]

                          End Loop

                          Go to Layout [ original layout ]

                           

                          Obviously not fit for values that contain carriage returns themselves …

                           

                          Even faster would be to simply import the related set into Build_Estimate and use Replace Field Contents to batch-write the static IDs from Takeoff_Data.

                          • 10. Re: Data Modeling - Multiple Attributes
                            mikee

                            Nice next progression   

                             

                            • 11. Re: Data Modeling - Multiple Attributes
                              peakest

                              Actually there are different ways data could be entered.  The first table is what I call raw takeoff data which comes from a graphic interface.  (A minimal data form) So it could certainly be used as a one time import.  This is stand alone data that can be used to extrapolate more information from, ie the BUILD DETAILS. 

                              (I would also like to enter this data via forms so I guess we could store the data in a repository and get the detail report at one time.)
                              FMC EXCEL DATA EXPORT TABLE.png

                              Here is the end result in another excel sheet I use for LOOKUPS  (Different data just showing for an example.)  So every new takeoff gets a brand new spreadsheet...

                              FMC EXCEL DATA LOOKUP TABLE.png

                              I am now adding the DISPATCH which is obviously a major advantage of the DATABASE so you can use the nomalized build details.  I will try out the scripts you have given and very much appreciate your help!

                              • 12. Re: Data Modeling - Multiple Attributes
                                peakest

                                Hello,  I know it has been a while but I wanted to ask again about the import / replace field option you mentioned above. The TAKEOFF DATA is a set that can be imported or treated as a data set:

                                TakeoffData.png

                                BUILD DETAILS store one or many BUILD PARTS that have RATES that need to the BUILD'S TAKEOFF QUANTITY:

                                BuildDetail.png

                                Below is a find I used to show how the the TAKEOFF DATA - BUILD QUANTITY is related to THE BUILD DETAIL'S BUILD PART RATE to calculate the part's EXTENDED BUILD DETAIL QTY shown in FIELD 12

                                ExtendedBuildDetailsVIAFind.png

                                I struggle with the find results and am simply using this for illustration. 

                                 

                                I feel your methodology would certainly be the best option and would love to understand the steps to accomplish this.

                                 

                                "Even faster would be to simply import the related set into Build_Estimate and use Replace Field Contents to batch-write the static IDs from Takeoff_Data."

                                 

                                • 13. Re: Data Modeling - Multiple Attributes
                                  peakest

                                  Hi erolst,

                                  I am trying to understand your final statement on your previous post.

                                   

                                  "Even faster would be to simply import the related set into Build_Estimate and use Replace Field Contents to batch-write the static IDs from Takeoff_Data."

                                   

                                  Can you help with the "related set"  Attached is another version of the file where I am trying to get the BUILD DETAIL(s) for each of the TAKEOFF DATA rows. 

                                   

                                  Here is another thread where I am being pointed toward a Go To Related Record step/script:

                                  Script for Related Set help please