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 (36 votes) Log in to vote

Hisc   Hisc
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.

Under construction. Email me your wish list for improvements.