Date ranges

[Alexnl]

Hello everyone this is the first time I am placing a post, I am having problems with a simple search between dates in one field, something which looks simple but not for me. (I have downloaded the version 8 actually using V.6) I have created a table which includes daily sales with the corresponding dates. The find search for sales of September is simple 01/09/2005...30/09/2005 how could I do this using a custom dialog with scripting where there is only one field with the dates? (Salesdate)

[Keith LaMarre]

I have a little script which i call "Get Valid Date Range" and it looks something like this:

There are 3 Global Values

gDate_1

gDate_2

gDate_Range

Show custom Dialog: "Please enter the Date Range (with entry values for gDate_1 and gDate_2

Set Field: Case(IsEmpty(gDate_2);

GetAstext(gGDate_1);

IsEmpty(gDate_1);

GetAsText(gDate 2);

GetAsText(gdate1)&".."&GetAsText(gdate1))

I can then enter find mode and set the date that I want to be the gDateRange - if the user has entered a value in only one field - it will find that date - otherwise it will find the date range

FileMaker handles the "valid date" part.

In your case -

Get Valid Date as shown above

Go To a layout that has the Sales Date

Enter find Mode -

Insert Calculated result: : gdaterange

good luck

[MarkVXII]

Wexing. It did not work. A FM8 help fie addresses the same problem(?). Help> (search)operators> About the types of relationships> About relationships that return a range of records.

I did build 2 databases:

1.DateRange, an empty database with the following properties:

StartDate (Date) 4-Digit, Allow Override

EndDate (Date) 4-Digit, Allow Override

2. Manufacturing database (with lots of fields, including: Date

StartDate and EndDate is connected to Date.

It did not work either.

Could you give a more detailed explanation of your method concerning of the properties of your mentioned fields?

Appreciated,

Edited October 23, 2005 by Guest

[comment]

I suspect you are mixing two separate issues here. Filemaker has two methods to isolate records that meet certain criteria:

1. You FIND the records, by going into Find mode in the desired table (i.e. in a layout showing records from an occurence of the desired table), entering the criteria, and performing a Find. This results in a found set of records. This found set is user- and window-specific, and cannot be used from another table.

2. You show the records in a PORTAL based on a relationship. Here, the criteria are defined in the definition of the relationship.

It seems you have tried the second method. The example given in Help (About relationships that return a range of records) seems pretty explicit. Did you place a portal based on your relationship on the layout in DateRange?

[MarkVXII]

Thank you to even bothering to answer. I am totally new to FM and it is kind of embarrassing to ask anyone questions when I myself do not know exactly what I am asking.

I would like to make a client DB. It has records with fields Date(a date stamp), $Amount 1, $Amount2 among others like: Surname,Firstname,DOB,Age,Address,Location,SIN,Note)

The DB is filled daily with ~20 records.

How to show daily, weekly, monthly, yearly stats?

Daily is done.

Weekly: find date range e.g 10/11..17/2005 (using Script and drop-down calendar to entry StartDay and EndDay ). The result should show:

Monday, $Amount 1, $Amount2

Tuesday, $Amount 1, $Amount2

...

...

Sub-total $Amount1, $Amount2

Total: $Amount1+$Amount2

Thank you,

[comment]

I am not sure what you're asking:

Monday, $Amount 1, $Amount2

Tuesday, $Amount 1, $Amount2

does not seem like a weekly stat - more like 7 daily stats, side-by-side. You will need 7 separate relationships for such a display, and 7 key fields to hold the dates of the selected week.

Can you explain a bit more what is this tracking? Especially, the business with the two amounts is confusing. Instictively, I would say keep one table of people (Clients) and another table showing their payments, but I would need to know more to be sure.

As an aside, do not name your fields with names that begin with $ - that is reserved for variables, and can really confuse the application.

[MarkVXII]

Thanks a lot.

