5 Replies Latest reply on Jul 9, 2015 7:24 AM by philmodjunk

    Trouble with FileMaker summary report from multiple tables

    arvindosu

      Title

      Trouble with FileMaker summary report from multiple tables

      Post

      Hello,

      I have two tables - one is headcount and the other is job offers. Here are the fields I am joining-

      headcount table - employee ID, report date

      offers table - employee ID, report date...all offers have an employee ID.

      Made a relationship based on employee ID and report date.

      My offers table has various offer status such as offers pending, accepted, offered etc with the same employee ID. So I made a self-join relationship and identified the dups. I don't want to delete the data.

      I'm doing a report based on the summary fields that show no. of heads + offers. But the problem is in my offer total, the dups are showing up due to the way the relationship is structured. I wrote a script to not include the dups in the found set for the report, yet I get the total offers with the dups. Any solution you have for this?

       

        • 1. Re: Trouble with FileMaker summary report from multiple tables
          philmodjunk

          Can you provide an example of what you want here?

          I don't see how your "dups" are really dups nor why you need a self join for that.

          This seems like a report that can be set up on a Offers layout with sorting and sub summary layout parts to group the records.

          • 2. Re: Trouble with FileMaker summary report from multiple tables
            arvindosu

            Thanks for responding! To answer your question, an offer can have multiple status in the same report such as offer pending, accepted, rescinded, etc. I want to count them as only one offer which is why I called them dups.

            I had tried to make my query simpler. Along with the above, I have another table for the open job reqs.

            So here is the table structure again -

            headcount table - employee ID, report date

            offers table - employee ID, req id, report date...all offers have an employee ID.

            reqs. table - req id, report date.

            Some of the job reqs can also have an offer pending. So I consider these dups. Similar to above mentioned for the offers table, in my found set I am excluding reqs and offers which are dups.

            headcount and offers are joined by report date and employee ID while the offers and req table are joined by req. id. I am trying to build a summary table of the headcount table and pulling in reqs and offers.

             

            • 3. Re: Trouble with FileMaker summary report from multiple tables
              philmodjunk

              But I can't tell from your description and field names, which values constitute "dups". Several records in offers with the same EmployeeID are dups? or is it some other field or combination of fields that produce the duplicates?

              And to repeat, can you provide an example of the report? What do you want to see on the screen or printed page exactly?

              • 4. Re: Trouble with FileMaker summary report from multiple tables
                arvindosu

                Sorry for not being clear. Yes, several records with the same employee ID are dups.

                • 5. Re: Trouble with FileMaker summary report from multiple tables
                  philmodjunk

                  It's still not clear.

                  I don't see how the data in your table can possibly break down into the categories of your report. What field in Offers identifies the category to which it belongs in the above report.

                  And while I can see ways for your Offers table to have more than one record with the same employee ID, I don't see how that would adversely affect your report as I'd expect each such record to be counted in a different category.