AnsweredAssumed Answered

SQL Query to get structure subset

Question asked by PeterGort on Apr 19, 2013
Latest reply on Apr 20, 2013 by PeterGort

I'm trying to build an SQL Query to give me the stored data fields only from a table.

 

If I execute

 

SELECT * FROM Filemaker_Fields

 

I get a nice long list of every field in every table occurence. The columns are

 

TableName, FieldName, FieldType FieldId, FieldClass, FieldReps, ModCount

 

Subsetting to FieldClass = 'Normal' leaves out all the calcs & summaries, so far so good.

 

Then I want to leave out the globals, and whether or not a field is global is in the "FieldType" column. Types are varchar, decimal, date, global varchar, global decimal, global date etc.

 

(yes I know the first two variables in this let statement are redundant)

 

Let([

tabchar = Char ( 9 );

returnchar = Char ( 13 );

ltn = Get ( LayoutTableName );

sqlQuery = "SELECT FieldName FROM Filemaker_fields ff

WHERE ( ff.Tablename = ? ) AND ( ff.FieldClass = 'Normal' ) AND ( ff.FieldType NOT LIKE 'global*' )

ORDER BY ff.FieldName"

];

ExecuteSQL ( sqlQuery ; tabchar ; returnchar ; ltn )

)

 

But the NOT LIKE clause is ignored and it returns the global field names in the results. Now if I change it to explicit LEFT(FieldType,6 ) <> 'global' it works

 

Let([

tabchar = Char ( 9 );

returnchar = Char ( 13 );

ltn = Get ( LayoutTableName );

sqlQuery = "SELECT FieldName

FROM Filemaker_fields ff

WHERE ( ff.Tablename = ? ) AND ( ff.FieldClass = 'Normal' ) AND ( LEFT ( ff.FieldType , 6 ) <> 'global' ) )

ORDER BY ff.FieldName ASC"

];

ExecuteSQL ( sqlQuery ; tabchar ; returnchar ; ltn )

)

 

But I don't understand why the NOT LIKE syntax didn't work. Can anybody tell me what I did wrong?

Outcomes