SageForum for UK versions of Sage 50- Help and support.

Hi,

I have this filter: SALES_ORDER.DESPATCH_DATE = REPORT.DATE

This report shows me sales orders that need to be despatched today...today being current date e.g. report date.

How do I write this:
SALES_ORDER.DESPATCH_DATE = REPORT.DATE + 1 day.

Idea is run this report at end of current day and it shows me sales orders that need to be despatched tomorrow?

Many Thanks
Gareth

You can't do a filter with an expression, you have to use a field on the report.

So add a field with the expression REPORT.DATE + 1 give is a name TARGET_DATE
You may not want it to print so turn on the suppress print attribute.

Then the filter SALES_ORDER.DESPATCH_DATE = TARGET_DATE should work.

If you fancy being extra clever...

How about adding a criteria to the report and adding the value given at run time, so you can run for any number of days ahead, eg on Friday you could run the report for Monday. Or just add a criteria for the DESPATCH_DATE and run it for any date you like.

Thats great thanks Bruce huge help. I did what you said with the target date. Yes I added a seperate report where the despatch date is a criteria to select any outbound jobs for any date specified.

But I dont understand the bit below sorry?:

How about adding a criteria to the report and adding the value given at run time, so you can run for any number of days ahead, eg on Friday you could run the report for Monday.

Many Thanks
Gareth

What I am suggesting is that rather than make it a fixed number of days relative to today, you make it a user definable number of day by passing the number of days to the report in a variable.

Hi, thanks for quick response. So you would specify the number of days, like show all orders (despatch date) in next 6 days and it would pull them through. Would you enter the number of days in a criteria box?

Sorry if this isnt what you mean.

You could do it either way, you could specify a date or a number of days.

Which would you prefer to give a date or to set a number of days?

Hi Bruce, thanks for reply. Both are useful, probably number of days would be better. Is there a way I can edit the tomorrow one so that if I run it on a friday it shows outbound jobs for Monday. Can I change my TARGET_DATE to REPORT DATE + 1 working day?

Many Thanks

Sort of... What you can do is use a criteria and a prompt when you run the report

Add a criteria such as the SALES_ORDER_CONTACT_NAME and change the prompt to "How many days in advance?"

Choose the preset option and set it to "Is Not Equal" to 1

Then edit it again and set it to Enabled. This sounds a bit strange, but by setting it as a preset first it will now prompt you with the "Is not equal to" and the Number "1" when you run it, which will be the default position. Obviously if you had a contact name of "1" this could cause problems, but you wouldn't would you so it is not an issue, all orders will pass the criteria and we can use that value to do whatever we want.

Now when you run the report you have a criteria field you can access CRITERIA.SALES_ORDER_CONTACT_NAME_FROM and it will contain the number you type in.

The only thing you need to deal with is the conversion of the string to a number using a function when you do the maths. StringtoInteger or Stringtofloat would work I think.

e.g.

SALES_ORDER.DESPATCH_DATE = REPORT.DATE + StringToInteger(CRITERIA.SALES_ORDER_CONTACT_NAME_FROM)

Cool I’ll have a go tomorrow when I’m back at work and have sage access .