Help with creating a report-range of date fields

[Boo]

I'm new to the filemaker d/b and am not sure how i do this -

I need to create a report which would display all the records created between a particular date and also of a particular company (this is optional.. sometimes i will have to display all the records satisfying the date criteria)

Also I have to subsummarize according to the state the company belongs to.. like..

State

Company Name ID Sales Profit Creation Date

I am not sure as to how i should go about designing the report?

Can anyone help me with this?

Thank You.

[-Queue-]

Create a layout that contains two global date fields (gdStart and gdEnd) and a global text field (gCompany). Format gCompany to use a menu or list based on a company value list (if you wish). Then, in your report script,

Enter Browse Mode [ ]

Set Error Capture [On] 

Go to Layout [layout with globals]

Pause/Resume Script

If [not IsEmpty(gdStart) and not IsEmpty(gdEnd)]

  Enter Find Mode [ ]

  Go to Layout [one containing your date created field]

  Insert Calculated Result [date created, DateToText(gdStart) & "..." & DateToText(gdEnd)]

  If [not IsEmpty(gCompany)]

    Set Field [Company, gCompany]

  End If

  Perform Find [ ]

  If [status(CurrentFoundCount)]

    Go to Layout [your report]

    Sort [Restore, No dialog]

    Print Setup [Restore, No dialog]

    Enter Preview Mode [Pause]

    Print [ ]

  Else

    Show Message ["No transactions could be found with the criteria given."]

  End If

End If

Enter Browse Mode [ ]

Go to Layout [Original]

Be sure your Sort is by State, and set your report layout to use a subsummary by State. Put your State field in the subsummary part and the other fields in the body. Note this is for version 5.5. Your profile doesn't state what version you are using.

[PCGMKTG]

I have tried to follow this in order to create a similar report and get nowhere fast....

I created the global date (not that I even know what that is...) but the calculations you give just do not compute. I'm a complete beginner which is my main problem.

I have a contacts database where I have to pull out the date of the latest action with the accompanying action notes as a report on the status of the contact. (I am BDM doing cold calls, etc and need to note when I called and the action taken) The report is to give the boss who wants a list of what happened when within the last month.

Can you help at all?

[-Queue-]

A global is simply a field that maintains the same data throughout the database. It is used to push or pull data between tables or temporarily store data for a test or search criteria.

It doesn't sound as if you need the global date fields for your report, however. But when you say 'within the last month', do you mean during the last thirty days, four weeks, or previous month only? If it is during the last thirty days, then you would set up a find such as

Enter Find Mode [ ]

Set Field [ActionDate; Get(CurrentDate) - 30 & "..." & Get(CurrentDate)]

Perform Find [ ]

If [not Get(FoundCount)]

Show Custom Dialog ["No records could be found for the last month."]

Else

Go to Layout [ReportLayout]

End If

[PCGMKTG]

Eureka!! It worked. Thank you so much. (Now if only I understood what I have done....)

As a refinement of this, if I wanted to find records within a given month, how would I go about doing that?

Thanks

[Vaughan]

I'm surprised that the Set Field on ActionDate worked... usually it complains that the entry is not a valid date (it does not like the "..." string). For this reason an Insert Calculated Result is usually used.

Is ActionDate a date or text field?

[-Queue-]

In version 7, GetAsText and the Insert are not needed for date ranges, which is a very nice change.

It would depend on how you want to select the month, PC. Would you enter a date in the month or select the month name and year?

[PCGMKTG]

Vaughan - the ActionDate is a date field (that I have managed to link up with CalSnap as designed by Brian Dunning). Don't ask me why or how it worked. It just did, which is good enough for me! That's why I'm into using this forum....

Queue - if I could select all records that have been entered beyond a certain date, that would be great (i.e. if today's date is 10/08/2004, select all records from 01/07/2004 - BTW, I'm an Aussie so use dd/mm/yyyy format).

[-Queue-]

So, you would want all records from the beginning of the previous month to today's date? If so, then try Set Field [Date( Month(Get(CurrentDate)) - 1, 1, Year(Get(CurrentDate)) ) & "..." & Get(CurrentDate)].

[PCGMKTG]

Sorry, but that didn't work. Kept telling me there was an operator missing.

What I've done instead is [ActionDate; Date ( 1, 7, 2004 ) & "..." & Get ( CurrentDate )]

I'll just have to remember to change base date as needed. Unless you can figure out what is wrong with your string.

Thanks again....

[-Queue-]

Whoops! The commas should be semicolons in version 7.

[PCGMKTG]

Since I asked for (and received) this information, I've been playing with the database and now this doesn't work anymore....

I had to change my [ActionDate] to a text format as the Yankee-ness of Filemaker meant that I couldn't link CalSnap (dd/mm/yyyy) to a date field - it kept wanting to make the date mm/dd/yyyy. I tried making the date format customised to dd/mm/yyyy, but to no avail.

Now, I can't get my report to run. Is this because I've changed to a text field or because FMP just won't recognise the different date string?

Can anyone help?

[RalphL]

