8 Replies Latest reply on Feb 28, 2017 1:28 PM by zukeman

    Report in preview mode doesn't show correct info in leading grand summary

    zukeman

      Ok, I'm a little bit of a movie on creating reports in filemaker.  Here is the scenario. I have a database to track owner changes on a construction project. All tables are relating back to the owner information for their unit. From there I have a table for change orders, which can be made up of multiple PCO(potential change orders), those potential change orders are linked to another table that has line item costs.  Here is my problem: if you look at the CO layout it displays all the information correctly and I can add or remove PCO to the change order and it all works just fine.  Tthe problem is I created the pdf version of this that has the line item detail as the body, the PCO as the sub-summary and the change order/unit info in the leading grand summary.  When I click the PDF test button it filters and formats the body information correctly but the other parts of the form only show the first record (CO 002) information.  I am trying to get this up and running for field staff but this layout is my major hickup.  I know I have dealt with similar problems before but not working on filemaker on a regular basis is biting me in the A$$.

        • 1. Re: Report in preview mode doesn't show correct info in leading grand summary
          SteveMartino

          Well it's doing what you are telling it to do.

          Correct me if I'm wrong, just so we're on the same page:

          -Unit 2-101 has 2 change orders, CO 002 & CO 003. 

          -You want to see all of Unit 2-101 change orders on a report.

          If that is true, you're only asking for change orders from CO 002 in your script, by performing a find for the Unit # and the current record (CO 002)

          So if you could confirm that, I'll dig a little deeper.

          As a side note, some of your relationships aren't set up properly, in some cases, not auto enter, non modifiable, and in one case a text to number relationship.

          • 2. Re: Report in preview mode doesn't show correct info in leading grand summary
            zukeman

            steve, 

             

            Thanks for responding.  Actually what I am trying to achieve is to create a report that shows just one of the units change order at a time. Let me see if I can better describe the scenario.  For example;

             

            unit x-xxx might have the following:

             

               -  change order 001 which haS

                          PCO x-xxx-001 which includes 

                                line item 1

                                Line item 2

                                line item 3. Etc etc

               -  change order 002 which has

                           PCO x-xxx-002 which includes

                                line item 1

                                line item 2

                 - it also may have PCOs that are not rolled into a change order at any given point

             

            the report should just show the information from one chnage order .  I think if you navigate from each change order to the report using the 'test pdf' button you will see that the body detail is correct but the info above and below just reflect the first change order for that unit. If you navigate from change order 002 it looks correct

             

            i'lol look at the relationships but any help there would be appreciated.   Not sure where I should be using an auto enter.

            • 3. Re: Report in preview mode doesn't show correct info in leading grand summary
              zukeman

              Just wanted to add that in fact one change order might have multiple PCOs in it like

               

                 -  change order 001 which haS

                            PCO x-xxx-001 which includes 

                                  line item 1

                                  Line item 2

                                  line item 3. Etc etc

                             PCO x-xxx-003

                                  line item 1

                                  line item 2

                                  line item 3

              • 4. Re: Report in preview mode doesn't show correct info in leading grand summary
                SteveMartino

                What you need to change, and this will help you all around now and into the future is...

                Every table should have a primary key-auto entered, serial number, on creation.  Modification should be prohibited, etc.

                Every child table and join table should have foreign keys that match the primary key of its parent table for each record.

                The relationships should be created with these keys.

                Right now you have relationships by Unit number, some as text, sum as number.  This could cause a huge problem down the road with relationships, and you should fix it first.

                I keep playing with it

                • 5. Re: Report in preview mode doesn't show correct info in leading grand summary
                  SteveMartino

                  More questions about work flow.  How does a PCO become a CO?  I think you should have these records in one table with some kind of indicator (checkbox?) that tells you-which can be filtered/sorted out.

                  Think of records for an estimate that then becomes an invoice (sale).  I don't think you would want that record twice in your DB, once in each table.

                  Hopefully others will chime in.

                  • 6. Re: Report in preview mode doesn't show correct info in leading grand summary
                    zukeman

                    Ok so our staff create PCO as they discuss change items with clients.  Many will die off before they ever get rolled into a change order. So not every PCO will result in a change order. At some point, our project manager will create a change order to the client, which is the mechanism to increase their contract sum.  The change order layout works as it should so the PM can select which PCO are to be included in the change order.  By selecting the PCO from the PCO3 portal it enters the CO value in the PCO table.

                     

                    I don't think I have the same record in two places, if I am understanding your comment.  The change order is an independent item since it's description can include multiple PCO and summary of items for the owner,  The information that resides in the PCO table only has one field that ties it to the change order. 

                     

                    Is the primary Key to foreign key a best practice left over from the days of Microsoft Access where you needed to identify a key for each table.  I have built several filemaker databases that seem to function without primary keys and the relationship seems to work fine. 

                    • 7. Re: Report in preview mode doesn't show correct info in leading grand summary
                      philmodjunk

                      Is the primary Key to foreign key a best practice left over from the days of Microsoft Access where you needed to identify a key for each table.  I have built several filemaker databases that seem to function without primary keys and the relationship seems to work fine.

                      A primary key, as a field specifically designated as a primary key as a built in part of the field's definition--as you can do in Access, does not exist in FileMaker. But the concept of primary key to foreign key is an essential part of good relational database design in all relational databases no matter the platform used for creating the database.

                       

                      So setting up a field that is always unique and never changed once assigned is an important part of good relational database design in FileMaker as in all other relational DB systems.

                      • 8. Re: Report in preview mode doesn't show correct info in leading grand summary
                        zukeman

                        I think I found the culprit:

                         

                        The relationship between the PCO table and the change order table should have had two fields

                         

                        Change order::CO = PCO::CO

                        And Change Order::Unit = PCO::Unit

                         

                        I found the fault by adding info from the Owner unit info to the leading grand summary and it showed up fine. So I figured the unit information was resolving itself(if thats the right term) back to the owner unit info table.  but this wasn't happening to the Change order table.  Figured that if I was only relating the ::Unit and not the ::CO I would give it a try.  Think it works so far as the first test.  Will input more data to try over different units.  Will look at cleaning up the relationships and key wherever I can.