report to show "orders coming due"

[Baylah]

I was wondering if anyone has a technique they can share for presenting a report on Orders coming due.

If I have a list of orders that looks like this:

Order#....Due

1234......4/25/07

4321......4/23/07

5678......4/20/07

7890......4/21/07

4103......4/30/07

7654......4/19/07

Is there a way I get the report to look anything like:

Orders Due Today

......7654......4/19/07

Orders Due Tomorrow

......5678......4/20/07

Orders Due Next week

......4321......4/23/07

......1234......4/25/07

Orders Due the following week

......4103......4/30/07

Ultimately I would like to write a script that will dynamically build this report based on the current date.

I can easily script a report to list open orders and sort by date, but I don't know how to do what I am describing above. Does any have any suggestions or samples they wuld be willing to share?

Thaanks,

Steve

[comment]

You need an unstored calculation field that will return the text "Today" if Due field = Get (CurrentDate), "Tommorow" if Due field = Get (CurrentDate) + 1, and so on (a simple Case() statement).

Then create a leading subsummary part, when sorted by this calc field. You will also need a custom value list to help with correct sorting of the returned text values.

[mr_vodka]

Well I was creating this when I saw that comment already posted the solution. Since I have done it already I might as well post the sample file.

group_days.zip

[Baylah]

Well Mr. Vodka,

Looks like you bailed my sorry butt out again!

Thanks,

This is perfect!

this is what I was talking about in the post I sent you. I think I should have known how to program this because I fully understand everything that is going on here, but I don't "see the solution" in my head.

I'm working on it though and the help and support of kind people like you and others on this list is invaluable.

Is the list still taking donations...I used to subscribe, then I stopped...I really should start again.

Steve

[Baylah]

Hi All,

This script is perfect in all aspects but one, it does not update when it is run the next day. I have tried several methods to fis this but I am coming up blank.

IF someone has a chance to look at the script 2 posts up and can offer a suggestion for how I can have this update when it is run on different days I would really appreciate the help.

Thanks,

Steve

[Baylah]

I MIGHT have solved this simply by changing the calculation fom stored to unstored.

At least I think that may work. what I really need to happen is for the calculation in the "cspread" field to evaluate at run time. If that happens then everything else works.

I can't tell though until tomorrow for sure without messing with the system dates on a computer where that can cause major problems.

If my logic or understanding of how this "may" work is faulty will someone let me know.

Thanks,

Steve

[comment]

1. Change the definition of cSpread to:

Let (

d = Get ( CurrentDate )

;

Case ( OrderDate ≥ d ;

Case (

OrderDate = d; "Today";

OrderDate = d + 1; "Tommorow";

OrderDate ≤ d + 7; "Next week";

OrderDate ≤ d + 14; "Following week"

)

)

)

Make sure it is unstored.

2. Change the script to:

Go to Layout [ “report” ] 

Perform Find [ Specified Find Requests: Find Records; Criteria: orders::cSpread: “*” ] [ Restore ] 



# UNCHANGED FROM THIS POINT

Sort Records [ Specified Sort Order: orders::cSpread; based on value list: “Report Order” orders::OrderDate; ascending 

orders::OrderNumber; ascending ] 

[ Restore; No dialog ] 

Enter Preview Mode 

[ Pause ] 

Enter Browse Mode 

Go to Layout [ original layout ] 

You might want to add some error handling in case no records are found.

[Baylah]

Hi Comment:

I tried what you provided with a couple minor changes I had to make based on my clients request but it did not work. Will you please see below and tell me what I might have done wrong?

First is the calculation for "cspread" and then the script.

I did not build any error checking into the script because this customer never has due dates fall on the weekends based on some previous coding. I also added a little routine to return the user to the order they were looking at before running the script. I'm sure those parts will be obvious to you.

I get a "There are no records that match this search criteria" error as soon as I run the script.

[cspread]

Let ( d = Get ( CurrentDate )

;

Case ( OrderDue >/= d;

Case(

OrderDue = d;"Today";

OrderDue = d + 1; "Tommorow";

OrderDue = d + 2; "In 2 Days";

OrderDue = d + 3; "In 3 Days";

OrderDue = d + 4; "In 4 Days";

OrderDue = d + 5; "In 5 Days";

OrderDue = d + 6; "In 6 Days";

OrderDue = d + 7; "In 7 Days";

OrderDue > d + 8; "More Than 7 Days";

)

)

)

[script]

Set Field [salesOrder::gTempOrderNum; SalesOrder::UniqueID]

Go to Layout ["OrdersDue" (SalesOrder)]

Perform Find [ Specified find requests: Find Records; Criteria: SalesOrder::cspread:"*"] [Restore]

Sort Records [specified Sort Order: SalesOrder::cspread; Based on value list: "Report Order" SalesOrder::OrderDue; Ascending]

[restore; No Dialog]

Enter Preview Mode

[pause]

Enter Find Mode

Set Field [salesOrder::UniqueID;SalesOrder::gtempOrdNum]

Perform Find []

Enter Browse Mode

Go to Layout ["SalesOrderHome"]

Set Window Title [Current Window; New Title: "AS - Sales Order Home"]

Adjust Window

[Maximaze]

Thank you for any help you might be able to offer.

Steve

[comment]

In view of your changed requirements, you can make cSpread =

Let (

d = Get ( CurrentDate )

;

Case(

OrderDue = d ; "Today" ;

OrderDue = d + 1 ; "Tomorrow" ;

OrderDue = d + 2 ; "In 2 Days" ;

OrderDue = d + 3 ; "In 3 Days" ;

OrderDue = d + 4 ; "In 4 Days" ;

OrderDue = d + 5 ; "In 5 Days" ;

OrderDue = d + 6 ; "In 6 Days" ;

OrderDue = d + 7 ; "In 7 Days" ;

OrderDue > d + 7 ; "More Than 7 Days" ;

)

)

Also, your script can find the records where OrderDue ≥ Get ( CurrentDate ), instead of searching the unstored calculation.

P.S.

OrderDue >/= d is not a valid expression (and neither is Tommorow, I think).

[Baylah]

Thanks Comment...That did the trick!

Steve