Is you date format in the operating system dd/mm/yyyy? If so make a clone of the offending file. Import data from the original into the cone. The date format should now be the same as the operating system.

[PCGMKTG]

Yep, settings are for dd/mm/yyyy. Tried the clone thing, but no go. It only returns dates that are mm/dd/yyyy (although they are actually dd/mm/yyyy). Any dates that do not fit in with the mm/dd/yyyy format are not shown (e.g. 29/12/2004).

I'm getting very frustrated.....

[Lee Smith]

Post a clone of your files. Be sure to strip out any confidential data that may be of concern.

[PCGMKTG]

Lee

I'm not sure that would be any good to you as my system settings are DD/MM/YYYY not MM/DD/YYYY. (I've been working on something else and just gotten back to this)

I know it is a problem with the date format but just can't figure out how to fix it. Here are the issues....

I use CalSnap to select a date which is entered to a certain field. This works fine if the field is defined as text. When I change to a date format, it tells me that "the value of the field must be a valid date in the range of years 1 to 4000 and should look like "12/25/2004" ". As a text field the 25/12/2004 date is entered just fine but not as a date field.

When I do a report that uses a date range - e.g. for the next 30 days - the report works, but the dates shown are all those that are on the first of the month rather than those of the first month - i.e. 01/02/2005, 01/03/2005, 01/06/2005. I want the dates for January which these are not....

Same for a date range for the previous month - all entries that start with 12 are shown so I gete 12 May, 12 Feb, etc. (12/05/2004, 12/02/2004)

I'm sure part of it is the "GetCurrentDate" command in the script as Filemaker seems to use MM/DD/YYYY as a default. Or is it that when I first started, my system was US and half way through I changed to Australian? The Help section of Filemaker tells me:

---

Get(CurrentDate) function

Description

Returns the current date according to the system calendar.

The format of the result varies based on the date format that was in use when the database file was created. In the United States, dates are generally in the format MM/DD/YYYY. You can change the date format in the Regional Settings Control Panel (Windows 2000), the Date and Time Control Panel (Windows XP), or the Date & Time System Preference (Mac OS).

If the result is displayed in a field, it is formatted according to the date format of the field in the current layout.

---

Given that I've said for the date to be displayed as entered, I'm totally stuck as to what to do. Can anyone tell me where I'm going wrong here?

Thanks so much.

[-Queue-]

Try leaving your field defined as text and create a temporary date field. Run a Replace Field Contents on the temp field with GetAsDate(textfield) as your calculation, making sure that the File -> File Options -> Text -> Data Entry option is set to 'Always use current system setting'. If that works, then run a Replace Field Contents on your text field with "" as the calculation. Then change it to a date field and run a Replace Field Contents with tempfield as your calculation.

Does that work?

[vad]

I am close the topic that you are discussing with.

In my database I have Field called "Certification" for each certification I have "BeginningDate" and "EndDate".

I want to make a report of Customers those have certifications between some random dates.

With the Code You guys provided I made something according to the code. Here is something that I couldn't understand.

I am using FileMaker Pro 7

Enter Browse Mode [ ]

Set Error Capture [On]

Go to Layout [layout with globals]

Pause/Resume Script

If [not IsEmpty(gdStart) and not IsEmpty(gdEnd)]

Enter Find Mode [ ]

Go to Layout [one containing your date created field] ---What is This?

Insert Calculated Result [date created, DateToText(gdStart) & "..." & DateToText(gdEnd)]---What is This?

If [not IsEmpty(gCompany)] ---I don't need company Info. So never Mind

Set Field [Company, gCompany]

End If

Perform Find [ ]

If [status(CurrentFoundCount)]----What does this do...........

Go to Layout [your report]

Sort [Restore, No dialog]

Print Setup [Restore, No dialog]

Enter Preview Mode [Pause]

Print [ ]

Else

Show Message ["No transactions could be found with the criteria given."]

End If

End If

Enter Browse Mode [ ]

Go to Layout [Original]

[-Queue-]

Going to a layout containing the field is not necessary in version 7, since Set Field (which does not require the field to be on the current layout) can be used instead of Insert Calculated Result (which does require it). So just put

Set Field [date created; gdStart & "..." & gdEnd]

in place of the Go to Layout and Insert steps.

Use Get(FoundCount) in 7 instead of Status(CurrentFoundCount). This returns true if at least one record is found, because any non-zero number is equivalent to 'true'. If zero records are found, then it is false and the If will jump to the Else step and continue.

[Oldfogey]

PCGMKTG,

Your format for the Date function is incorrect. Even though you have set the system date format to (the logical and civilised) ddmmyyyy, the Date function still wants the parameters set as (the crazy) Date(month; day; year).

[PCGMKTG]

Thanks Oldfogey

I have managed to get it to work - not sure how (but then I never do....) - which is great for what I need. The only problem is, as you note, the way FM7 likes the date format. When I use my script to add the date & action to an action log, it presents in mmddyyyy so I have to manually change it to ddmmyyyy. It's a minor irritation, but way better than not having the report function work for the date selection.