4 Replies Latest reply on Jul 12, 2012 5:47 AM by philmodjunk

    Searching for date ranges -this time it's serious

    PeterThorton

      Title

      Searching for date ranges -this time it's serious

      Post

      Hello,

      I have a problem I need find an answer to. I have a very specific question, related to a set of complex scripts that work in tandem to deliver the desired result. The answer to this problem is, if you will, the last missing piece of the puzzle.

       

      I'm searching for orders within a given month, using the following script steps:

      Enter Find Mode []

      Set Field [Order::OrderDate; $$CurrentDate]

      Perform Find []

       

      The variable $$CurrentDate has a value that looks like this

      4/2012

      I've extracted this value from the date the user inputs. This way the search returns only the orders made within the specified month.

      However, there is a similar search I need to do. I need the search to find all the orders that were made in the given month, or before. I.e. if I get "4/2012" as the input, I need to find all the orders from 4/2012, 3/2012, 2/2012, 1/2012, 12/2011, and so on. So far I have no clue as to how to write the search parameter.

      If anyone knows how to write a date range parameter, something like "from 12/2011 to 4/2012", that would be helpful, because I know the month when the company started making orders, and I can set it as the start of the range.

      If you know how to write a parameter that would find all the records up to a certain date, like "everything up to April 30th, 2012", I'd be interested to see it, but it won't help me that much - the input parameter I get is already shaped to the "4/2012" format, and changing that would be far too time consuming.

       

      Thank you in advance.

        • 1. Re: Searching for date ranges -this time it's serious
          philmodjunk

          Set Field [Order::OrderDate; "<" & $$CurrentDate]

          Also, if the user enters a date in a date field with global storage specified, you can do it this way:

          Set Field [Order::OrderDate; "<" & Month ( Order::GlobalDate ) & "/"  & Year ( Order::GlobalDate ) ]

          The key requirement for this option is that GlobalDate must be a field with global storage enabled.

          • 2. Re: Searching for date ranges -this time it's serious
            PeterThorton

            Thanks, that was helpful.

            My instructions, however, have changed again, and I have another nut to crack. This is going to be a long post, and I apologize for that.

            First, let me describe what I'm trying to achieve in a bit more detail. I'm working on sort of a monthly summary by product, whererein I calculate things like amounts bought and sold, prices, stock levels, and so on, for any given product. One of the values I need for the calculation is "freight price". Freight price is stored in the database as the value of a "freight" item on an order. A product can be on many orders, and most orders have a freight price specified (it's freight for the entire order, it will be divided by the number of items (don't try to argue against this, I tried, but those are my instructions))

            So there will always be multiple freight charges relevant to a single product. In order to identify the most relevant freight charge, I've been given the following instruction:

            Take the latest non-zero freight charge from an order within the given month, that has the product on it. If there's no such freight value (the product may not have been ordered at all that month, but still needs to appear on the report for other reasons), go forward to the next month and do the same (i.e. if the reports is made for April, go to May, and so on).

            This complicates things for me significantly. I'll illustrate how, but first a note on the date format I'm using:

            There are three date formats I came across so far:

            European - day/moth/year - 12/7/2012 - from smallest time unit to biggest

            Japanese - year/month/day - 2012/7/12 - from biggest time unit to smallest

            USA - month/day/year - 7/12/2012 - arbitrary

            I decided to use the Japanese format for this post for two reasons:

            1. the Japanase format seems best for visually representing which date is bigger and which is smaller.

            2. the Europan format (which I'm used to) and the US format (which I assume most of this forum's contributors are used to) are mutually confusable, to the point where there is no way of telling which format the date is in, if the day of the month is smaller than 13. 12/7/2012 could be July 12th in European format, or December 7th in US format.

            So back to my problem:

            If I want all the orders from $$CurrentMonth and later, I can write

            >= $$CurrentMonth

            Now I can order the results by DeliveryDate, either ascending or descending. Ascending order would be:

            2012/4/3

            2012/4/25

            2012/5/1

            2012/5/6

            2012/5/14

            2012/6/11

            2012/6/18

            And descending would be:

            2012/6/18

            2012/6/11

            2012/5/14

            2012/5/6

            2012/5/1

            2012/4/25

            2012/4/3

            neither of which is the order in which I need to look at the records. What I need is:

            2012/4/25

            2012/4/3

            2012/5/14

            2012/5/6

            2012/5/1

            2012/6/18

            2012/6/11

            - ascending by months but descending within a single month.

            Is there a way of doing this?

            It would simplify things for me greately, because the alternative is doing loops within loops within loops:

            loop through months

             search, sort,

              -loop through orders

              --loop through order items to find freight

            this is a pain to write, prone to errors which are hard to debug, and those nested loops take pretty long to execute.

            Thank you.

             

            • 3. Re: Searching for date ranges -this time it's serious
              Abhaya

              Hi

              Whatever i understood i am giving its sol'n

              Just go through these steps

              Enter find mode

              setfield..

              New Record request

              set field

               

              so on

              perform find

              Sort by date field

              In all the set field  step just put all the date's condition like 4/2012 ,3/2102,1/2012...

              Hope you will get your answer...

               

              Thanks

              • 4. Re: Searching for date ranges -this time it's serious
                philmodjunk

                I suggest setting up some calculation fields that use the month and day functions to extract those values into separate fields. That makes it simple to sort by Month in ascending order and day in descending order.

                With regards to your report, I'd need to know the tables and relationships in your system before I can suggest much.

                Typical invoicing systems are built around this backbone in most cases:

                Customers---<orders-----<LineItems>-----Products

                And am I correct that you need to list all products in this report even if they were not sold during the month of a given report? Or is it just that a Product may be sold, but not on an order with freight charges for that month?

                PS. I know the forum software allows you to vote a thumgs down on a suggested solution, but I recommend against it as you can't use that method to tell the person trying to help you why it doesn't help you. I recommend either ignoring their post in favor of one that does help you or politely responding back with a "thanks but this doesn't work because..." message.