Please Help

[Mehrdad]

We have photography studio which photograph students in schools. I have made a database which has a photo shoot date and every service we give to schools such as Rolodex cards and yearbook CD have a due date that must be back to schools on those dates. I need a report that can create a Due date and Overdue date so my staff can get on it right a way. when I made a report layout it asked me for the field I need and I pointed to (School name, YB CD and Rolodex Cards and so on) But when the reports were generated they have all the schools and all the services, I need an action or script that will bring only the Overdue schools and their Overdue items. How can I create such report.

[MoonShadow]

... I need a report that can create a Due date and Overdue ...

Reports can't create this for you. You need to create a calculation that tells you when something is overdue (depending upon the current date). Here's the simplest way to do it. Create a calculation (text) with:

Case(Status(CurrentDate) > DueDate, "Overdue")

Then search in this field for 'Overdue' before you generate your report.

Or create a calculation (number) with: Case(Status(CurrentDate) > DueDate, 1) and just search for the 1 in this Overdue calculation field.

[Mehrdad]

Thanks for your response, I do have fields that calculate the overdue dates however there are lots of them in my database. Is there anyway to find those overdue items without having to type overdue in each field?

[MoonShadow]

Is there anyway to find those overdue items without having to type overdue in each field?

You don't have to type Overdue in each field. The above example would have produced the word "Overdue" automatically, as it as a calculation (and you would perform a find on 'Overdue'). However, since you already have a Due Date field, you can just perform your find directly on it. Add this Find to the beginning of your report script and it will find all records whose Due Date is past (older than today) or overdue. On the Insert Calculated Result portion, specify your DueDate field:

Set Error Capture [On]

Allow User Abort [Of]

Enter Find Mode [ ] ... unclick Pause and unclick Restore

Insert Calculated Result [ ">" & DateToText( Status(CurrentDate)) ]

Perform Find [ Replace Found Set ] ... unclick Restore

If [ not Status(CurrentFoundCount) ]

Show Message [ Button: OK; Message: "No overdue records found" ]

Halt Script [ ]

End If

... from here, continue your regular script, which would include Go To Layout [ your report ], sorting and previewing if necessary.

[Mehrdad]

I am sorry, you lost me on (Insert calculate result) would you elaborate a bit more. I wish I could email you my file, so not to take anymore of your time and you could see what kind of mess I am in.

[MoonShadow]

Hmmm, you listed your skills as Intermediate. Maybe I made assumptions that you are familiar with the various script steps? In your Find script, the Insert Calculated Result script-step can be found part-way down under the 'Fields' category. If you double-click that, it will insert that line in your script. Be sure 'Select' is clicked.

Then click 'Go To Target Field' and find your DueDate field from the list/table above. Double-click your DueDate field to select it. Then click 'Specify' on Calculated Result and type exactly as indicated:

">" & DateToText( Status(CurrentDate))

[Mehrdad]

you are right if I don't know what is insert calculated result I must be a beginner. I did everything you mentioned but It always comes up with no overdue record found, although I have 2 over due records. I don't know if I mentioned before that in each record I have at least 10 to 15 field which have different due dates. Can I ask for different fields or is this calculation good for finding overdue date in one field in all records.

I do really appriciate your help and response.

thanks

[MoonShadow]

No. You didn't mention that and that changes your needs a bit. As you are discovering, this will continually give you grief. Any time you have many 'like' fields, it indicates you need a related file/table to hold that data. Otherwise, you will constantly be trying to get the job done. But the change is a small one and we will pull this off yet ...

In truth, either a record is overdue or it isn't. It doesn't matter if there are several DueDates within the record because you will want to find the record regardless of which field is overdue.

The only change is that, instead of using Insert Calculated Result[], we will use a Set Field. Create one calculation (number, unstored) with:

If(

Status(CurrentDate) < DueDate1 or

Status(CurrentDate) < DueDate2 or

Status(CurrentDate) < DueDate3 ... list all 12 fields. Last field would be:

Status(CurrentDate) < DueDate12, 1, "")

You will perform your find on this calculation and it will search for a 1. New line to use instead of Insert Calculated Result ...

Set Field [ this new calculation, 1 ]

That should do it for you.

[Mehrdad]

I am sorry Mine doesn't work, I think it might be where you mentioned to put

If [ not Status(CurrentFoundCount) ]

Should I choose the If from the menu and write not Status(CurrentFoundCount) ] in there or I am getting confusesd here because when I leave the IF there it gives me error when I take the IF out it look like this when I close the calculation

If [ "not Status(CurrentFoundCount)" ]

In any case it brings up noting but it doesn't say record not found either.

[MoonShadow]

I've attached a file which shows how this should look and work together.

date.zip

[Mehrdad]

I made a calculation field called REPORT and put this in calculation option

If( ID Due Date & Pims Due Date < (Today),1,0)

And made sure "do not calculate when the field is ematy is selected. However when I go to browse mode all Overdue and blank field are 1 and the rest 0 why is this happening although I ask FM not to calculate when field is empty?

Is this happenning because my due date are calculation from Shoot date?

[MoonShadow]

If( ID Due Date & Pims Due Date < (Today),1,0)

1) Do not use Today()

2) Do not unclick 'Do not Evaluate if all Referenced fields are empty

3) Use of the & is incorrect ... you need OR

Does my demo show any of that in it? No. Did you even try adjusting my calc to match your field names, as:

If(

Status(CurrentDate) < ID Due Date or

Status(CurrentDate) < Pims Due Date, 1, "")

Sorry to sound grumpy but it can be frustrating to try to help when I can't see through this computer to your file. Please either: 1) List your specific field names, data types and calculations (if they are calculations) which are relevant to your dates or 2) Post your file. Otherwise, I feel we are spinning in circles here.

[Mehrdad]

Please See the attached file to see what I am trying to do.

I really appreciate your time.

schoolListOld.zip

[-Queue-]

Change all of your due date fields to look like Case( field = "Yes", Shoot Date + somenumber ). "-" does not make any sense in a date field. Then change your Report field to

(not IsEmpty(Pims Due Date) and Status(CurrentDate) > Pims Due Date) or

(not IsEmpty(ID Due Date) and Status(CurrentDate) > ID Due Date) or

(not IsEmpty(Rolodex Book DD) and Status(CurrentDate) > Rolodex Book DD) or

(not IsEmpty(Rolodex Card DD) and Status(CurrentDate) > Rolodex Card DD) or

(not IsEmpty(Sticker DD) and Status(CurrentDate) > Sticker DD) or

(not IsEmpty(Verify) and Status(CurrentDate) > Verify) or

(not IsEmpty(WinSchool DD) and Status(CurrentDate) > WinSchool DD) or

(not IsEmpty(YB CD DD) and Status(CurrentDate) > YB CD DD)

and change your Overdue report script to Set Field [Report, 1] just before the Perform Find [ ].

[Mehrdad]

Thank you very Much, I don't know how to thank you.I am greatful

[MoonShadow]

I am very grateful also, JT.

I hope this helps you through this report process, Mehrdad.