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.
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
Now I can order the results by DeliveryDate, either ascending or descending. Ascending order would be:
And descending would be:
neither of which is the order in which I need to look at the records. What I need is:
- 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
-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.
Whatever i understood i am giving its sol'n
Just go through these steps
Enter find mode
New Record request
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...
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:
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.