In another thread, Erika wrote:
Quote:


Hi Erika!

I remember you mentioning this at the conference, and I made a note that it was important to me, but I've forgotten just how and in what instances Piping should be used.  Can you explain its use in a bit more detail?

Is it just for date calculations?  Should I place pipes around all my date manipulations (and by all I mean form programming as well as report programming)?  Would it serve any purpose in a programming calculation such as:

Invoice_Age = @ToNumber(@Date) - @ToNumber(Invoice_Date) 

or a report calculation such as:

Var MonthTotal as Double
MonthTotal = @ToMoney(@XLookup("MonthTotals.db", @Year(@Date) - 1, "Year", @Month$(@Date)))
Last_Year = "Last year's total for " + @Month$(@Date) + " was:  $" + @STR(MonthTotal) 

Or is it just for retrieve specs?  Here's one I use to retrieve this month's invoices:

{@Year(Invoice_Date) = @Year(@Date) AND @Month(Invoice_Date) = @Month(@Date)} 

How would piping be used on that?  Is it as simple as replacing the curly braces?

Thanks!

Quote:

You can't use the pipe for this because it refers to a record value - Invoice_Date. I believe there is a way to construct a retrieve that will work. I'm not where I can test though. I'll try to post it later.

Holy guacamole, what the heck is that?  But hey, it works.  Can you explain it to me?  And where should I put the "-1" for 1) Last month's and 2) last year's.

Thanks Ray!

@Right("00" + @Str(@Month(@Date)), 2) pads the month number out to two places. A date is stored as YYYY/MM/DD, where MM is the month. For September it is stored as 09 but @Month simply returns 9 so we need the other zero before it, so that the retrieve works.

Last month is tricky as last month may have been December and subtracting 1 from 1 does not give you 12 nor does it give you the right year. It's gonna be a tricky statement.

-Ray

Explanation:

] <-This character makes it a string search instead of a date search

|@Year(@Date)| <- This gets the current year

/ <-This inserts a slash into the criteria

|@Right("00" + @Str(@Month(@Date)), 2)| <-This gets the month number padded to two numbers as described above

/.. <-This inserts a slash and then dot dot for any day in that month

So what you end up with(If you look at it after the compiler) is

]2007/09/..

-Ray

For all of last year it would be

]|@Year(@Date)-1|/.. 

-Ray

I've been having fun with these; I've crashed Sesame 4 times because I've put the math in the wrong places.  But when the smoke cleared:

This month last year

]|(@Year(@Date-@DOM(@Date))-1)|/|@Right("00" + @Str(@Month(@Date)), 2)|/..   

Last month last year

]|(@Year(@Date-@DOM(@Date))-1)|/|@Right("00" + @Str(@Month(@Date-@DOM(@Date))), 2)|/..   

I now have a nice little library of programmed retrieves that make use of piping and are not tied to an element.  Thanks Ray and Erika!

Well, one is already posted above as something not to do.  I'll mail the others.

Temporarily change your system date to January to test it out.

Remember to change it back again.

Good reminder Carl. and thanks to Alec for the article.  I have used Alec's method and agree with using the format of "yyyy/mm/dd".  Usually the easiest and most reliable.

I had a similar experience where some trial software thought it had been expired, and I had to remove, edit registry values, redownload, and reinstall.  We are lucky that Sesame provides us the tools to do that.  Can't always do that with other programs that need dates/times in the testing.

Quote:

Oh, yeah. Thanks for reminding me why I suggested GlobalValues in the first place.

Your example only retrieves this week's records. The first half needs to have a "-7" in there, like this:

{ Eingang > @Date - @Dow(@Date) -7 AND Eingang <= @Date - @Dow(@Date) +7 } 

Which can then be put inside pipes like this:

>|@Date - @Dow(@Date) -7|..<=|@Date - @Dow(@Date) +7| 

Note that it must be placed in the Eingang LE.

Quote:

Actually, to clarity, this will find all of last week AND all of this week - not just the last 7 days. Meaning as of today (09/23/2010) it will find dates from 09/13/2010 through 09/26/2010, which also includes dates a few days into the future.

Quote:

Again, as of today (09/23/2010), this will find dates from 09/20/2010 through 09/26/2010. This one is like you indicated, but I wanted to clarify it for onlookers.

Answering my owe  question, it does fail because the year changed.  Changing my system time and using the following will retrieve January's invoices:

]|@Year(@Date-@DOM(@Date))|/|@Right("00" + @Str(@Month(@Date-@DOM(@Date))-1), 2)|/.. 

Is there a generic fix that retrieve two months ago in spite of the year change?

Thanks again for your help.

BOOYAH!  Ray, you are the BEST!  Works like a charm!

(When you've got a minute, could you break down what it's doing?  Thanks!)

Hello Scott,

Since you know what most everything else does I'm going to focus on this part of the syntax that repeats itself

@Date-@DOM(@Date)-@DOM(@Date-@DOM(@Date)) 

What this does is it goes to the last day of the month 2 months before the current one. And here is how it does that.

Let's use February 28th 2011 as the current date in this example. So let's substitute that in

2011/02/28 - @DOM(2011/02/28) - @DOM(2011/02/28 - @DOM(2011/02/28)) 

Now let's solve for @DOM() of today's date

2011/02/28 - 28 - @DOM(2011/02/28 - 28) 

2011/02/28 minus 28 is 2011/01/31 so let's substitute that in

2011/01/31 - @DOM(2011/01/31) 

Again let's solve for @DOM()

2011/01/31 - 31 

and we reach the result of

2010/12/31 

Okay now we know that for February 28th 2011 this piece of code below resolves to 2010/12/31

@Date-@DOM(@Date)-@DOM(@Date-@DOM(@Date)) 

So let's substitute that into the original piece of code.

]|@Year(2010/12/31)|/|@Right("00" + @Str(@Month(2010/12/31)), 2)|/..  

Make a bit more sense now?

-Ray