today's date in Filemaker query with Lasso

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

today's date in Filemaker query with Lasso

Keith M. Rettig
I am almost embarrassed to ask but given I couldn’t figure it out after 150 minutes of trying every conceivable method I could think of, so here I am.

I have an inline for querying Filemaker.
I want it to get all records with today’s date.
The field it is calling is a date field, which was extracted from XML submitted to the database.

Here is the inline.

[inline(
        -database=’theDatabaseofInterest’,
        -table=’tableSpecificallyforThisQuery’,
        ‘kioskofinterest’=$kioskID,
        ‘date_to_search’=$dateofToday,
        -sortfield=‘a_timestamp_field’,
        -sortorder='ascending’,
        -maxrecords=2000,
        -search
)]

Here is the variable I tried setting earlier in the page (it was one of many many attempts, but this one is the one I most expected to work as expected);

        [var( dateofToday =( date->format('MM/dd/y') ) ) ]

if I just put [$dateofToday], I correctly show    01/26/2018
If I use that in the query by hand, it works just fine.

The page returns with zero records.  There should be plenty of records returned.

Can someone guide me in the right direction to fix this?

Thank you,
Keith.

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: today's date in Filemaker query with Lasso

stevepiercy
[date->format('%D')]

The date format symbols are in the Lasso Language Guide.
http://www.lassosoft.com/lassoDocs/languageReference/obj/Date/Format?v=8
http://www.lassosoft.com/lassoDocs/languageReference/obj/Date/Format?v=9

--steve


On 1/26/18 at 8:44 PM, [hidden email] (Keith M. Rettig) pronounced:

>I am almost embarrassed to ask but given I couldn’t figure it
>out after 150 minutes of trying every conceivable method I
>could think of, so here I am.
>
>I have an inline for querying Filemaker.
>I want it to get all records with today’s date.
>The field it is calling is a date field, which was extracted
>from XML submitted to the database.
>
>Here is the inline.
>
>[inline( -database=’theDatabaseofInterest’,
>-table=’tableSpecificallyforThisQuery’,
>‘kioskofinterest’=$kioskID,
>‘date_to_search’=$dateofToday,
>-sortfield=‘a_timestamp_field’,
>-sortorder='ascending’,
>-maxrecords=2000,
>-search )]
>
>Here is the variable I tried setting earlier in the page (it
>was one of many many attempts, but this one is the one I most
>expected to work as expected);
>
>[var( dateofToday =( date->format('MM/dd/y') ) ) ]
>
>if I just put [$dateofToday], I correctly show    01/26/2018
>If I use that in the query by hand, it works just fine.
>
>The page returns with zero records.  There should be plenty of records returned.
>
>Can someone guide me in the right direction to fix this?
>
>Thank you,
>Keith.
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Eugene, OR
<[hidden email]>               <http://www.stevepiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: today's date in Filemaker query with Lasso

Carl Ketterling
In reply to this post by Keith M. Rettig
Have a look at the action_statement to see what is being sent to FileMaker.

Carl

> On Jan 26, 2018, at 10:44 PM, Keith M. Rettig <[hidden email]> wrote:
>
> I am almost embarrassed to ask but given I couldn’t figure it out after 150 minutes of trying every conceivable method I could think of, so here I am.
>
> I have an inline for querying Filemaker.
> I want it to get all records with today’s date.
> The field it is calling is a date field, which was extracted from XML submitted to the database.
>
> Here is the inline.
>
> [inline(
> -database=’theDatabaseofInterest’,
> -table=’tableSpecificallyforThisQuery’,
> ‘kioskofinterest’=$kioskID,
> ‘date_to_search’=$dateofToday,
> -sortfield=‘a_timestamp_field’,
> -sortorder='ascending’,
> -maxrecords=2000,
> -search
> )]
>
> Here is the variable I tried setting earlier in the page (it was one of many many attempts, but this one is the one I most expected to work as expected);
>
> [var( dateofToday =( date->format('MM/dd/y') ) ) ]
>
> if I just put [$dateofToday], I correctly show    01/26/2018
> If I use that in the query by hand, it works just fine.
>
> The page returns with zero records.  There should be plenty of records returned.
>
> Can someone guide me in the right direction to fix this?
>
> Thank you,
> Keith.
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: today's date in Filemaker query with Lasso

maxwellk2
In reply to this post by stevepiercy
I used to use this..

        [date->format('%m/%d/%Y')]

…which works, until I figured out the cleaner format Steve suggests...

        [date->format('%D')]

Also, when searching dates in FileMaker make sure to use the -Operator parameter. The default operator is "BW" which will fail for date fields with FMS12 and newer. For example...

[inline( -database='theDatabaseofInterest',
-table='tableSpecificallyforThisQuery',
'kioskofinterest'=$kioskID,
-Operator='EQ',
'date_to_search'=$dateofToday,
-sortfield='a_timestamp_field',
-sortorder='ascending',
-maxrecords=2000,
-search )]

With FileMaker you should pretty much always use -Operator='EQ' before all search field parameters, unless you actually need a different operator result...

        http://reference.lassosoft.com/LassoReference.LassoApp?192

The default "BW" is much slower than "EQ":


-Max



> On Jan 26, 2018, at 11:16 PM, Steve Piercy - Website Builder <[hidden email]> wrote:
>
> [date->format('%D')]
>
> The date format symbols are in the Lasso Language Guide.
> http://www.lassosoft.com/lassoDocs/languageReference/obj/Date/Format?v=8
> http://www.lassosoft.com/lassoDocs/languageReference/obj/Date/Format?v=9
>
> --steve
>
>
> On 1/26/18 at 8:44 PM, [hidden email] (Keith M. Rettig) pronounced:
>
>> I am almost embarrassed to ask but given I couldn’t figure it out after 150 minutes of trying every conceivable method I could think of, so here I am.
>>
>> I have an inline for querying Filemaker.
>> I want it to get all records with today’s date.
>> The field it is calling is a date field, which was extracted from XML submitted to the database.
>>
>> Here is the inline.
>>
>> [inline( -database=’theDatabaseofInterest’,
>> -table=’tableSpecificallyforThisQuery’,
>> ‘kioskofinterest’=$kioskID,
>> ‘date_to_search’=$dateofToday,
>> -sortfield=‘a_timestamp_field’,
>> -sortorder='ascending’,
>> -maxrecords=2000,
>> -search )]
>>
>> Here is the variable I tried setting earlier in the page (it was one of many many attempts, but this one is the one I most expected to work as expected);
>>
>> [var( dateofToday =( date->format('MM/dd/y') ) ) ]
>>
>> if I just put [$dateofToday], I correctly show    01/26/2018
>> If I use that in the query by hand, it works just fine.
>>
>> The page returns with zero records.  There should be plenty of records returned.
>>
>> Can someone guide me in the right direction to fix this?
>>
>> Thank you,
>> Keith.
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Eugene, OR
> <[hidden email]>               <http://www.stevepiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>





#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>