Date Ranges

[jbullydawg]

I have a transaction layout with 10 independent transaction sections. I want to be able to provide an invoice with transactions completed in a certain date range but with 10 different transaction dates I'm not sure how to do that.

Is there a way to do a search of 10 different date fields at once for a particular range (ex. 12/1/2006...12/31/2006) ? Silly question, I'm sure but if there's a way to do it I would be greatly appreciative.

Thanks

[Genx]

10 different date fields at once? Are they all "And" criteria or "Or" Criteria?

[CobaltSky]

Is there a way to do a search of 10 different date fields at once for a particular range (ex. 12/1/2006...12/31/2006) ?

jbullydawg,

Assuming what are saying is that you require a range which fully encompasses the ten available date values - and assuming that the issue arises because there is no recurring sequential pattern to the ten dates, you could solve the problem with a formula along the lines of:

Let([

Mn = Min( Date1; Date2; Date3; Date4; Date5; Date6; Date7; Date8; Date9; Date10 );

Mx = Max( Date1; Date2; Date3; Date4; Date5; Date6; Date7; Date8; Date9; Date10 );

d1= GetAsText( GetAsDate( Mn ));

d2= GetAsText( GetAsDate( Mx ))];

d1 & "..." & d2

)

However you should note that such a formula will only evaluate correctly in Browse mode (but not in find mode), unless the referenced values are all in variables or global fields. So you may need to transfer the result to a variable or global field before entering find mode... Just a thought. :wink2:

[jbullydawg]

Yes, 10 date fields at once. If you can picture a billing statement you might see 10 differing transactions on 10 different lines. Each of those lines has a transaction date. I want to be able to go into Find Mode, throw in a date range to search for, and have it search all 10 date fields at once.

I realize repeating fields would have done this no problem but the way these folks wanted it designed it wasn't plausible to do it that way.

This is a stumper for me.

[Lee Smith]

Hi jbullydawg,

Never apologize for not using Repeating Fields. :

I'm not understanding your file structure, but if they all have the same field "transaction date", these items sound like there could be line items and the transactions put into a related file.

HTH

Lee

[jbullydawg]

Lee,

I have ten different fields that capture inputted dates.

TransDate1, TransDate2, TransDate3....TransDate10.

[mr_vodka]

As Lee stated, these would be better served by putting them in a seperate related table.

There should be no need for TransDate1, TransDate2, TransDate3....TransDate10. You can have it as multiple related records with one TransDate field.

[CobaltSky]

I want to be able to go into Find Mode, throw in a date range to search for, and have it search all 10 date fields at once.

Notwithstanding the thoughtful comments about structure from from Lee and mr_vodka, there are various ways you could achieve the effect you're describing. IOW a number of things - including searches of this kind - may be easier with a different structure, but they are not impossible.

For example, creating a multi-request find will allow searching simultaneously on ten (or more) fields. Whilst this may be rather tedious to set up manually, the process can be scripted in such a way as to make it no more onerous than a conventional find. Here is one way to do it:

Create a pair of global text fields to hold the start and end dates for your search. Lets call them gStart and gEnd. Then create a script along the lines of:

Show Custom Dialog ["Automated Seach"; "Enter the start and end dates for your search:"; YourTable::gStart; YourTable::gEnd]

If [Get(LastMessageChoice) = 2]

Exit Script [ ]

End If

Enter Find Mode [ ]

Set Variable [$Criteria; Value: YourTable::gStart & ".." & YourTable::gEnd]

Set Field [YourTable::DateField01; $Criteria]

New Record/Request

Set Field [YourTable::DateField02; $Criteria]

New Record/Request

Set Field [YourTable::DateField03; $Criteria]

New Record/Request

Set Field [YourTable::DateField04; $Criteria]

New Record/Request

Set Field [YourTable::DateField05; $Criteria]

New Record/Request

Set Field [YourTable::DateField06; $Criteria]

New Record/Request

Set Field [YourTable::DateField07; $Criteria]

New Record/Request

Set Field [YourTable::DateField08; $Criteria]

New Record/Request

Set Field [YourTable::DateField09; $Criteria]

New Record/Request

