The difference between a field Reference and a Litteral

[Ron Cates]

I've been working on a series of custom functions for building SQL queries. As I tailor these Custom Functions to meet many different situations and combine them in many different ways as well as making some recursive, I am running into situations where I can send a field reference and other times when I need to send a literal name of a field. What I mean by that is for instance is I am either sending ~SQLfield ( Table::field ) referencing the field or ~SQLfield ( "Table::field" ) using literal text.

If I send as a field reference such as ~SQLfield ( Table::field )
The following works 

Let ([

~field = GetValue ( Substitute ( GetFieldName ( ~fieldRef ); "::" ; "¶" ) ; 2 ) ;

~table = GetValue ( Substitute ( GetFieldName ( ~fieldRef ) ; "::" ; "¶" ) ; 1 ) ;
~table = ExecuteSQL (

"
SELECT DISTINCT BaseTableName FROM FileMaker_Tables

WHERE TableName = ?
"

;"";""; ~table
)

];

Quote ( ~table ) & "." & Quote ( ~field )

)

 

If I send as a field reference such as ~SQLfield ( "Table::field" )
The following works 

Let ([

~field = GetValue ( Substitute ( ~fieldRef ; "::" ; "¶" ) ; 2 ) ;

~table = GetValue ( Substitute ( ~fieldRef ; "::" ; "¶" ) ; 1 ) ;
~table = ExecuteSQL (

"
SELECT DISTINCT BaseTableName FROM FileMaker_Tables

WHERE TableName = ?
"

;"";""; ~table
)

];

Quote ( ~table ) & "." & Quote ( ~field )

)

 

The difference being in the use of GetFieldName ( ~fieldRef ) when sending a field reference which does not work when sending the field name as a litteral.

 

So my question is this.

Is there a way within the custom function to determine if the value being passed is a reference or a literal and adjusting accordingly so that passing either ~SQLfield ( Table::field ) or ~SQLfield ( "Table::field" ) could both work?

 

 

Edited June 17, 2016 by Ron Cates

[Fitch]

Maybe. But maybe you'd be better off with separate functions.

[Ron Cates]

That's actually what I have done. I was just hoping there was a way so I could consolidate them.

[Wim Decorte]

http://www.soliantconsulting.com/blog/2013/11/custom-function-parameters-value-or-reference-example

and this is how I test whether I passed in a field ref or a string:

 

_fieldRef = If(

PatternCount( fieldRef ; "::" ) = 0 ; GetFieldName( fieldRef ) ;

fieldRef

) ;

[Ron Cates]

Bam! Racked my brain all day Friday trying to figure it out, came in this morning and there it is!

Thanks Wim