3 Replies Latest reply on Jan 13, 2010 6:15 PM by comment_1

    Inventory Report



      Inventory Report


      I have an interesting problem:


      I have created a Dealer Information System, that we install in our dealers to track sales to their customers. We then pay the dealer and sales rep commission based on the sales.


      I have serveral table, as outlined below:


      1. Inventory Data: contains SKUID, SKUName, SKUexworksPrice, etc, This table will grow as  the dealer adds new lines and SKUs.

      2. Customer Data: contains CustomerID, name, etc

      3. Order Table: looks up the customer information and is then linked to InventoryOut table

      4. InventoryOut Table: when a ordered is completed in the order table the information is saved here as Customer ID, SKUID, SKUOrderQuanity, SKUTotalprice, RecordID(RecordID is linked to the recordID in the Order Table).

      5. CustomerOrder Table: This just contains the CustomerID,  (When an order is place this information is written to this table, it checks for Duplicate ID first. This eliminated the problem of trying to sort the InventoryOut table for duplicate Customer IDs when it comes time to record payments from the customers).


      Now I am trying to create a sales report that would look like this:


                                           SKU1             SKU2            SKU3            SKU4     .......etc

      Customer 1                    (Total)           (Total)          (Total)          (Total)

      Customer 2                    (Total)           (Total)          (Total)          (Total)

      Customer 3                    (Total)           (Total)          (Total)          (Total)




      Grand Total                   (Total)            (Total)         (Total)         (Total)


      I have set up a new table "Inventory Report" This is linked to all the above table:


      I created a body in the Inventory report table that contains the CusomerID and a Portal that links to the inventoryOut Table. I can easily see all the orders for a certain period and customer, but I do not want to see each individual order, just the Quanity totals  (As per the above).


      I tried to set a find request that use a  repeating field, in each field I can put the SKU that I want to see and the portal will show me all the records for the SKUs that are listed in the find's repeating fields. However I can not seem to just develop a way to summarizes all the information that I want in the above format.


      any help is appreciated





        • 1. Re: Inventory Report

          Yep, the format you describe takes a bit more work. A vertical list is more flexible and simpler to set up:


          Like this:


          Customer 1

            SKU1 Total

            SKU2 Total

            SKU3 Total


          Customer 2

            SKU1 Total

            SKU2 Total


          If You can set up a portal that lists all the SKU totals for each customer record, there is a "horizontal portal" trick that can set up the columns for you. Your layout would look like this:


          [Customer Field] [Portal 1][Portal 2] [Portal 3] ...


          Each portal references the same Table Occurrence, but Portal 1 is set to inital Row: 1 Number of rows: 1, Portal 2 is set to Initial Row: 2 Number of rows 1 and so forth to rearrange the rows horizontally instead of vertically.


          Note that your report will be limited by the width of your page on how many different SKU totals you can list for each customer.

          • 2. Re: Inventory Report

            Thanks for your reply,


            Yes I also used this method as a backup, I was hoping that there was a way to do it without creating all the separate relationships for each SKU.


            I decided to limit the report to 4 or 5 SKU at one time  and made a separate field and relationship for each SKU that is searched.