5 Replies Latest reply on Jan 21, 2011 9:33 AM by philmodjunk

    How to create a new database for reports by filter?

    NimrodMargalit

      Title

      How to create a new database for reports by filter?

      Post

      Hi.
      Im trying to create a new database that will allow me to create sales report each month.
      I have 1 table with my product id and i would like to create another table with the product id's and the amount they sold each month(updated each month).
      Now what i want to do is that i would be able to create a report from these 2 tables that the 1'st table will be filtered by the product id's in the second tables so finally i can see all the product id's that made selling and the amount of each one...

      Am i designing the structure right or is there a better way doing this?

      Thanks 

        • 1. Re: How to create a new database for reports by filter?
          philmodjunk

          Not sure that I understand why you need two tables for this.

          It's possible just to perform a find on your existing table to find just those items purchased in a given date range without needing the extra table. These records can then be sorted in to a summary report that lists info about each item sold during that time period. There are many different types of reports fitting your general description that are possible here. The report, could for example, list each item  once, but show the total number sold during that month.

          • 2. Re: How to create a new database for reports by filter?
            NimrodMargalit

            Hi.

            I will explain why i do need 2 tables.

            The first table has all the data of the products(items), this data i dont get inside my sales report... For example from what catalog is this product and etc... for this i need to take the products table and do a filter by the sales table. 

            If you can find a better way to create this structure i'd be thankful, but i couldn't think of any.

            Anyway, im having trouble building it like this too, i've made the relationships but i couldn't create a report or a portal that will do the filter...

            • 3. Re: How to create a new database for reports by filter?
              FentonJones

              The Sales table has the ProductID (as a foreign key). Reports would be from the Sales table. All data in the Products table is available from Sales via the relationship on ProductID.

              • 4. Re: How to create a new database for reports by filter?
                NimrodMargalit

                I Know... this is what i tried to do.

                I did a relationship between the two, but i wasn't able to do the portal for some reason or another way to filter the results from one to another...

                • 5. Re: How to create a new database for reports by filter?
                  philmodjunk

                  No need for a portal, just the correct relationship between your two tables.

                  I'm assuming this basic relationship typical of most sales or ordering systems:

                  Invoices---<ItemsSold>----ProductInfo

                  ProductInfo::ProductID = ItemsSold::ProductID
                  Invoices::InvoiceID = ItemsSold::InvoiceID

                  You create a new record on an Invoices layout with a portal to ItemsSold to list Items sold and quantity with product descriptions, pricing, etc looked up from ProductInfo.

                  If you base your report layout on ItemsSold (Specify ItemsSold in Show Records From in Layout Setup...), you can add fields to this report layout from Product Info and Invoices just as though you were adding them from ItemsSold.