Item list report by last date used

RebeccaRebecca Member Posts: 34
edited July 22 in Accounts Business Range

Hi, 


Is it possible to run a report for my Item List that shows the last date used? 

My (non inventory) items list is getting too long and I would like to make inactive any item/part we have not used for 2 years. 

I am using the software version of Reckon Accounts 2019. 

Thanks.  

Bec

Comments

  • PhuongDoPhuongDo Reckon Developer Partner Posts: 217 ✭✭
    edited July 22
    Hi Rebecca,

    I don't think there is any report in Reckon that can help you to see that easily.

    I can write a quick tool for you to make all the items (that are not used for x days) inactive. So you don't even need to manually go through the list to do that. Just a few click and done.

    Let me know if you are interested. I also offer a very reasonable price to support businesses during this pandemic.

    My email is [email protected]

    Thanks,

    Phuong


    Phuong Do / Reckon Developer Partner

    [email protected]

  • RebeccaRebecca Member Posts: 34
    edited July 22
    Hi Phuong, 
    I am very much about self-sufficiency and would prefer to learn how to do it myself.  Are you talking about an external tool that I would run in my Reckon program?  Sounds a little suspicious for someone like me who is quite conservative.   

    Would love the help if you could set it out step by step.

    Thanks. 
    Bec

  • PhuongDoPhuongDo Reckon Developer Partner Posts: 217 ✭✭
    edited July 22
    Yeah, I understand your point of view. It will be an external tool that can connect to Reckon Desktop using their SDK. The app can be removed once done. When you run it, you can choose a date and the app will get all the items that are inactive since that selected date, then it just goes thru the list and makes them inactive.

    Another better option instead of inactivating them, is that you can merge them into one item, for eg, called Inactive item. This way, not only you shorten your active item list but also free up a lot of space of items (means you can add new items without reaching the limit)

    I recently did a small app (similar to yours) but to merge customers. The client has duplicate customers (but in different naming format, they have both Scott Tiger and Tiger Scott and now want to merge to Scott Tiger), the app I wrote goes thru the 100K+ records and merge a lot of them. 

    If you think you would go with my solution, I can just go ahead and create the tool, show you how it works from my PC, then if it works the way you want, I can help you to run it in your PC (we can run in a dummy file or a copy of your file first).

    I hope it makes sense :)

    Thanks,

    Phuong


    Phuong Do / Reckon Developer Partner

    [email protected]

  • RebeccaRebecca Member Posts: 34
    edited July 22
    Thanks Phuong, 

    I know tech can be used for good as well as bad. :)

    That is a great idea to merge them, but I still kinda want to be able to track the history of items if needed at a future date.  

    Also I was just looking for a quick fix.  I think I only have around 700 items, so I'm actually just in the process of running a Fast report on each item, looking at the date from the most recent transaction, and then choosing to make inactive or not.  It will take me a few hours, but that's ok I guess. 

    Thanks again for your offer. 

    Kind Regards, 
    Bec

  • PhuongDoPhuongDo Reckon Developer Partner Posts: 217 ✭✭
    edited July 22
    Cool :) Happy to share some ideas. And feel free to ask me if you have any questions, tech can help your business process tremendously :)


    Phuong Do / Reckon Developer Partner

    [email protected]

  • RebeccaRebecca Member Posts: 34
    edited July 22
    Thanks.  Will keep in mind.  :)

  • gazza73gazza73 Accredited Partner Posts: 1,432 ✭✭
    edited July 29
    Rebecca, 

    using just the Reckon product you have,  you'd need to use a transaction based report (which contains the date that an item was sold), as opposed to an ITEM report,  which only deals with overall status of what an item entails (rather than when it is used)..

    You could run a report called:  SALES BY ITEM DETAIL,     and let me suggest a strategy that might help.

    First,  consider whether there is a target OLD date that you believe represents a time in the past, when you think is 'so long ago it is too long ago to be considered active'.   Let's say that is 18 mths ago as an example.   

    MODIFY the parameters of the SALES BY ITEM DETAIL report as follows:
    a)   Date range to be from 18mths ago thru to today   (therefore showing only 18mths of transactions .
    b)  then SORT BY 'DATE' (ascending).    This will sort the  transactions into chronological sequence.

    Cast your eye down the report and you can observe a couple of points.

    1.  If an item is NOT ON THE REPORT, then it represents an ITEM that by your definition of time,  it should be marked as inactive.
    2.  Look at the last transaction entry of each item, in conjunction with just how many transactions are in fact shown FOR THAT ITEM,   and make a subjective decision based on the number of sales you made, and how long ago (in the parameter of time, like 18mths potential)  and consider whether it is still a big seller or not, and then determine if you want it to be inactive..


    Another way,  is to consider the report called: SALES BY ITEM SUMMARY,  and again,  consider the DATE RANGE.    This is a shorter report but you only get to consider the QTY field.   You  can't determine from this report whether you sold QTY 120 to one customer compared to 120 single sales to 120 customers.   AND,  you can't determine from a BUSINESS PERSPECTIVE whether you risk losing a good customer who buys lots of different items from YOU,  purely because you also provide the service of selling some unusually item.    Don't lose sight of that marketing/sales perspective when dropping inventory items off your price list too fast!

    For a more technical research of the DATA<  you can EXPORT these reports to EXCEL or LibreCALC, and do some data manipulation to check for LIKE/COMMON line items from the DETAIL report and do some fancy data compression to leave just the last (most recent) dated ITEM to achieve what you asked for in this forum question in the first place.   You dump the data to a spreadsheet.  Then you copy that for  a fallback position.  Then you compare adjacent ROWS for the same item code,  and progressively put a YES/NO against duplicates or not.  When you come to the last of many ITEM#1 and see the next line being the start of an ITEM#2,  than you mark the LAST OCCURRENCE of ITEM#1  with a NO.   Otherwise all the duplicates are marked with a YES.   Then resort the whole SHEET so that the "NO" entries fall beside each other, and delete all the 'YES' lines.  The result is a SHEET that shows ITEM#, and latest date SOLD.

    But frankly,  from a business point of view,  the eye-balling of the NUMBER of transactions as opposed to just the QTY of an item sold,  is a more meaningful exercise to really understand how volatile/useful your stock really is.  (Hence the SALES BY ITEM DETAIL report)


    Gary Pope
    m: 0408994799
    e:  [email protected]
    An Accredited Partner- Consultant (VIC. Aust)
    http://www.alchester.com.au/reckon-ac
    “Working with Accountants/Bookkeepers PPs/APs, as an
         independent IT Professional and retired FCPA Accountant”


Sign In or Register to comment.