7 Replies Latest reply on Dec 1, 2009 3:14 PM by philmodjunk

    help needed: creating estimates with sections and line items

    maloney

      Title

      help needed: creating estimates with sections and line items

      Post

      Hi

       

      I have a database with projects, estimates and invoices. everything works fine.

      Right now the created estimates & invoices are based on projects.

      In projects it's possible to create subitems like films (portal). So every project can have one to many films.

      I would like to create an estimate which includes the films. Each film has its section on the estimate and each section contains alls the needed line items with the products.

       

      My estimate should look like:

       

      Project XYZ 

       

      Film 01

       

      product A

      product B

      product C

      ....

       

      Film 02

       

      product A

      product C

      ....

       

       

      Thanks. 

       

       

        • 1. Re: help needed: creating estimates with sections and line items
          littledog
            

          Look on http://www.hierarchy.lv/?page_id=158 until I find the application I have made based on hierarchy tree.

          OR take as the base one of the mentioned sources and add summary fields in Parent table summarizing values from Child table.

           

          • 2. Re: help needed: creating estimates with sections and line items
            philmodjunk
              

            Do you have a table for your "Products"? If so which one?

             

            If you have a table of products records related to your Film and Project tables, you can create a summary report based on your products table with fields from the Projects and Film tables placed in subsummary or other parts so that they form headings and subheadings as shown in your report.

            • 3. Re: help needed: creating estimates with sections and line items
              maloney
                

              Yes, there are tables for:

               

              Projects

              Products

              Estimates

              Invoices

              Films

               

              Products, estimates, invoices and films are related to the project table.

              I have also to relate films and products, right? But I don't know how to create summary and subsummary reports.

              I am using FilemakerPro Advanced.

               

              Push in the right direction would be great. 

               

              Thank you. 

              • 4. Re: help needed: creating estimates with sections and line items
                maloney
                  

                Another question:

                 

                I know how to relate the products to the invoice and the estimates to the project. 

                 

                But how do I assign the products to the films and the films to the estimates in one step?

                 

                • 5. Re: help needed: creating estimates with sections and line items
                  philmodjunk
                    

                  First let's see what relationships you already have:

                   

                  Projects:: ProjectID = Products:: ProjectID

                  Projects:: ProjectID = Estimates:: ProjectID

                  Projects:: ProjectID = Invoices:: ProjectID

                  Projects:: ProjectID = Films:: ProjectID

                   

                  That's my interpretation of "Products, estimates, invoices and films are related to the project table."

                   

                  If that's correct, then the question to be answered here is: "Should products be related directly to Projects or should they really be linked to Films instead."

                   

                  It looks to me like your relationships should be changed to:

                   

                  Projects:: ProjectID = Estimates:: ProjectID

                  Projects:: ProjectID = Invoices:: ProjectID

                  Projects:: ProjectID = Films:: ProjectID

                  Films:: FilmID = Products:: FilmID

                   

                  I don't know that for sure but that's what your posts suggest. If so, then you would base a layout on the Products table and put fields from Films and Projects in two SubSummary parts (If your report is to include multiple projects) or a SubSummary and a Header or Leading Grand summary part (If your report is only to list data for one project at a time.).

                   

                  • 6. Re: help needed: creating estimates with sections and line items
                    maloney
                      

                    Hi PhilModJunk

                     

                    Thank you for your reply.

                     

                    I think relating the products to films is the right direction.

                     

                    I create my estimates in my estimates form in the line item portal.

                    What I need is somehow to include the name of the film, like a title, above the products belonging to that film in the portal and also relate the products to that film. Sometimes there might be two or three films in the same estimate. 

                    The films need to be in relation with the estimates and the project. 

                    The estimate needs also to be in relation to the project.

                     

                    Would it be better to create for each film an estimate and then join the estimates of the films from the same project together to get the final estimate?

                     

                    Thank you for your time and help. 

                     

                     

                    • 7. Re: help needed: creating estimates with sections and line items
                      philmodjunk
                        

                      Filemaker can "drill down" through multiple TO's in your relationship graph provided certain issues don't intervene.

                       

                      Since Products to Films is a one to many relationship and Films to Products is a one to many relationship.

                       

                      Filemaker can "drill through" films to get to a related group of products records and from a given products records, it can drll through films to reference a the parent record in Projects.

                       

                      For the sake of your sumary report, things will now work. For data entry purposes, you may need to create a special TO and portal to allow you to add Products to a film from a Projects layout. Here's how:

                       

                      Add a global field to Projects: gFilmID (Use Field Options to select global storage).

                      Create a new Table Occurence for Products (Click the Box in the Relationships Graph and then click the button with two plus signs.) Name it "SelectedProducts".

                      Link it into your graph:

                      Projects::gFilmID = Products::FilmID

                       

                      Enable creation of related records for the SelectedProducts side of the relationship.

                       

                      Now write a script:

                      Set Field [Products::gFilmID; Film::FilmID]

                      Commit Record

                       

                      I'm assuming you already have a portal on a Project layout that lists related Film records. Add a 2nd portal to SelectedProducts. Put a button in the row of the Film based portal and set it to run the above script.

                       

                      Now when you click the button, you'll see the current list of products for the film whose row you clicked and can add/delete product records assigned to that film record.

                       

                      To finish things up, use a conditional format to change the fill color of your Film Portal fields if the row's FilmID = Projects::gFilmID. This "highlight" trick tells the user which film has been used to select Product records in the SelectedProducts portal.