2 Replies Latest reply on Jul 11, 2012 10:40 AM by philmodjunk

    Creating Reports with records that have multiple data per field

    RobWood

      Title

      Creating Reports with records that have multiple data per field

      Post

      I am used to using Access where creating a simple query would clear this problem right up, but from what I've gotten from the FM Tutorial PDF, all you can create are reports.

      I've created a report (below) that shows job locations categorized by the city. There are many jobs that have more than one proposal, statement, and/or invoice related to it through the relationships I've created, but the report is only showing one in each of those fields per job. How do I make the report show all proposals/statements/invoices connected to each job? 

      Also, are reports the only way to create organized information in FM? Like I said, I'm used to Access where I could create a query, for example, by simply typing out an address of a job and running it and then I could get everything in the database that's related to that address right in front of me in a report that I could print out. Is something like that possible in FM?

      If you need a screenshot of my tables and their relationships, just let me know.

      Screen_shot_2012-07-11_at_11.46.59_AM.png

        • 1. Re: Creating Reports with records that have multiple data per field
          RobWood

          I just decided to go ahead and supply a screenshot of the relationships....

          • 2. Re: Creating Reports with records that have multiple data per field
            philmodjunk

            A screen shot of your tables and relationships would be very helpful. Also tell us what version of FileMaker you are using.

            I'm used to Access where I could create a query, for example, by simply typing out an address of a job and running it and then I could get everything in the database that's related to that address right in front of me in a report that I could print out.

            I've developed a fair amount with Access as well as FileMaker. I think you are describing using the Query tool to produce a table view of data produced by the query you constructed. FileMaker does not have such a tool as it is not designed for users/developers to use SQL.

            But you can often set up a table view of your data and perform a find directly from your layout to get similar results. The key difference being that SQL allows you to specify relationships as part of the query. FileMaker requires that you define such relationships in Manage | Database | Relaitonships so you can't set them up "on the fly". One way to think of it is that the relationshps from manage databse define the "join" portion of your query, entering find mode and specifying criteria produces the "where" clause and then using sort records to sort your results represnts the "orderby" portion of your query.

            And it does take some mental adjustments to transition from an SQL based system to FileMaker as you keep reaching for the SQL expression you used to use only to find you have to use very different tools in FileMaker to produce equivalent results.

            And with the release of FileMaker 12, there's a new function: Execute SQL that has some limited abilites for using SQL select statements to query your database. It's limited, because the results are returned to a single field.