Finding data in a date range

[Kadin2048]

I think this is a fairly simple question, but I can't find the answer to it anywhere, either in the manual nor in the 'Missing Manual' book.

I need to make an Invoice-like layout, that summarizes a physician's appointments in a particular date range.

So basically, I need the user to be able to select a patient, start date, and end date, and find matching records (there is a table which contains the appointments).

I haven't figured out how to 'Find' records that lie between the two inputted dates. How do I do this?

Thanks in advance!

[K1200]

Here's a method I use:

http://fmforums.com/forum/showtopic.php?tid/178072/post/211851/hl//

(You'll need Start/End Dates instead of Yesterday/Today/Tomorrow, of course -- then just display your fields from the new TO.)

[Kadin2048]

Thanks for the idea. I'm downloading your example file in the other thread right now, but I wonder a little bit where you said that the approach would be 'cumbersome to apply to longer periods' (paraphrased). I need to choose periods that are a month long....so I'm hoping that choosing 30 day periods works as easily as one or two day ones.

[MartinaL]

Just wondering if you found an answer for this? If so could you possibly post it here?

[Ender]

I need to choose periods that are a month long....so I'm hoping that choosing 30 day periods works as easily as one or two day ones.

Yes, the same idea applies to any range. The general method is to use a range relationship, where the relationship is defined in this format:

Parent <=> Child =

Parent::ParentID = Child::ParentID

AND Parent::gRangeStartDate ≤ Child::Date

AND Parent::gRangeEndDate ≥ Child::Date

Additional criteria could be added to further filter the results.

[LaRetta]

If I understand your needs correctly, this sample file (attached) might provide various options. It shows filtering by three methods - 1) any date range, 2) month range non equijoin and month range using ONE calc in the child table, based upon =. I prefer method 3. Note that the calc fields which control the month range (2) are based upon User entering ANY date into a global date. Sample 3 also allows creation of a value list based upon field values (the Invoice indexed calc) so selection from popup is easier (and also conditional). Method 3 allows using that monthDate calc for sorting, finding and more ...

Ooops, Mike beat me this time! Well, I hope the file comes in handy anyway. I had forgotten to add the CustomoerID into the relationships so I changed the file.

LaRetta :wink2:

DateRange.zip

[Ender]

Nice sample, LaRetta. But you should really try to cut down on the caffeine. : I use methods 1 and 2 in my solutions, plus a few others.

[K1200]

Regarding "cumbersome to apply to longer periods":

I was referring to using the method to display multiple groups of date-related items ... and not to using the method to gather any one group of entries for a particular range of days. The span of dates for that one group can be whatever is needed.

[Kadin2048]

Hi Ender and LaRetta,

Thanks very much for the information.

I'm still running into trouble, though.

I thought maybe what I'm trying to do would make more sense, if I posted a screenshot of the table relationships and the database itself. I'm having a few problems implementing the approaches that you guys have suggested.

First, all the approaches I've seen thus far rely on portals. I am trying to make a printable invoice, where each record is on a separate line (I think FMP calls this a "List View"). I don't really understand how I can use a portal in this style of layout. I need something that will print and look like an invoice, with a header, body (repeating "body" section for each record), footer, etc.

The other thing is, I'm not sure which table should have the "StartDt" and "EndDt" fields. I've made them global variables, but in LaRetta's example, she had them in the equivalent of my DB's "Client" table. I don't think this will work in my situation, because I need to have an occurance of the Treatment table that shows all records, and doesn't select by date, for data entry.

Basically, I need to have a 'Treatment Data Entry' layout, where the user can see all the entries in the Treatment table, and then an Invoice view, where the user can enter a start and end date, and see (on the layout) only those entries in the Treatment table which lie between the two values.

In order to try and do this, I created a second instance of the Treatment table in the Relationships graph ("Treatment 2") and then joined it to the first instance of Treatment (that's where I did the greater than / less than relationship).

This is tough to explain but I think if you look at the relationships graph or at the DB itself, it'll make sense.

Unfortunately, this doesn't seem to work.

Any suggestions?

Treatment_Database.zip

Edited July 6, 2006 by Guest

[Ender]

Well, you could find those records through a traditional Find, but using the relationship is more versatile. In this case, by putting the global date range fields in the Client table, you can show a portal of all Treatments, and a portal by that date range. The Invoice can them be generated from there. I've modified your file to show this.

You may want to add a Status field or Billing date to the Treatments table and the relationships so a Client doesn't get double billed.

Treatment_Database.fp7.zip

[Kadin2048]

Wow ... yes that looks to be about what we are looking for.

I never really thought to put the portal and the date-selection fields on another view other than on the invoice view itself; how do you make the invoice screen limit itself just to what's visible in the portal like that?

I saw that you created a script to "Invoice this client" that seems to be the key to the whole thing. How would I set up another layout that would, say, invoice not by client, but by agency? (So, show all the treatments that are associated with a particular agency in a date range, grouped by client; instead of showing all the treatments in a particular date range for a specified client, grouped by agency and date.)

Would I need to put the portal on the Treatment table? Or could I keep it on the client data entry table, next to the ones you created?

Thanks again for all your help. I'm sorry to ask so many questions -- honestly I don't think I've ever had this much trouble with a piece of software in years, but I'm just at a loss as to where I'm supposed to figure this stuff out from. It seems like it wasn't covered in the manual at all, and barely touched on in the book I read.

-Kadin

Edited July 14, 2006 by Guest