SQL Help

["... you mean these fans?"]

Hi,

 

I need some help.  I am wanting to validate a user input on one table to a value in another table without any relationship.

 

I was hoping I could write an Execute SQL for the following.

 

Field: "SearchPassword"  in ( Table A )

 

Field: "Admin Password" in ( Table B )  If match continue, if not EXIT.

 

Or I'm on ( Table C ) and I would like to enter a "Password" in a Global Field ( Table A ) and check it to the "Admin Password" also in ( Table A )  If match continue, if not EXIT.

 

All I want to do is provide a field on any table, check that field to the "Admin Password" field in my Preference Table without have to build relationship on each table.

 

I'm very new to SQL and I'm trying to understand it and this example would provide me with a very nice Bicycle as a point of reference in my mind so I can reference as I start my learning SQL

 

Any assistance with the about password check I would be grateful.

 

Thank you.

 

Tom :-)

[email protected]

 

 

 

[Lee Smith]

Automatic message

This topic has been moved from "External Data Sources" to "FQL or FileMaker Query Language".

[Wim Decorte]

The global field that the user types into is available from any context without a relationship.  The only SQL that you would need then is a simple SELECT

 

SELECT AdminPassword FROM TableB

 

Provided that TableB has only 1 record.  Otherwise you need to specify what record to target:

 

SELECT AdminPassword FROM TableB WHERE someField = something

["... you mean these fans?"]

Hi Wim,

 

Having trouble, the result throws a "?".  Here is the calculation ...

 

 

ExecuteSQL ( 

 

"SELECT Preference::Administrative Password

 

FROM Preference

 

WHERE Preference::Search Text  =  Preference::Administrative Password"

 

 ; "" ; "" )

 

The "Search Text" is my global fields and the Administrative Password is the "One" record fields with the data.

 

Thank you.

 

Tom

[Priyabrata]

Hi,

 

Now try executing this SQL command:

 

ExecuteSQL ( 

 

"SELECT Administrative Password

 

FROM Preference

 

WHERE Search Text  = ?" 

 

 ; "" ; "";Preference::Administrative Password)

 

 

 

Regards

Priyabrata Sahoo (Priya)

Mindfire Solutions, India.

["... you mean these fans?"]

Hi,

 

I went ahead and enter the "Select" statement as instructed but it still throws a "?" hmm ...

 

Tom

[eos]

SQL doesn't understand field and table references with spaces (blanks) in them; put your field names in quotes like so:

 

SELECT "Administrative Password" … WHERE "Search Text" = …

 

or better yet, try to avoid using spaces in field names. See Wim's example code above, where he uses sample field names without blanks. It's one potential source of errors less to worry about.

["... you mean these fans?"]

Hi Eos,

 

Thank you.  I went ahead and removed my spaces as instructed and now the statement throws a error code "5" when I enter nothing, the wrong passcode or the correct passcode.  Hmm ...

 

Here is the corrected ExecuteSQL ...

 

 

ExecuteSQL ( 

 

"SELECT AdministrativePassword

 

FROM Preference

 

WHERE SearchText  = ?" 

 

 ; "" ; ""; Preference::AdministrativePassword )

 

Tom :-)

["... you mean these fans?"]

Eos,

 

I don't get the "?" now, does that mean that the SQL statement is understood ... yes / no ?

 

Filemaker throws an "Error" code and I can look for that.  But I am getting an "Error" code even when the correct password is entered.

 

Hmm ...

 

Anyway, any assistance here I would be grateful ..

 

Tom :-)

[eos]

Just to be sure: you changed the formula AND the field names …?

["... you mean these fans?"]

Hi Eos,

 

I've double checked by cut and past just to make sure I didn't inadvertently entered a typo ... and I also made sure that both fields are "TEXT"

 

Why is the "SELECT AdministrativePassword" not like this ... "Preference::AdministrativePassword"

And why is the "WHERE SearchText" not like this ... "Preference::SearchText"

 

The "?" makes perfect sense to me and the field it uses make perfect sense .. but inside the statement nothing is making sense.

 

I'm reading everything I can, but it still not connecting in my mind ??

 

Anyway, here is the calculation ... 

 

 

ExecuteSQL ( 

 

"SELECT AdministrativePassword

 

FROM Preference

 

WHERE SearchText  = ?" 

 

 ; "" ; ""; Preference::AdministrativePassword )

Hi Eos,

 

Once I get this working, will I be able to determine whether or not I have a true or false condition.  I want to continue with the script if I have a Match and / or do something "ELSE" if I don't have a match.

 

Thank you.

 

Tom :-)

[Wim Decorte]

at first glance there is nothing wrong with the query.  Is there a TO named "Preference" (mind the uppercase)?  Is there a field named "SearchText" in the Preference table?   Is the Preference::AdministrativePassword that you are injecting valid from the context where you run the query?

[eos]

Why is the "SELECT AdministrativePassword" not like this ... "Preference::AdministrativePassword"

And why is the "WHERE SearchText" not like this ... "Preference::SearchText"

 

