Insert by calculated result into timestamp field

[hbadger30]

Dear all,

I'm relatively new to filemaker and I'm having some syntax problems trying to make a script.

I'm trying to perform a find on a range of records by a timestamp field. I have a custom dialogue which prompts the user to enter in the start date and end date and then I'm using a insert by calculated result script step to add times to both the start and end dates (I don't want the user to have to worry about entering a time) which are then placed into the timestamp field. The problem I'm having is that I'm getting an extra :* put in the timestamp field I'm performing the find on.

The insert calculated result script step is as follows:

(Sessions::External Session Start Date for performing find & " 00:01 ... " & Sessions::External Session End Date for performing find & " 23:59")

Instead of getting

24/12/2006 00:01 ... 29/12/2006 23.59

I'm getting

24/12/2006 00:01:* ... 29/12/2006 23.59:*

and so filemaker won't perform the find - can anyone help??

Many thanks,

Graham

[Raybaudi]

Hi

try:

(Sessions::External Session Start Date for performing find & " 00:00:01 ... " & Sessions::External Session End Date for performing find & " 23:59:59")

[hbadger30]

Yeah I tried that and it's still not working - now I'm getting:

09/11/2006 00:00:01.*...15/12/2006 23:59:59.*

Any more thoughts??

Thanks in advance.

Graham

[comment]

I don't know why you bother with the times - Filemaker will do it for you*. Just enter the search criteria as a date range:

YourTable::YourStartDate & ".." & YourTable::YourEndDate

BTW, it's preferable to use Set Field[] instead of Insert Calculated Result.

---

(*) Unless you have a specific reason to exclude records of the first second of start date, and the last second of end date.

Edited March 1, 2007 by Guest

[hbadger30]

Thanks comment for the advice on not having to bother with times but I'm still having problems. I tested finding the range in Find mode with just two dates seperated but three full stops and it works great but unfortunately the script I'm writing doesn't do it!!

Is there something obvious I'm missing here?

-Show All Records

-Go to Layout ["External Sessions" (External Sessions)]

-Enter Find Mode[]

-Show Custom Dialogue["Show expenditure by date range"; External Sessions::External Session Start Date for performing find;  External Sessions::External Session End Date for performing find]

-Set Field [External Sessions::External Session Start Date plus Time; External Sessions::External Session Start Date for performing find & "..." & External Sessions::External Session End Date for performing find]

-Perform Find[]

-Enter Browse Mode[]

The problem is that I'm getting * entered in place of the time in the timestamp field and then filemaker tells me there are no records found in the date range I requested - which I know is not correct. For example through the custom dialogue I enter the start date as 08/11/2006 and as the end date I enter 18/12/2006 and instead of getting

08/11/2006 ... 18/12/2006

in the timestamp field I'm getting:

08/11/2006 **:*...18/12/2006 *:*:*

and then filemaker tells me there are no found records. Is it the *: that are causing the problem or is it something with my code? Can anyone help?

[comment]

Yes, Filemaker automatically adds wildcards, meaning 'any time on this date'. That is a good thing. I don't know why it doesn't work for you - can you attach a sample file demonstrating the problem?

[hbadger30]

Ok here is a test file showing you what I mean.

Thanks for your help with this.

Graham

test.fp7.zip

[comment]

OK, to see the problem, insert a Pause/Resume Script[] step just before Perform Find. This will allow you to inspect the criteria your script produces.

You could clear the holding fields (start/end date for performing find) before performing the find, so that they are not included in the search criteria. But a better solution would be to change them to global storage. That way you also eliminate the overhead of 2 empty fields attached to EVERY record in the table.

[hbadger30]

Fantastic! Switching those two fields to global sorted it! I thought there wasn't much wrong with my code - but thank you so much for helping me find the solution!

Graham