The DB's Entry Form Fields: ID, Date, Last Name, First Name, DOB, Age, Sex,Address, Phone, Health Card Number, Version Code, Sum //to be payed later by the government//, Cash, Location, Note, Next Visit.

-------------------------------------------------

e.g. Daily Stats Layout shows:

Tuesday, October 11, 2005 at London (Location)

Header

-------------------------------------------------

Sum Cash

$40 $60

$30 $60

..

..

----------

$80+ $120=$200

The Weekly Stats should show:

Monday, Oct 10, 2005 - Sunday, Oct 16,2005

Header

-------------------------------------------

Day Sum Cash

Monday $40 $10

Tuesday $10 $20

...

...

------------------

$XXX + $XXX = $XXX

This DB main purpose to track daily, weekly, monthly, yearly income... beside other very ambitious pretension which most probably never will be realized: like receive data from magnetic card reader into the DB's proper fields, sending health card data by modem to the goverment.

But it is fun to try.

Appreciate your help.

[comment]

Doing this with relationships is possible, but quite complex. I could do a demo file for you, but I am not sure you would understand it at this point (no offense). FWIW, we did something similar in this thread.

I would suggest you try Filemaker's reporting feature. You need to add some summary fields to the Visits table (I still think you should have at least 2 tables - Patients and Visits).

To report on a single day, you would find the Visits records for that day, and go to a DailyReport layout, where the summary fields are placed in the footer.

For a weekly report with daily subsummaries, study the attached file.

WeeklyReport.fp7.zip

[MarkVXII]

Hi,

Thanks again.

Could you tell me how to attach files to the mail? I believe some pictures may explain better what I am trying to do in this DB.

The stats are not about patients but income.

DailyReport is DONE following your instruction.

In the WeeklyReport to find the date range it maybe possible to use Keith LaMarre's idea: gStartDate, gEndDate, gDateRange (gDateRange definition is -Calculation-Unstored,

=GetAsText(gStartDate)&".."&GetAsText(gEndDate)

-------------------------------------

Show All Records

Go to Layout[incomeStats]

Enter Find Mode[Pause] //Used Drop-down Calendar to enter gStartDate, gEndDate.//

Set Field [incomeStats::Date=IncomeStats::gDateRange]

Perform Find[]

Go to Layout[Weekly Stats]

Enter Preview Mode[Pause]

Go to Layout[incomeStats]

Enter Browse Mode[]

-------------------------------------------------

The whole WeekReport is kind of buggy. It lists in 'preview'the records all right, except the days are separate instead of accumulated, in this fashion:

Monday $4 $5 = $9

Monday $5 $10 = $15

Tuesday $2 $2 = $4

Tuesday $1 $2 = $3

...

...

$XX+$XX=$XXX

-----------------------------------------------

[comment]

Have you seen my file?

In order to see the accumulated sum, change the definition of sTotal to running total, and put it in the subsummary part instead of (or in addition to) cTotal.

The script above should work, but:

1. I'd thought finding the week's start and end (from any gDate) would be easier if done by the computer. But it can work your way, too - and you can enter any report period, not just a week.

2. The calculation field gDateRange is redundant - you can set the searched field directly to the calculation. Also, Show All Records before a Find serves no purpose. Finally, you MUST sort records for a subsummary report to work.

I would also create another layout to set up the report and include a Cancel button there.

Go to Layout [ "Set Up Report" (Visits) ]

Pause/Resume Script [ Indefinitely ]

Go to Layout [ "Report" (Visits) ]

Enter Find Mode [ ]

Set Field [ Visits::Date; Visits::gStartDate & ".." & Visits::gEndDate ]

Perform Find [ ]

Sort Records

Enter Preview Mode

Pause/Resume Script [ Indefinitely ]

Enter Browse Mode

Show All Records

Go to Layout [ original layout ]

To attach a file, use Reply (not Quick Reply). You'll find a "Manage Files" button there.