How to find the last purchase date on all items

Sort:
You are not authorized to post a reply.
Author
Messages
Bev Edwards
Veteran Member
Posts: 366
Veteran Member
    How would I find the last purchase date for all items in the ITEMMASTER table?
    JonA
    Veteran Member
    Posts: 1162
    Veteran Member
      This is how I find the last purchase date for items. Query POLINE and PURCHORDER using Crystal or MS Addins. Select PO DATE, ITEM, DESCRIPTION. In Crystal you can use a formula to return the max value for PO DATE for each item. If using Addins, put the data in a pivot table and use the max value of PO DATE to summarize.
      Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
      Bev Edwards
      Veteran Member
      Posts: 366
      Veteran Member
        Hi Jon,
          Thank you for the direction. My Director wants the most recent purchase date for every item in our Item Master. When I try to pull the data using MS Add Ins, it times out. I'm not familiar with Crystal.

        You gave me a place to start though.

        Thanks again!
        Bev
        jlgonzal
        Basic Member
        Posts: 7
        Basic Member

          Change the retrieval time-out value under the addins preferences option or reduce the data date range to avoid the timeout issue. 


          Bev Edwards
          Veteran Member
          Posts: 366
          Veteran Member
            Thank you. This action produced results, but after the query reached the 1001 record, I received a system error that said An error occurred outside of IOS while accessing Lawson DB. Not sure what to do with this. I'm reaching out to our IT department to see if they can assist me with writing a Crystal report and using the Max PO Date value in a formula.

            Thank you for your help!
            Bev
            brupp
            Veteran Member
            Posts: 165
            Veteran Member
              Hi Jon - What is your Crystal formula for finding the max value? We are on version 8.5. I am using a pivot table right now & would love to have a report for others to use. Thanks in advance!
              JonA
              Veteran Member
              Posts: 1162
              Veteran Member
                Maximum ({PURCHORDER.PO_DATE},{POLINE.ITEM})

                I'm using CR Pro 11. What I usually do is create a group with POLINE.ITEM and throw the formula in the group header along with the description and any other info I might need and suppress the details section.  Leaves you with a nice list of all the items and last date ordered for whatever date range I selected.
                Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
                brupp
                Veteran Member
                Posts: 165
                Veteran Member
                  Thanks Jon. I ended up creating a group & suppressing the details. Very handy & more efficient than clicking Next on PO63. We've had several facilities leave our system over the last few years so the report is wonderful in that I can exclude those facilities.
                  You are not authorized to post a reply.