ExecuteSQL is a FileMaker function, where the first parameter is the SQL SELECT command with its own syntax rules; SELECT only uses field and table names. The other parameters are field and record delimiters, and the optional variable references – and here you're back in the FileMaker world, where a fully qualified field reference is written as TableName::fieldName.

 

Your expression is syntactically correct, but I think we've been sidetracked by another post, not noticing that it is semantically false. You want to check if the admin password from a one-record table is identical to the user input into the field SearchText, so you only need to grab the one existing value – unconditionally, i.e. no WHERE – and do exactly that.

Let (
pw =
ExecuteSQL ( "
SELECT AdministrativePassword
FROM Preference
" ; "" ; "" ) ;
pw = Preferences::SearchText
)

If this evaluates to True, then your user entered the admin password.

["... you mean these fans?"]

Hi Wim,

 

I'm not sure what you mean by: Is the Preference::AdministrativePassword that you are injecting valid from the context where you run the query?

 

I am on my "Locations" table and the information I need to validate is in my "Preference" table.  Both the "SearchText" field and the "AdministrativePassword" field are in my "Preference" table ... however I'm on my "Locations" table when I run my script.  I've attached my script.

 

I'm hopeful I can have the user enter the "Administrative Password" into the global "SearchText" field from a message box, before the script would continue.  Again, I usually build relationships between the search field and the field that has the data to validate a user entry.

 

If I'm understanding correctly, you no longer need to have relationships with an ExecuteSQL () calculation.

[eos]

Besides any other errors there may be, you are using the wrong tool. What you need is ExecuteSQL() within a calculation context, and NOT the Execute SQL[] script step, which is used to access SQL sources outside of FileMaker.

 

Try something like

If [ not formula from post 13 ]
  Error Dialog []
​Exit Script []
...

["... you mean these fans?"]

Hi Eos,

 

Yeah ... I just read that.  So here is what I did.  I put your Let into an If ( IsEmpty ( ) or "=" Zero ...

 

Please see calculation below.  It works.  Can it be condensed ??

 

Thank you Eos :-)

 

 

IsEmpty ( 

 

Let (

pw =

ExecuteSQL ( "

SELECT AdministrativePassword

FROM Preference

" ; "" ; "" ) ;

pw = Preference::SearchText

)  )

 

or 

 

Let (

pw =

ExecuteSQL ( "

SELECT AdministrativePassword

FROM Preference

" ; "" ; "" ) ;

pw = Preference::SearchText

) = 0

 

Thank you.

Hi me again.

 

In my SQL reading for Filemaker, the first "Three" parameters are a "MUST" ...

 

 

"Note that ExecuteSQL takes four parameters, and the first three are mandatory:

"

 

 

Eos, it seems that the "Third" parameter is left out.   Is this OK ??  Obviously it must be since your Let Statement worked great!!

Any insight here I would be grateful.

Again Eos, thank you for your patients with me.

Tom :-) 

[eos]

Please see calculation below.  It works.  Can it be condensed ??

 

Yes; you're only interested in a boolean result to use for your script branching. The expression in the calculation does exactly that: x = y can only be True or False, so there's no need to check on anything else. So your script would start like this:

 

Show Custom Dialog […] // where you prompt and commit the string to compare

If [

Let (

pw =

ExecuteSQL ( "

SELECT AdministrativePassword

FROM Preference

" ; "" ; "" ) ;

not ( pw = Preference::SearchText ) // True or False

) ]

# error dialog

# rest of your script

 

depending on your script logic, use the expression with or without negation, i.e. not ( expression) or expression

 

"Note that ExecuteSQL takes four parameters, and the first three are mandatory:

"

 

Eos, it seems that the "Third" parameter is left out.   Is this OK ??

 

The string "SELECT…FROM… etc." is parameter #1 (the query), "" is #2, and (the second) "" is #3. 

[Wim Decorte]

Hi Wim,

 

I'm not sure what you mean by: Is the Preference::AdministrativePassword that you are injecting valid from the context where you run the query?

 

I am on my "Locations" table and the information I need to validate is in my "Preference" table.  

...

 

If I'm understanding correctly, you no longer need to have relationships with an ExecuteSQL () calculation.

 

You do not need relationships to collect data using the ExecuteSQL function, but if you reference a field's value then that value needs to be available where the function is calculated.

So if you are in your Locations table and there is no relationship to Preferences AND the AdministrativePassword field is not a global then that value of Preference::AdministrativePassword will be empty.

 

So to make it context save, first use ExecuteSQL to collect the admin pw, then another ExecuteSQl to collect the searchText value (or if that is a global you can just use it to compare to the result of your first ExecuteSQL)

[Priyabrata]

Hey Eos,

Thanks mate ,for correcting the execute SQL statement posted by me.

I had missed the spaces in the field name.

["... you mean these fans?"]

Hi Eos,

 

Thank you for your assistance in this matter.

 

As I continue my reading and basic tutorials on  SQL this example will be a nice point of reference I will have as I learn this stuff.

 

Tom :-)