solutions wanted A Find Script

[Jeremy G]

OKay I bet that you guys can tell me in 2 seconds on how to do this. I've tried for 2 days. I've ironed out all my other problems with complex scripts but I cannot get this one to work at all.

I have a report that looks at ALL projects (120+) that has been started and ended. It shows all these projects. Nice but not needed. These projects do start and end on various dates. Say i have project 918SC which started on the 9/18/09 and ended on the 2nd of this month (10/2/09). I would like for the report page to show all projects that ended at which ever month I select. How could I do this? I do have a start date and and end date. But When i look at the report for Sept. i just want to see all reports that have ended in that month.

I need to be able to select the month and see all projects that ended only in that month and not past or current month (if not selected on current month).

Thank you everybody

Jer

[Jeremy G]

I was thinking about this more and thought about how could I get a window to pop up when the 3day report button is selected. This window would give you a search range and then you enter this and the report will show automatically. any ideas on this?

[comment]

I would like for the report page to show all projects that ended at which ever month I select.

It is not clear how do you want to select the month - and the year, of course.

[Jeremy G]

Well I really could care less but maybe a pull down box for the month and same for year. Usually this would be done just at the end of the month or begining of the first month.

[comment]

Well then, suppose you have two global fields (both a Number) for the month and the year, make your script:

Go to Layout [ "Report" (Projects) ]

Enter Find Mode []

Set Field [ Projects::EndDate ; Date ( Table::gMonth ; 1 ; Table::gYear ) & ".." & Date ( Table::gMonth + 1 ; 0 ; Table::gYear ) ]

Perform Find []

...

---

Note: global fields can be in any table; replace "Table::" above with the name of your table.

[Jeremy G]

I tried but it gives me an error when saving the script in Set Field - An operator (e.g. +,-,*,...) is expected here.

What if I send you the file to look at and see what you think on this.

I am sorry about being such a noobie about this. I've learned quite abit in the past month.

Edited October 3, 2009 by Guest

[comment]

Make you sure you specify separately the target field (your EndDate field) and the calculated result. Only the formula:

Date ( Table::gMonth ; 1 ; Table::gYear ) & ".." & Date ( Table::gMonth + 1 ; 0 ; Table::gYear )

goes into the the calculated result part.

[Jeremy G]

okay this is starting to work but I tried a pull down tab from the tabel gMonth and gYear (I made) and it is not related.

[Jeremy G]

Okay i've tried several different ways I cannot get it to change and sort through the dates

[David McQueen]

Set up a calculated field that is of type text such that if your report ended on 01/28/09 that field reads January 2009. Have that field indexed for fast finds.

Then have two global fields, one for month and one for date, again both of type text and both with drop down lists.

Select the month and year in the globals.

Have your script go to a layout with the calculated field on it, enter find mode and then us "Insert Calculated Result (g_Month & " " & g_year" and perform the find.

You will have to put traps for not fidning any reports etc., but this will give you the found set you want for the report.

HTH

dave

[Jeremy G]

Okay I have been trying the way you told me to but I cannot get this to work at all. Any other suggestions please.

[David McQueen]

I'll make up a small demo tomorrow and post it for you.

[Jeremy G]

THANK YOU! this is about the last report I have to get done and its kicking my A$$ as you can see. I wish I knew more of the ends and outs of this program.

Jer

[David McQueen]

Hi Jeremy,

You now have a demo for this.

When you open it up, you will come to a list view that shows some transactions data, a date and the date calculation you will be searching on. The calculated field would not normally be shown.

When you click Go To Month Search, you will go to a form view.

Select a month and a year from the drop down lists. This populates two global fields.

Below is the calculated Month - Year field highlighted in yellow. This has to be on the same layout for the insert calculated result to work. Normally you would hide it by making it only 1 pixel wide and perhaps hiding it under an object.

When you click search, the program enters find mode, inserts the calculated result into the Month - Year calculated field and then performs the find.

I have the script trapping for a null find.

If I read your previous posts correctly, this is what you are trying to do prior to forming the report.

Have fun

Dave McQueen

demo_date_find.zip

[comment]

This has to be on the same layout for the insert calculated result to work. Normally you would hide it

Normally you would use Set Field[].

[David McQueen]

Normally you would use Set Field[].

I just tried it and you are right. Set field works for this one. I forget what I was trying to do, perhaps calculate a full date field, but "Insert Calculated REsult" was the only way I could do it.

Anyway, using either script step, the file will act a s a small model for setting similar things up for those who need it.