sql.FieldList ( )

Function stats

Average user rating
507
386
9999
Support
FileMaker 10.0 +
Date posted
20 August 2015
Last updated
22 August 2015
Version
Recursive function
Yes

Author Info
 Thomas

1 functions

Average Rating None

author_avatar



 

Function overview

Prototype

sql.FieldList  ( _listOfFieldNames )


Parameters

_listOfFieldNames  List ( GetFieldName ( Table::FieldName1 ) { ; GetFieldName ( Table::FieldNameN ) } )


Description

Tags:  sql variables list  

I know the FMstandards guys would want me to have long clear names that make sense... apologies. And I'm sure you've HAD a long clear discussion about why ACRONYM naming approach is ALWAYS wrong, but I can't help myself... personal weakness, and perhaps not having read your discussion! :)

Examples

Sample input

Let ( _query = "SELECT " &
sql.FieldList ( List (
    GetFIeldName(Table::FieldName1) ;
    GetFieldName(Table::FieldName2) ;
    GetFIeldName(Table::FieldName3) ;
    GetFIeldName(Table::FieldName4)
) ) &
" FROM Table WHERE \"UID\"= 1234567" ;

_query & ¶ & Quote($sql.FL) & ¶ & Quote($sql.FLF)

)


Sample output

SELECT "FieldName1", "FieldName2", "FieldName3", "FieldName4" FROM Table WHERE "UID" = 1234567
"FieldName1¶FieldName2¶FieldName3¶FieldName4"
"Table::FieldName1¶Table::FieldName2¶Table::FieldName3¶Table::FieldName4"

 

Function code

/* If you like this, respect and Kudos to Daniel Smith, JBante & Fabrice Nordmann for inspiration in approach

I like the thinking behind FileMakerStandards.org

2015 (based on #Dict2Var) Thomas Seidler, harmlesswise.com

If you don't like, blame me, cos I've taken the approach to [SQL] utility functions in a perhaps confusing direction!! Thought to try return to the community... (I use Daniel Smith/JBante's sql.fieldName and sql.tableName functions everywhere, sql.fn and sql.tn for short...!)

I frequently SQL query lists of fields, and this enables me to access them more intelligibly and easily. It does TWO prime things:

1. Actually returns the correct formatted field names for SQL query returning the literal result thus: "Field1", "Field2", "Field3", "Field4".

2. Creates TWO variables with value lists of the fields:
$sql.FL = Field List (FieldName only);
$sql.FLF = Field List FULL (i.e. with "TableOccurance::" prefix)

In practice it is to be used thus:

ExecuteSQL ( "SELECT " & sql.getFieldList ( List ( GetFIeldName( Table::Field1 ) ; GetFieldName(Table::Field2) ; GetFIeldName(Table::Field3) ; GetFIeldName(Table::Field4) ) & " FROM table WHERE ID=1234567" ; "" ; "" )

$sql.FLF will return "Table::Field1¶Table::Field2¶Table::Field3¶Table::Field4"
This could be used in a looping Set Field By Name script step, with $i as iterator, it would remain field name independent, so you can change field names, if copying some values from one field to another in same Table for instance.

$sql.FL will return "Field1¶Field2¶Field3¶Field4" and presumably you will already have an array of Value List CF manipulators... if anyone is interested rate this function, or just ask me and I will upload the three CFs I think most necessary:

ValueLists2Variables ( _VL_varNames ; _VL_varValues ; _global )

This has two dependencies, however, so you will perhaps think me mad (and so I don't upload de facto, not wishing to clutter a site with my own CF-insanity!). The dependancies are primarily because (to be really useful) it uses my GetAsVarType function [though I see Fabrice has also got a DataType CF and I don't want to duplicate], which in turn uses IsNumber. I use these *everyday* and *everywhere*! ;)

[BTW if you get to the point: "OH, my values have ¶ in them?! ...but I want to use this!" Yes, you can play around to make it work (quoted values & GetValue etc), but probably need a more powerful tool, my friend >>> http://www.harmlesswise.com/0906-filemaker-python-dictionary-list-functions - still working for me 6 years on (version 29!) and very reliably, I can use JSON now so easily, happy days - not as *fast* as could be to be sure, but fast enough for my practical needs]
*/

Let ([
    ! = If ( not $$~sql.FL.start ; Let ( $$~sql.FL.start = 1 ; "" ) ) ;
    _pos = Position ( _list ; ¶ ; 0 ; 1) ;
    _fn = If (_pos ; Left(_list; _pos - 1 ) ; _list ) ;
    _sql.fn = GetValue(Substitute(_fn ; "::" ; ¶);2) ;
    $$~sql.FL = List ( $$~sql.FL ; _sql.fn );
    $$~sql.FLF = List ( $$~sql.FLF ; _fn )
];

Quote ( _sql.fn ) &

If (
    _pos ;
    
    // MAIN CASE
    ", " & sql.FieldList ( Right( _list; Length(_list) - _pos ) ) ;

    // LAST CASE - function start boolean variable reset ensures variables cleared on start of next run
    Let( [ $$~sql.FL.start = ""; $sql.FL = $$~sql.FL ; $sql.FLF = $$~sql.FLF ; $$~sql.FLF = "" ; $$~sql.FL = "" ] ; "" )
)

)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/386

    Prototype: sql.FieldList( _listOfFieldNames )
    Function Author: Thomas (http://www.fmfunctions.com/mid/507)
    Last updated: 22 August 2015
    Version: 1.5

*/
// ===================================

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (32)
Date  (28)
XML  (27)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Layout  (15)
Interface  (15)
Text  (14)
Variables  (13)
Filter  (12)
Layout Objects  (11)
Design  (10)
Array  (8)