6 Replies Latest reply: May 15, 2014 9:57 AM by DavidS _ RSS

    Filter to show latest instance when several instances exist

    txstar _

      I need to build a report that gives me the latest cost per unit of a particular inventory item for 17 locations. Locations upload their data to the host daily. My accounting software produces a report that gives me a daily calculated cost per unit, by location, for every day of the month.    I can trap by date to capture the most recent cost.  The problem is, some locations are slow to send the data I need; therefore several locations are lacking the most recent cost.  What I would like to do is report the cost on those locations for which I have current data, and when I don't have current data, I wish to report on the last cost I do have.  The report I use has a new cost per row for each day, and is in location order.  How can I best extract this info?  I've experimented with every IF statement I can think of, and can't get it.  :confused:

        • Filter to show latest instance when several instances exist
          Grant Perkins

          Originally posted by txstar:

            The report I use has a new cost per row for each day, and is in location order.   /b[/quote]Hi txstar and welcome to the forum.

           

          I guess suggesting that fixing the price feed problem from the slow locations (maybe use Monarch?) would not be the idea you seek although I suspect it is what you would like to do.

           

          I would guess that many out there have the problem and can provide a full proof solution. However one thing I am curious about is the sentence I have extracted above.

           

          Is this some sort of table array with rows of products/locations and columns across that exist for each date and have (or do not have if the information has not been provided) a cost value for each date?

           

          Or have I completely misinterpreted?

           

          Is there any possibility of posting a small sample or simulation of what it looks like? I find it is very useful to be able to visualize the challenge!

           

          If you can post the sample I suspect the format will be important and may be lost on posting unless you udse the "code" function - see the Code button below the posting text entry window. There is also help about how to post structured samples in the faq menu option at the top of the page.

           

          HTH.

           

           

          Grant

          • Filter to show latest instance when several instances exist
            txstar _

            Thank you Grant for your reply.  Here is a sample of the report.  I'm only interested in the Per/Unit cost of the MED for each location. While I'd love to fix the source of my problem, there is nothing I can do to get them to feed me my data by the time I need it.  Notice location 143 has the data I need for 8/22, but location 145 only has data thru 8/21.  I will want to report the per unit cost on 8/22 for each location I am able to, and the cost on 8/21 if I don't have 8/22 data.  I really appreciate your time!  I'm not a power-user of Monarch, but would like to be able to make this work!

            [font="courier"] 

                  -


            Inventory Units -


              -- Inventory Value --

            Date  Beginning   Delvr'd     Xfered    Adjusted     Sold      Ending   Per/Unit   Dollars   

            -


                                                                                            Location       143  #143                                                                               

            Type     SML                                                                      

            1.           1                                                                               

            08/20  13316.00                  0.00      12.36-    772.64   12531.00    2.56491   32140.89  

            08/21  12531.00                  0.00      10.65-    770.35   11750.00    2.56491   30137.69  

            08/22  11750.00                  0.00       6.42     839.42   10917.00    2.56491   28001.12                                                                               

            Type Total        0.00       0.00      16.59-   2382.41   10917.00              28001.12                                                                               

            Type     MED                                                                     

            1.           2                                                                               

            08/20   3522.00                  0.00       0.63      64.63    3458.00    2.87197    9931.27  

            08/21   3458.00                  0.00       0.59      50.59    3408.00    2.87197    9787.67  

            08/22   3408.00                  0.00      27.90      42.90    3393.00    2.87197    9744.59                                                                               

            Type Total        0.00       0.00      29.12     158.12    3393.00               9744.59                                                                               

            Type     LGE                                                                       

             

            1.           3                                                                               

            08/20   4232.00                  0.00       2.84     377.84    3857.00    2.64025   10183.44  

            08/21   3857.00                  0.00       1.26     401.26    3457.00    2.64025    9127.34  

            08/22   3457.00                  0.00      15.46     396.46    3076.00    2.64025    8121.41                                                                               

            Type Total        0.00       0.00      19.56    1175.56    3076.00               8121.41                                                                               

            Loc Total        0.00       0.00      32.09    3716.09   17386.00              45867.12  

                                           G/A INVENTORY REPORT                

            Location         145 #145                For  08/20/2007  Thru  08/23/2007         

            -


                  -


            Inventory Units -


              -- Inventory Value --

            Date  Beginning   Delvr'd     Xfered    Adjusted     Sold      Ending   Per/Unit   Dollars   

            -


                                                                                            Location       145  #145                                                                               

            Type     SML                                                                      

            1.           1                                                                               

            08/20   7688.00                  0.00      16.91    3810.91    3894.00    2.53454    9869.50  

            08/21   3894.00    7366.00       0.00      87.66    3400.66    7947.00    2.47899   19700.53                                                                               

            Type  Total     7366.00       0.00     104.57    7211.57    7947.00              19700.53                                                                               

            Type     MED                                                                      

            1.           2                                                                               

            08/20   4042.00                  0.00       0.80     287.80    3755.00    2.77551   10422.04  

            08/21   3755.00                  0.00       0.12-    163.88    3591.00    2.77551    9966.86                                                                               

            Type Total        0.00       0.00       0.68     451.68    3591.00               9966.86                                                                               

            Type     LGE                                                                       

            1.           4                                                                               

            08/20   7548.00                  0.00       1.05     821.05    6728.00    2.63161   17705.47  

            08/21   6728.00                  0.00       9.80-    999.20    5719.00    2.63161   15050.18                                                                               

            /font[/quote]

            • Filter to show latest instance when several instances exist
              DavidS _

              txstar, if you always have a "Type Total" line, perhaps you can create a detail template that includes the line for the last date, the blank line following it, and the type total line. You can trap it on the "Type Total" text. of course, if you have page headers in between, you would have to eliminate those with a page header template. Each detail record would then include these types of lines:

               

              [font="courier"] 

              08/22  11750.00                  0.00       6.42     839.42   10917.00    2.56491   28001.12

               

               

                  Type Total        0.00       0.00      16.59-   2382.41   10917.00              28001.12   /font[/quote]

              • Filter to show latest instance when several instances exist
                txstar _

                Thank you David!  Worked like a charm...now why didn't I think of that?  I really appreciate your help (and yours Grant!).  Hope you don't mind if I come back again with more questions about other projects.  You guys make it seem so easy.

                • Filter to show latest instance when several instances exist
                  Grant Perkins

                  DavidS - that was a solution after my own heart - and there I was thinking of trying to do something smart with sort orders and summaries ... so easy to get distracted into missing the obvious or seeking an unneccesarily complex solution.

                   

                  txstar - glad you got a solution. In answer to your question - see above! You are not alone.    

                   

                   

                  Grant

                  • Filter to show latest instance when several instances exist
                    DavidS _

                    Grant, thanks for the accolades. Coming from you, it means a great deal. At first, I came up with a different solution, which involved creating a calculated field which combined the numerical value of the date with the cost field, then creating a summary that would return the max() of that field, which in turn would also return the date and cost field. I had it working, but as I started typing the explanation, it dawned on me that there was a much easier solution.