2 Replies Latest reply on Aug 7, 2013 7:17 AM by mikebeargie

    Date based report

    ohannis

      Good Day guys.

      I have created a small database with the following tables.

      -Sales which includes item number, qty sold, price and invoice date,

      -Purchases which has purchase date, part number, qty purchased and price.

       

      I want to generate a weekly report of the inventory what will tell me what was sold from Monday to Friday.

       

      my brain is tuck now any help will be appreciated.

       

      Thanks

        • 1. Re: Date based report
          ohannis

          I have something like this in my mind. but I don't know how to execut it.

          If my sold quantity of an item is grater then 0 (qty is a field in the database) With the dates selected from (week start field and week end field) then display these products.

          any ideas ?

          • 2. Re: Date based report
            mikebeargie

            Hi ohannis, welcome to technet!

             

            Based on your description, you should ideally have a third table for "products", in addition to "sales" and "purchases".

             

            Your inventory report would then just be records from the products table, and you could find a date range based on a sales portal, related and seen from the products table. You should store sales as transactions, one record for each sale.

             

            So, to spell it out: "I want a list of all products, which have had sales in a specified date range". This means you're looking for a list of products (Products table), where there has been more than one sale of said product (sales table) in a date range. Always helps to write it out to see what tables and relationships you need.

             

            It sounds like you are probably new to Filemaker, as well as the RDBMS concept. You may benefit from using one of Filemaker's starter templates. There is an inventory starter template that may already have a lot of what you need.