Professional Web Applications Themes

Find Date in Calculation field - FileMaker

FMP 5.0 WIN2000 I am having trouble writing a script to find records based on a date that is located in a calculation field. I am converting Text 20030715 to a Date 07/15/2003 in a Calculation Field (Date). I am writing a script to do a find for any record that has a date of <= Current Date - 7. I have tried using the Insert Calulated Result and the Set Field Commands along with the Date to Text Command. I get errors stating no valid critera in this request and messages about reverting the field. Enter Find Mode {} ...

  1. #1

    Default Find Date in Calculation field

    FMP 5.0 WIN2000

    I am having trouble writing a script to find records based on a date that is
    located in a calculation field.

    I am converting Text 20030715 to a Date 07/15/2003 in a Calculation
    Field (Date).
    I am writing a script to do a find for any record that has a date of <=
    Current Date - 7.
    I have tried using the Insert Calulated Result and the Set Field Commands
    along with the Date to Text Command.
    I get errors stating no valid critera in this request and messages about
    reverting the field.

    Enter Find Mode {}
    Insert Calculated Result "<=" & DateToText( Status( CurrentDate- 7 ))
    Perform Find

    Thanks
    Bigfoot1956



    Bigfoot1956 Guest

  2. #2

    Default Re: Find Date in Calculation field

    Hi Bigfoot1956

    Assuming the conversion of the text date to date format in the calculation
    field is correct, the only other problem that I see is in the calc for the
    Insert Calculated Result script step. You should subtract 7 outside the
    status function, not inside it:

    "<=" & DateToText( Status( CurrentDate- 7 ))

    Should read

    "<=" & DateToText( Status( CurrentDate) - 7 )

    Bridget Eley

    in article bgGSa.29074$647.6436fe05.atl2.webusenet.com, Bigfoot1956 at
    [email]wadewjSILVERbellsouth.net[/email] wrote on 21/7/03 10:22 AM:
    > FMP 5.0 WIN2000
    >
    > I am having trouble writing a script to find records based on a date that is
    > located in a calculation field.
    >
    > I am converting Text 20030715 to a Date 07/15/2003 in a Calculation
    > Field (Date).
    > I am writing a script to do a find for any record that has a date of <=
    > Current Date - 7.
    > I have tried using the Insert Calulated Result and the Set Field Commands
    > along with the Date to Text Command.
    > I get errors stating no valid critera in this request and messages about
    > reverting the field.
    >
    > Enter Find Mode {}
    > Insert Calculated Result "<=" & DateToText( Status( CurrentDate- 7 ))
    > Perform Find
    >
    > Thanks
    > Bigfoot1956
    >
    >
    >
    Bridget Eley Guest

  3. #3

    Default Re: Find Date in Calculation field

    Bigfoot1956 wrote:
    > I have tried using the Insert Calulated Result and the Set Field Commands
    > along with the Date to Text Command.
    > I get errors stating no valid critera in this request and messages about
    > reverting the field.
    >
    > Enter Find Mode {}
    > Insert Calculated Result "<=" & DateToText( Status( CurrentDate- 7 ))
    > Perform Find
    I assume you are really using "<=" & DateToText(Status(CurrentDate)-7)
    since your positioning of the '-7' is not valid.

    To make any 'Insert' step work, the field you are inserting to must be
    on the current layout at the time that step is run.

    If you are still having troubles, do a CTRL-R, which is 'refind'. This
    will put you back in find mode with the same criteria that the script
    tried using. This might help you see what the problem is.


    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Howard Schlossberg (818) 883-2846
    FM Pro Solutions Los Angeles, California
    Associate Member, FileMaker Solutions Alliance

    Howard Schlossberg Guest

  4. #4

    Default Re: Find Date in Calculation field

    The problem was -7 in the wrong place.

    Thanks
    Bigfoot1956




    "Howard Schlossberg" <howardantispahm.fmprosolutions.com> wrote in message
    news:vhmfd0kbhtp44fcorp.supernews.com...
    > Bigfoot1956 wrote:
    >
    > > I have tried using the Insert Calulated Result and the Set Field
    Commands
    > > along with the Date to Text Command.
    > > I get errors stating no valid critera in this request and messages about
    > > reverting the field.
    > >
    > > Enter Find Mode {}
    > > Insert Calculated Result "<=" & DateToText( Status( CurrentDate- 7 ))
    > > Perform Find
    >
    > I assume you are really using "<=" & DateToText(Status(CurrentDate)-7)
    > since your positioning of the '-7' is not valid.
    >
    > To make any 'Insert' step work, the field you are inserting to must be
    > on the current layout at the time that step is run.
    >
    > If you are still having troubles, do a CTRL-R, which is 'refind'. This
    > will put you back in find mode with the same criteria that the script
    > tried using. This might help you see what the problem is.
    >
    >
    > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    > Howard Schlossberg (818) 883-2846
    > FM Pro Solutions Los Angeles, California
    > Associate Member, FileMaker Solutions Alliance
    >


    Bigfoot1956 Guest

Similar Threads

  1. Date calculation in ActionScript
    By gregd in forum Coldfusion Flash Integration
    Replies: 2
    Last Post: February 25th, 01:25 AM
  2. Date calculation
    By pugs in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 1st, 12:36 PM
  3. Date Calculation & Formatting of Output
    By coryd218 in forum Macromedia ColdFusion
    Replies: 12
    Last Post: May 16th, 04:02 PM
  4. Indexed Calculation on Looked Up Field
    By Jonathan Perel in forum FileMaker
    Replies: 1
    Last Post: September 24th, 06:26 PM
  5. Calculation Field Help
    By Henry in forum FileMaker
    Replies: 2
    Last Post: August 4th, 10:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139