Set Field [YourTable::DateField10; $Criteria]

Set Error Capture [On]

Perform Find [ ]

If [Get(LastError)]

Show Custom Dialog ["Your search did not produce any results"]

Enter Browse Mode [ ]

Show All Records

End If

When the users run this script they will be prompted to enter the start and end dates for their ten-field search. Once they enter two dates and click OK, the rest of the process will be performed automatically, locating records (if any) which haves dates falling within the entered range in any of the ten date fields. :wink2:

[jbullydawg]

CobaltSky,

I'm just now getting around to testing your suggestion for search ten differing date fields using a date range all at one time.

You provided the following script example:

[color:blue]Show Custom Dialog ["Automated Seach"; "Enter the start and end dates for your search:"; YourTable::gStart; YourTable::gEnd]

If [Get(LastMessageChoice) = 2]

Exit Script [ ]

End If

Enter Find Mode [ ]

Set Variable [$Criteria; Value: YourTable::gStart & ".." & YourTable::gEnd]

Set Field [YourTable::DateField01; $Criteria]

New Record/Request

Set Field [YourTable::DateField02; $Criteria]

New Record/Request

Set Field [YourTable::DateField03; $Criteria]

New Record/Request

Set Field [YourTable::DateField04; $Criteria]

New Record/Request

Set Field [YourTable::DateField05; $Criteria]

New Record/Request

Set Field [YourTable::DateField06; $Criteria]

New Record/Request

Set Field [YourTable::DateField07; $Criteria]

New Record/Request

Set Field [YourTable::DateField08; $Criteria]

New Record/Request

Set Field [YourTable::DateField09; $Criteria]

New Record/Request

Set Field [YourTable::DateField10; $Criteria]

Set Error Capture [On]

Perform Find [ ]

If [Get(LastError)]

Show Custom Dialog ["Your search did not produce any results"]

Enter Browse Mode [ ]

Show All Records

End If

I have two questions pertaining to the above code.

First, I am working on FM 7 Pro and my ScriptMaker does not have a function called Set Variable. Is this function native to FM 8 and, if so, can I substitute that for something native to FM 7?

Second, on all of the Set Field lines there is a '$Criteria' line at the end. Where does this stem from and is this, also, a FM 7 issue?

Thanks to anyone that can help this scripting newbie.

Jason

[CobaltSky]

Hi Jason,

I'm afraid I did not previously note that you are using FileMaker 7.

You're correct that variables did not become available until FileMaker 8 - and that accounts not only for the presence of the Set Variable[ ] script step, but also the references to "$Criteria" elsewhere in the code I suggested.

Something simlar could be achieved in FileMaker 7 with the use of a global text field. Eg along the lines of:

Show Custom Dialog ["Automated Seach"; "Enter the start and end dates for your search:"; YourTable::gStart; YourTable::gEnd]

If [Get(LastMessageChoice) = 2]

Exit Script [ ]

End If

Enter Find Mode [ ]

Set Field [YourTable::gTempText; Value: YourTable::gStart & ".." & YourTable::gEnd]

Set Field [YourTable::DateField01; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField02; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField03; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField04; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField05; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField06; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField07; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField08; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField09; YourTable::gTempText]

New Record/Request

Set Field [YourTable::DateField10; YourTable::gTempText]

Set Error Capture [On]

Perform Find [ ]

If [Get(LastError)]

Show Custom Dialog ["Your search did not produce any results"]

Enter Browse Mode [ ]

Show All Records

End If

See how you go with that.

[jbullydawg]

Beautiful!! That worked like a charm.

Thank you CobaltSky!!

After looking through it understand now how you went about this and why the script works except for the 'New record' portions....that's a puzzle.

Anyway, thanks again. We can create invoices for our international students like we have wanted to do for a while.

[CobaltSky]

...understand now how you went about this and why the script works except for the 'New record' portions....that's a puzzle.

In browse mode, the New Record/Request command creates a new record. No surprises there.

In find mode, however the New Record/Request command creates an additional find request. Thus multiple find requests can be stacked up and executed simultaneously - giving you access to more complex serch capabilities than would otherwise be available. :wink2: