Calculating Total of "Cost" field

[Brit-Flick]

Hi guys, this is the last thing I need to do to finish my system, and I am completely stuck, even my coordinator has no idea.

Basically I have a layout which calculates how much each "Department" is spending, through summary fields and a currently basic script

The idea is that the user enters the department and the date (drop down calender) they want the results to be shown from.

So far the script works in working out the "Total Cost" when searching by Department alone, but when searching with the "Date Range" as well, unless there is a record with that matches that date exactly, no results are found.

So what I was wondering if there was a way for the script to detect the date range and show records that were created after that date?

The script I have so far is:

1. Go to layout - Departmental Spending

2. Enter Find Mode

3. Perform Find

4. Set Field(Courses Attended; Get ( FoundCount )

6. Go to layout - Add to Record

5. Go to layout - Departmental Spending

Any help would be fantastic :)

[mr_vodka]

how are you entering in the date range? You only have 1 date field...

Well you can script it for > 2/01/2008 and < 2/20/2008.

Enter Find Mode []

Set Field [ Department; "yourDept" ]

Set Field [ Date; ">2/01/2008" ]

Duplicate Record []

Set Field [ Date; "<2/20/2008" ]

Perform Find []

IF you have global fields for your search fields then you can do something like...

Enter Find Mode []

Set Field [ Department; gDept ]

Set Field [ Date; ">" & gDateStart ]

Duplicate Record []

Set Field [ Date; "<" & gDateEnd ]

Perform Find []

[Mikhail Edoshin]

The script with two requests “> 2/01/2008” and “< 2/20/2008” will actually find “ > 2/01/2008” OR “< 2/20/2008”. A correct way to search for date range is to use the “..” operator:

2/01/2008..2/20/2008

It's inclusive, i.e. end dates are also included. In modern versions if the date range is exactly a month or a year, it's possible to use a wildcard like:

2/*/2008; */*/2008.

[Brit-Flick]

I have a current date field which is a global field.

I attempted what you said, but the script just halted and when I press continue it just pressed me with the same results as before, I'm not sure I did it right so I've posted a screenshot below.

Still a bit of a novice really

Thanks for your help so far.

Edited February 21, 2008 by Guest

[mr_vodka]

:) Oops I dont know what I was thinking there or why I didnt post with date range ellipses... I cant for the life of me remember why I posted it that way... with or finds... The only thing I remember was that he had only one date field... Oh well, chalk one up for embarrassing.

[Brit-Flick]

Sorry I'm confused, how do I apply the elipse with what I've done in the script above?

I'm still quite a novice I'm afraid

Thanks

[mr_vodka]

My comment was directed at Mikhail's correction. I am still confused about your setup. In your orig post you seem to mention only one date field. What date range are you looking for?

[Brit-Flick]

I have two date fields, "Current Date" which is filled automatically by Get(CurrentDate). With a Date Range field. The idea is that a user can select a date from the past combined with the department and find records that have been created since that date.

I know I'm confusing myself, hope this clears things up somewhat

Thanks

[mr_vodka]

Ok that clears up things a lot. So basically all you need is:

Enter Find Mode []

Set Field [ Department; gDept ]

Set Field [ Date; "≥" & gDateStart ]

Perform Find []

This will find all records that are newer or equal to the date that you have selected as for the department that you selected. You do not need the second date field. Make sure that both the selection fields are global.

[Brit-Flick]

I'm not sure what I'm doing wrong here but the script is still pausing and then producing the wrong results when I click continue.

Sorry if I'm being awkward, this one seems so simple but is throwing me completely

[mr_vodka]

Well i cant see any of your images for some reason. Maybe due to firewall and where your images are stored.

uncheck the Pause for Enter Find Mode

[Brit-Flick]

Oh really?

Maybe a direct link will work:

I've unchecked it but it still shows all of the records under that department, if the date range was working it should only show one

[mr_vodka]

I cant get to photobucket... Anyway, are you sure that your search Date field is a global date?

[Brit-Flick]

Ah right again me being silly I was setting the date range in the wrong field, it works perfectly now, thank you so much for your help you've been so helpful :)