Customising built in reports
  • JeffG
    Posts: 91
    Joined: Sat Aug 31, 2013 6:50 pm
    Location: Sydney

    Customising built in reports

    by JeffG » Thu Apr 07, 2016 8:58 pm

    Is there any way that I can copy the code of an inbuilt report and then be able to edit the copy. For example, I want to copy the Backorder By Customer Report (Cus09) and then limit the results of the copied report to a specific product code.

    Reason being we need to purchase a lot of one-off products for customers, they are entered into the system as a Misc item but the only way I can retrieve the comments of what the item is is by printing the whole report when I only want the Misc Item comment.

    Thanks in advance.
  • COBS Tech Support
    Posts: 678
    Joined: Fri Sep 09, 2005 8:23 am

    by COBS Tech Support » Fri Apr 08, 2016 11:40 am

    Unfortunately no. The internal reports can't be modified. If you require a customised version you have to build the report again from scratch using the Visual Builder report design tool. Alternatively, you can add your request to the Wishlist area and see if the request gets added to the next upgrade.
  • JeffG
    Posts: 91
    Joined: Sat Aug 31, 2013 6:50 pm
    Location: Sydney

    Build from scratch

    by JeffG » Fri Apr 08, 2016 7:27 pm

    ok, no problem. but if you could tell what table comments are kept in after pressing the insert key on a sales order are, that would be great.

    thanks
  • COBS Tech Support
    Posts: 678
    Joined: Fri Sep 09, 2005 8:23 am

    by COBS Tech Support » Fri Apr 08, 2016 8:05 pm

    Are you referring to the comments you might write under a product item description (on the Items tab), or comments entered somewhere else?
  • JeffG
    Posts: 91
    Joined: Sat Aug 31, 2013 6:50 pm
    Location: Sydney

    by JeffG » Mon Apr 11, 2016 10:32 am

    Correct. We have a generic product item called MISC - generally used for one off purchases. Then at either Sales Order or Invoice time, we press the INS key to add any comments as to what the one off purchase actually is. We do this because we buy a lot of one-off items for customers that they just don't know where to get it.

    When in Order Control, the item only shows up as MISC and we cant see the INS description. The INS description is available if we print an entire back order report, but more often than not, it does not suits us to run off an entire back order report to do this.

    This inserted comment is what I need to see on a report.

    Thanks
  • COBS Tech Support
    Posts: 678
    Joined: Fri Sep 09, 2005 8:23 am

    by COBS Tech Support » Mon Apr 11, 2016 11:05 am

    You can zoom in on the original order from the Back Order Control Centre to see the original comment. Otherwise that information is stored in the OSTOCK table for sales orders.
  • JeffG
    Posts: 91
    Joined: Sat Aug 31, 2013 6:50 pm
    Location: Sydney

    by JeffG » Mon Apr 11, 2016 6:23 pm

    Thanks.
    I have linked ORDERS.OORDERNO to OSTOCK.ORDERNO -> OK

    I can see the information I am looking for in OSTOCK.OTITLE -> GREAT

    What I cant see is where to link OSTOCK to say something like STOCK or some other table, so I can filter by something like Product Name.

    I guess I have to link OSTOCK.OCODE to something so I can select a product name of "MISC"

    any and all guidance is very much appreciated. thanks.
  • COBS Tech Support
    Posts: 678
    Joined: Fri Sep 09, 2005 8:23 am

    by COBS Tech Support » Tue Apr 12, 2016 8:51 am

    What you're attempting to do here is moderately complicated. A simple link won't connect the data. The extended description or comment may be wrapped over multiple OSTOCK records. The only way to retrieve that information would be to write a script to step through those records and gather up the text. Each record in OSTOCK has information on the sales order, the product line item and if that record is part of a 'group' (i.e., comment or extended description) -

    OORDERNO - Order/Transaction Number
    LINEREF - Group ID
    OCODE - Stock ID

    If you identify the matching record in OSTOCK to the one in the Back Order, you can probably use the TRANREF index to then match the applicable lines. Use the SKIP() function to move through each record and store the text found in OTITLE in a string. When the LINEREF ID changes, the record is then referring to a different line item.

Who is online

Users browsing this forum: No registered users and 1 guest