{"id":2625,"date":"2013-09-04T18:02:05","date_gmt":"2013-09-04T18:02:05","guid":{"rendered":"https:\/\/gofilemaker.de\/?p=2625"},"modified":"2022-03-28T16:18:10","modified_gmt":"2022-03-28T15:18:10","slug":"sqlquery-exports-and-field-names-with-underlined","status":"publish","type":"post","link":"https:\/\/gofilemaker.de\/en\/2013\/09\/sqlquery-exports-and-field-names-with-underlined\/","title":{"rendered":"SQLQueryExecute and field names with underscores"},"content":{"rendered":"<p><img decoding=\"async\" class=\"alignleft size-full wp-image-2324\" src=\"https:\/\/gofilemaker.de\/wp-content\/uploads\/gofilemaker_kurztipp2.jpg\" alt=\"FileMaker Quick Tip: SQL queries and field names\" width=\"150\" height=\"150\">FileMaker 12 includes \"SQLQueryExecute\", a function that is extremely helpful in daily FileMaker practice. This function can be used very well, for example, to create value lists from field values of other tables without the need for a relationship. In conjunction with the \"MagicValueList\" function presented by Arnold Kegebein in the FileMaker magazines 201301 and 201302, there are practically undreamt-of possibilities in terms of FileMaker and value lists.<\/p>\n<p>For this purpose, usually only simple <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/sql\/\" target=\"_self\" title=\"What is SQL and what is SQL used for? SQL (Structured...\" class=\"encyclopedia\">SQL<\/a>-queries are required, such as <em>SELECT field name FROM <a href=\"https:\/\/gofilemaker.de\/en\/glossary\/table\/\" target=\"_self\" title=\"What is a table in FileMaker? A table is used in FileMaker...\" class=\"encyclopedia\">Table<\/a> WHERE criterion=xyz<\/em>. Such queries work fine in FileMaker 12 - at least if the field names involved do not contain an underscore at the beginning. However, many FileMaker developers mark key fields with an underscore as a prefix so that they appear at the top of the field definitions and in the relationships graph. Unfortunately, there is a bug in all previous FileMaker versions which means that SQL queries with such field names do not work.<!--more Weiterlesen &gt;&gt;&gt;--><\/p>\n<h2>Result = \"?\"<\/h2>\n<p>If there is an underscore at the beginning of a field name, the \"SQLQueryExecute\" function only returns a question mark as the result. But don't worry: there are ways to work around this error.<\/p>\n<p>Practical example: We have a customer<a href=\"https:\/\/gofilemaker.de\/en\/glossary\/database\/\" target=\"_self\" title=\"What is a database? A database (database system, DBS) is understood to be...\" class=\"encyclopedia\">Database<\/a>to which addresses in another table are linked with a 1:n relationship. The respective default address of the customer is marked with the value \"1\" in the field &lt;&gt;, all other addresses do not contain this marking. If we now want to find out the ID of the corresponding address data record from the customer database using an SQL query, the query would be as follows<\/p>\n<pre>SQLQueryExecute(\"SELECT _pk_address_ID FROM addresses WHERE sta.default=1\"; \"\"; \"\")<\/pre>\n<p>This query would not work and would only return a question mark as a result. However, this is not because there is an error in the SQL query, but FileMaker simply does not seem to like the underscore of the field name &lt;&gt; at this point.<\/p>\n<p>This can be remedied by quoting the field name - many thanks at this point for the corresponding tip from the <a href=\"https:\/\/www.filemaker-magazin.de\/forum\/beitrag\/131682\" target=\"_blank\" rel=\"noopener\"><strong>Forum<\/strong><\/a> of the FileMaker Magazine by Otmar Kramis. If the query is modified as follows:<\/p>\n<pre>SQLQueryExecute(\"SELECT \\\"_pk_address_ID\\\" FROM addresses WHERE sta.default=1\"; \"\"; \"\")<\/pre>\n<p>...then it also works without problems in FileMaker :-)<\/p>\n<h2>Addendum: Tips for the robust development of SQL queries<\/h2>\n<p>The FileMaker Developer <a href=\"https:\/\/gofilemaker.de\/en\/author\/karo\/\">Robert Kaiser<\/a> recommends in another forum post a <a href=\"http:\/\/www.filemakerhacks.com\/?p=4924\" target=\"_blank\" rel=\"noopener\"><strong>Articles from filemakerhacks.com<\/strong><\/a>which describes how SQL queries in FileMaker can be developed in such a way that they function correctly even after renaming or changing relationships or fields, for example. This solution also eliminates the problems described with underscores at the beginning of field names. The article describes the use of custom functions, with which SQL queries in FileMaker can be developed extremely robustly. It is therefore worth delving further into this topic :-)<\/p>","protected":false},"excerpt":{"rendered":"<p>FileMaker 12 beinhaltet mit &#8222;SQLQueryAusf\u00fchren&#8220; eine Funktion, die in der t\u00e4glichen FileMaker-Praxis \u00e4u\u00dferst hilfreich ist. Sehr gut gebrauchen kann man diese Funktion zum Beispiel, um Wertelisten aus Feldwerten anderer Tabellen zu erstellen, ohne da\u00df daf\u00fcr eine Beziehung notwendig ist. Im Zusammenhang mit der von Arnold Kegebein in den FileMaker-Magazinen 201301 und 201302 vorgestellten Funktion &#8222;MagicValueList&#8220; [&hellip;]<\/p>\n","protected":false},"author":187,"featured_media":32984,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":10,"footnotes":""},"categories":[798,1,5],"tags":[],"class_list":["post-2625","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-custom-functions","category-filemakerpro","category-tipps-anleitungen"],"_links":{"self":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts\/2625","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/users\/187"}],"replies":[{"embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/comments?post=2625"}],"version-history":[{"count":0,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/posts\/2625\/revisions"}],"wp:attachment":[{"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/media?parent=2625"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/categories?post=2625"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gofilemaker.de\/en\/wp-json\/wp\/v2\/tags?post=2625"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}