6 Replies Latest reply on Oct 21, 2016 10:50 AM by philmodjunk

    One report for purchase and sale records of products from seperate tables.

    akhlaq38

      I have a typical structure of my database like;

      Customers -------<Sale Invoice--------<Sale Line Items>--------Products---------<Purchase Transactions>---------Purchase Bill

      Every thing is working Ok and well setup. But when tried to design a report layout and I made many to many relationship of Sale Line Items and Purchase Trans tables. This report is critical as need to see which are the more running items to take future decisions. I chosen second table to get data and later added fields from first table. Both have their own date fields. I sorted data based on Product ID/Name and seen the data of purchase table shown correctly with all transaction with dates but record of sale table showing only first record repeated equal to purchase records. I came on a decision that I should redesign the database to make one table for both purchase and sale entries where there will be one date field for both sale and purchase transaction with different record numbers.

      Before doing it I want to know if there is any other way or solution to do it with same structure?

      Thanks for giving time.

        • 1. Re: One report for purchase and sale records of products from seperate tables.
          wimdecorte

          akhlaq38 wrote:

           

          I chosen second table to get data and later added fields from first table. Both have their own date fields.

           

          This is not clear, can you explain using the names of the tables and names of fields?

          • 2. Re: One report for purchase and sale records of products from seperate tables.
            philmodjunk

            If you were to use the same data source table for Sale Line Items and Purchase Transactions, your reporting might be easier. You might use different Qty fields for purchase and sales as well as different match fields for billing and invoices, but all in one table.

             

            Another option you might explore is a report table where you import records from these two separate tables in order to be the data into one table for report purposes.

            • 3. Re: One report for purchase and sale records of products from seperate tables.
              akhlaq38

              Actually I chosen "Purchase Trans" table to get data to design report layout in list view. It has fields like ProductIDfk, Purchase Date, Qty Purchased. Then I added fields into same layout from "Sale Trans" which have fields like ProductIDfk, Sold Date, Qty Sold. As the field ProductIDfk is common in both tables and coming from one table "Products" so I made relationship of many to many. These both tables are children of main table Products as shown in above structure.

              • 4. Re: One report for purchase and sale records of products from seperate tables.
                akhlaq38

                Hi phil

                I worked on both of your suggested options. Both worked but not fine. Each have some drawbacks. In first option, I need at least two separate tables for purchase entries and sale bills which share a table of transactions. But then those have two separate fields for dates of purchases and sales which you know will not give required results for monthly report. If I make one table for both purchase entries and sale bills with a common table of transaction for portal, it will give accurate results in reports. Then I can create separate layouts for purchases and sales and if I create a record in purchase layout and enter some data of products in portal, it will show up again in second layout of sales with empty fields of sale qty. And vise versa. That is annoying and looks a poor design.

                For second option, user need to import data from separate tables each time he want to see report of product summary.

                Second option look attractive if there comes a button with script of import steps recorded which can import data before run a report.

                phil I have seen you answered many of similar questions. Can you refer any of your answer which could help in my case. I searched a lot in this community but all are different cases.

                • 5. Re: One report for purchase and sale records of products from seperate tables.
                  akhlaq38

                  Ok I have got the solution.

                  I kept the Purchase Order and Sale Bill separate tables. These two share data to their child table Transactions. This Transaction table has its own single field for Date. This date set to Creation date or to fill manually. Purchase and Sale tables have their own dates fields. Report will only see the date of transaction and show record on month or any date range from this field. Report of purchase and sale is working fine now and no drawback in my database.

                  Thank you for help as usual.

                  • 6. Re: One report for purchase and sale records of products from seperate tables.
                    philmodjunk

                    I did not see your earlier reply and questions, but I see that you finally figured out how to do what I was suggesting.