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 - Show more from this author
noplace http://nourl |
///
RequestSQL(customers::phone;customer::name;"Dude")
///
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, Teg Jul 27, 2014 |
|
| Try your CF with fieldKey being a number field … | ||
|
Hisc, noplace Jul 28, 2014 |
|
| you're right. I will look back at it ASAP. Would you know how to solve it? | ||
|
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, 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, 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, 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, noplace Aug 12, 2014 |
|
| I agree with you. And I thank you for the time on perfecting and explaining. I'll update it. | ||
 |
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) ) ; "" ; "" ) ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.