Page 1 of 1
Customising built in reports
Posted: 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.
Posted: 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.
Build from scratch
Posted: 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.
Posted: 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?
Posted: 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.
Posted: 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.
Posted: Mon Apr 11, 2016 6:23 pm
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.
Posted: 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.