4 Replies Latest reply on Aug 9, 2011 4:58 PM by LarryMastromatteo

    Count Contracts

    LarryMastromatteo

      Title

      Count Contracts

      Post

      We are upgrading from FM 5.5 and are having a little difficulty with a summary field. We are trying to count our total projects on hand. So, we have contract amount for each project and we subtract the amount that is invoiced to date, in a Sub-Summary Part. The data base is sorted by job name and that part works good. Now we have set a Trailing Grand Summary Part and put a summary field in it called count jobs which is a count of the jobs field. After doing all that our result is the count of the invoices on the last job instead of the count of jobs. What are we doing wrong? Thanks in advance for any help!

       

        • 1. Re: Count Contracts
          philmodjunk

          You mention "projects" and "jobs" are these the same thing?

          What table is your layout based on?

          And is your summary field defined in the same table?

          Any summary field defined in the layout's table and placed in the Trailing Grand Summary will compute a value based on the current found set so the records that are currently in the found set will determine the value. If it's a summary field defined in a related table, you won't get the results you might expect as this then is a summary of those records in the related table linked to a specific record in layout's found set. If this is put in the trailing grand summary, then you get a summary of related records linked to the last record in your found set--which is what it sounds like you have done here.

          • 2. Re: Count Contracts
            LarryMastromatteo

            Hello PhilModjunk:

             

            Thanks for answering my question! Yes, projects and jobs are the same thing and YES, you are right, that is exactly what is happening. How can I fix it? I have two files that are related. One file is a list of Contracts,"Jobs" The other is the invoice file. In the jobs file I count the amount of Jobs that are to be finished. That number I want to bring over to the Invoice file, So when I print off a summary of invoices I would like at the end of the summary to know haw many jobs I have left to complete without having to go back to the job file. It seems simple enough and I had it working in FM 5.5 but yet I can't seem to get it working in FM 11. Thanks for your help!

            • 3. Re: Count Contracts
              philmodjunk

              What is the relationship between Contracts and Invoices in your Invoices file?

              I would assume something like:

              Invoices::ProjectID = contracts::ProjectID

              You haven't indicated how you mark a Contracts record as "complete". For the sake of this example, I'll assume that the text "complete" is entered into a field named Status in the contracts table.

              Define a new field in Invoices, constComplete, enter "Complete" as it's sole calclulation term and select "text" as its return type.

              Create a new table occurrence of contracts in the invoices table. (Open Manage | Database | Relationships in Invoices, click Contracts, then click the button with two green plus signs.)

              Link it to Invoices like this:

              Invoices::constComplete = Contracts 2::Status

              Now you can define a calculation field in Invoices as Count ( Contracts 2::Status ) to return the count of incomplete projects or you can define a "count of" summary field in Contracts that counts a never empty field such as this status field and then you can place this summary field, from Contracts 2, on your layout.

              • 4. Re: Count Contracts
                LarryMastromatteo

                Hello PhilModjunk:

                 

                Yes, Yes, Yes! Thank you! Got it working! Thanks for your help!

                 

                From Saskatoon!

                 

                Larry Mastromatteo