Test for a used value using a global variable

[Rich]

Howdy, all:

Once again, I know I'm close but the syntax is getting the better of me.

My goal is to use a global variable ($$_ProposedDate) to test against a date field (Date__lxd) in the same table (RESERVATION_PARENT) to see if a date has already been used. Here's what I have so far:

Let (

TheDate = $$_ProposedDate ;

ExecuteSQL ( "

SELECT COUNT ( * )

FROM RESERVATON_PARENT

WHERE   Date__lxd = ?

" ; "" ; "" ; TheDate

)

)

If the count > 0, then I know the date has been used. Obviously, I'm missing something, but what? 

Along this calculation, I could swear there's a native FileMaker Pro way of doing the same thing--I hazily remember that by making the date field into a value list, one could "beat" a value against it to see if it's there or not but for the life of me, I can't remember how to do that. (Using ValueCount, maybe?)

Anyway, it's good thing I have a bottle of Tylenol handy. TIA for your help!

(On a completely different note--what's going on with FileMaker's site? None of the links online to the Community section work anymore.)

Edited May 9, 2019 by Rich

[bcooney]

couldn't you simply do a find? what's not working with the ESQL call? Doesn't seem like you're returning the result of ESQL.

(the community recently migrated to a new platform and the migration didn't preserve links. They're hoping to restore them).

[Rich]

I don't know--I get the dreaded question mark as a result.

I suppose I could just perform a Find; part of this is hoping to learn e-SQL skills but I'm beginning to think that it may be beyond my ability.* (I tend to make things waaaay more complicated than they need to be. ...and thanks for the FM website update.)

*"Check this out, Dad--I aced the I.Q. test by scoring a 100!"

 

Edited May 9, 2019 by Rich

[bcooney]

Break it down. Start with "SELECT * FROM myTable" . Hard-code the date using a value that you know exists. You can always wrap the result in ValueCount ( ).

 

RESERVATON_PARENT - misspelled?

 

[comment]

44 minutes ago, Rich said:

I hazily remember that by making the date field into a value list, one could "beat" a value against it to see if it's there or not

Yes, it's possible - but I would be reluctant to go that way, because the comparison is done in text domain. If your date format is MM/DD/YYYY, then comparing an entry of "5/5/2019" (which is a valid entry into a date field that Filemaker will accept) to the result of Date ( 5 ; 5 ; 2019 ) will fail, because "5/5/2019" is not equal to "05/05/2019".  You may protect yourself (at least as far as user data entry is concerned) by auto-entering GetAsDate ( Self ) into the date field.

In any case, the calculation would be =

not IsEmpty ( FilterValues ( $$_ProposedDate ; ValueListItems ( "" ; "YourValueList" ) ) )

Another option is to use a relationship matching a global date field against your date field, and test for the existence of related records.

All that said, there's no reason why ExecuteSQL() couldn't work just as well. Hopefully, you are using it in a script, not in a calculation field. (And if so, a script variable should  be sufficient.)

 

[Rich]

Thanks for all the good info, guys.

*blush* The typo with RESERVATON--good catch!  Maybe it's time I update my trifocal prescription. (Getting old sucks!)

I like the related matching global field suggestion--it's simple to be sure. (Thanks for reminding me about using SQL in scripts and not hard-coded in fields--I know the latter slows things down a lot.)

Here's my logic for not using a Find--it would take fewer resources to "peek around the corner" to "see" a record/value than to go through the gyrations of finding, parsing, restoring, etc. Then again, I may be fooling myself since it's a small database with maybe 500 records at most (with only a handful of data fields), so I may be projecting a non-existant performance hit.

 

 

Edited May 9, 2019 by Rich