Underscore as first character in field name?

[David Nelson]

I have been reading so much and not sure where I read this but it has to do with SQL I think, probably the Execute SQL function. Anyway now in version 12, I read that underscore should not be used as first character in field names. But how is everyone making their keys pop to the top of the list if not underscore? I tried space but it would not take it, nor dash. Period won't work as first either.

It would help because FM always resorts the relationship into alpha when pulling up imports. And in general it is easier if I can sort by name and have the keys at the top.

What is everyone using now please?

[bcooney]

I'll have to cope with this as well. You could wrap the names (use Kevin Frank's CF) or use aa_

[dansmith65]

I also used to always use underscores as field prefixes, but am now working on a database that uses "id" for the primary key and "id_relatedTable" for foreign key's. It's actually not too bad to work with - it's easy enough to type "id" and jump to that field selection windows.

But, you shouldn't be referencing a field by it's text name anyways IMHO, so you might as well be using a custom function that extract's the field's name and escapes it for SQL.

[beverly]

can you post a link to the CF, please?

perhaps you mean this blog? http://www.filemakerhacks.com/?p=1065

(reference from this forum post http://fmforums.com/forum/topic/76921-custom-functions-for-fql/ )

Beverly

[bcooney]

Yes, Beverly, that is the reference.

[David Nelson]

Hi everyone,

Thank you for the ideas. No I'm not referencing field names. I haven't even gotten that far to even know SQL. I just like to sort by field name but have the IDs at the top. I have tried many different characters but either FM says they are invalid or when I look them up they may be invalid for Oracle such as my latest hopeful, the tilde.

I do not understand how FM can refuse number beginning in field name when text follows in same word but yet accept text in number field. It seems screwy and backwards. What about beginning underscore in table occurrence names? I think I downloaded a file with SQL reserved characters and words maybe Beverly did it but I can't find it now.

We won't be serving or going live for another few months but I want as safe as possible. I appreciate the input.

[David Nelson]

Hi Dan,

You mention cf for referencing field names. I did not see anything like that on FMFunctions and Brian Dunning is too difficult to search. Can you point me to possible ideas of its name so I can search for it? I might as well get prepared so I have it in place when/if needed. Thank you again.

[dansmith65]

Beverly linked to the custom functions I was referring to, she also just wrote a great blog post about FileMaker SQL: http://www.filemakerhacks.com/?p=6406#comment-2847

I think I use one more custom function that isn't on the previous link, but can be found here: http://fmforums.com/forum/topic/85263-executesql-failing/#entry392853.

I use these custom functions for two reasons:

1. I see references to a field/table in a DDR report and I can change a field/table name without breaking my SQL queries.
   
2. It doesn't matter what your table or field names are, they will work in an SQL statement because the names have been quoted.