3 Replies Latest reply on Oct 14, 2009 12:10 PM by philmodjunk

    Report Generation - Querying like access

    sugarking

      Title

      Report Generation - Querying like access

      Post

      Hi,

      I am a new user of filemaker who likes the simplicity. However, i am having problems understanding some concepts. I have a program that i created to track sales. I created a table called order that has the following fields (order id, clientid, etc) then i also have a table related called orderline ( that it linked to the main table and this one tracks the items that are being sold ( stockid, quantity, etc ). However, i am having trouble understanding how to create a report :

       

      1) that will allow me to filter or report to me the sales in one month ( i really don't know what tool i need to use --> i know in access you do queries )

      2) that will allow me to cross reference the sales vs the amount paid

       

      I would like a guide line, i do sales for a living and i thought of filemaker to help me track the sales.

       

       I look forward to receiving some help !!!

       

      thanks to you all

       

      Sugarking

        • 1. Re: Report Generation - Querying like access
          philmodjunk
            

          In filemaker you find groups of records by "Performing Finds" instead of pulling up a wizard that builds a SQL query for you. For simple data searches this is much easier, especially since the find is performed on the layout (in access this would be a "report" or "form"), not in a separate window that pulls up a table view of the data when you're done.

           

          Basic steps:

          Enter find mode

          Type criteria in each field as needed

          Click Perform Find

          Filemaker then pulls up all matching records for you to click or scroll through.

           

          To find all records dated 5/1/2009...5/31/2009, (MMDDYYYY format):

           

          Enter find mode and type in the above date range exactly (date1...date2), or just enter 5/2009

          click perform find and you've got them.

           

          For item 2, you'll need to be more specific. The method used will depend on how you are recording payments and billing. Do you have a separate table recording payments? Can a given invoice record multliple payments or just one?

          • 2. Re: Report Generation - Querying like access
            sugarking
              

            Ok. Got that !!! Now for number # 1, 

             

            I am trying to understand. But, when i do a FIND i only get the items for the particular client (good !) but it will not show me continuosly all the details of the subsequent orders. Only the first line or the first item of the order. I tried using a portal but still it will not show the 30 items accumulated in all the orders. 

             

            The find process works, but i am not able to show all the items for "Bob" I want to be able to summarize how much "Bob" has spent on all his purchases.

             

            For number 2, i am using a separate table called "payment registry" where i include the clientid, the account where i deposited the money (collected), the purchase order which is related to  ( order and order detail ), total amount paid, date of the deposit and some other details.

             

            I would like to be able to say "order #1" has been cleared because all the money has been collected.

            or

            show all the money collected and the oustanding balance.

             

            Basically, i want to be able to have a report that shows me : all the items and the item the person has ordered and all the money collected towards the various orders.

             

            However, if i do "send records to EXCEL" it will send me all the data correctly. ( it is strange in filemaker ) 

             

             thanks again for your help

             

             

            • 3. Re: Report Generation - Querying like access
              philmodjunk
                

              "I am trying to understand. But, when i do a FIND i only get the items for the particular client (good !) but it will not show me continuosly all the details of the subsequent orders. Only the first line or the first item of the order. I tried using a portal but still it will not show the 30 items accumulated in all the orders. 

               

              The find process works, but i am not able to show all the items for "Bob" I want to be able to summarize how much "Bob" has spent on all his purchases."

              In any database, the devil is in the details and not all the details to your database design are immediately apparent. Your original post specifed simply searching for all records dated within a given month for example.

               

              First issue, what table are you searching with your find request? Do you want to see a single list of all "Bob's" order items for all invoices posted within a given date range or month? Or do you want to see a list of all Bob's invoice records for a given time period? Or a found set of his actual invoice records?

               

              The precise details you go through to set this up depend on what you want to see when you're done.

               

              I'm guessing the first option is what you want.

              Set up a list view layout based on your order items table. You can place customer fields such as a customer name and Invoice dates from the related tables in the header or a sub-summary part. You can create a summary field to total all the purchases as a field in your Order Items table and place this field in either a footer or trailing grand summary of this layout.

              Select this layout, enter find mode and enter your search criteria. You can enter criteria such as an invoice date or date range in fields from a related table.

              Perform the find. Sort the found set if needed (and it will be if you have sub-summary report parts.) and you're done.

               

              Let's see if you can get that to work to your satisfaction first. The second issue will likely be resolved through proper relationship design and the creation of a few calculation fields to sum up total related payments and compare that amount to total related invoice charges.