Brian Dunning's FileMaker Custom Functions

RequestSQL ( fieldDesired ; fieldKey ; valueKey )

retrieves value from one field base on value of another field

  Average rating: 4.0 (37 votes) Log in to vote

Hisc   Hisc - Show more from this author
noplace
http://nourl

Share on Facebook Share on Twitter

  Sample input:
RequestSQL(customers::name;customer::CustomerID_pk;45)

///

RequestSQL(customers::phone;customer::name;"Dude")
  Sample output:
the name of the customer whose ID_pk is 45

///

the telephone of all the customers whose name is "Dude"

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

this custom functions allows you to retrieve a value from any table, based on a search. One will put the field from which they desire the value – fieldDesired –, the field which will be used as a criteria for finding the right record, from all the existing records – fieldKey – and the value that the fieldKey must have for the desired value to be returned – valueKey

*updated so that it works with number values also, without need to declared that field is number

 

Comments

eos   eos, Teg
Jul 27, 2014
Try your CF with fieldKey being a number field …
 
Hisc   Hisc, noplace
Jul 28, 2014
you're right. I will look back at it ASAP. Would you know how to solve it?
 
Hisc   Hisc, noplace
Jul 28, 2014
seems to be solved. If there is any else I need to correct, please, don't hesitate on "requesting". lol
 
eos   eos, Teg
Aug 11, 2014
See here:

Let ( [
nameFieldDesired = GetFieldName ( fieldDesired ) ;
nameFieldKey = GetFieldName ( fieldKey ) ;
nameFieldDesired_list = Substitute ( nameFieldDesired ; "::" ; ¶ ) ;
myTable = GetValue ( nameFieldDesired_list ; 1 ) ;
fieldDesired_sql = GetValue ( nameFieldDesired_list ; 2 ) ;
fieldKey_sql = GetValue ( Substitute ( nameFieldKey ; "::" ; ¶ ) ; 2 ) ;
fieldType = ExecuteSQL ( " SELECT * FROM FileMaker_Fields WHERE TableName = '" & myTable & "' and FieldName = '" & fieldKey_sql & "'" ; "" ; "" ) ;
condSingleQuote = Case ( PatternCount ( fieldType ; "varchar" ) ; "'" ) ;

~sql = "SELECT " & Quote ( fieldDesired_sql ) & "
FROM " & Quote ( myTable ) & "
WHERE " & Quote ( fieldKey_sql ) & "
= " & condSingleQuote & valueKey & condSingleQuote

] ;

ExecuteSQL ( $$~sql ; "" ; "" )
)
 
Hisc   Hisc, noplace
Aug 12, 2014
eos, I believe it works, but does it have any improvement over the standard function?
I see you changed my way of approach on getting names (Substitute and GetValue instead of Position, which seems more reasonable) and also the way to get the fieldType (which is a best name them valueType, I have to admit), but does it add new functionality?

(If it is about the fieldKey being a number, I have recently updated it).

Anyway, I'm glad to see how you'd do, and I'll probably change a little in latter development.
 
eos   eos, Teg
Aug 12, 2014
New functionality is for the end user; from a developers point of view, it's written more clearly without repetitions; e.g. why set a flag that you then have to read two times to set a value that you could have calculated in the first place?
 
Hisc   Hisc, noplace
Aug 12, 2014
I agree with you. And I thank you for the time on perfecting and explaining. I'll update it.
 
Eric   Eric, SJSU
Jan 19, 2015
I had a number valueKey with dashes in it. It didn't work until I added GetAsNumber(valueKey). I simplified the use of need_quotation_sql slightly.

need_quotation_sql = If(fieldType="Number"; False;True)
];
ExecuteSQL(
" SELECT \""&
fieldDesired_sql
&"\" FROM \""&
myTable
&"\" WHERE \""&
fieldKey_sql
&"\"="&
If(need_quotation_sql=True; "'" & valueKey & "'"; GetAsNumber(valueKey) )
; "" ; ""
)
)
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth:

Under construction. Email me your wish list for improvements.