SQL mapping to thin air

[LaRetta]

I can manually import ODBC from SQL table (using System DSN SQL Server). Whether using the Query builder or entering Query text, the import map opens fine and displays all SQL fields. When I create a script, I can again enter Query text or use Query builder and the Import map opens and displays all SQL fields.

Problem: When I build a calculation (by using a global text field which I set via script), the field mapping dialog will not display the SQL field names. All calculations – all queries I’ve written WORK. But calculated queries (even very simple ones) refuse to show the SQL fields in the import map box. I can map the fields (they allow an arrow) and FM knows there are fields on the left but it simply will not display the field names. I can’t map thin air to my FM fields!

I can make it work by typing a query in the SQL text box or using the Query builder. It will then display all the fields and I have to perform the full mapping there. Then, I change it back to a calculation (even a DIFFERENT calculation) and, when the import map opens, it shows all arrows lined up (no names though) but the import works and imports to the right fields I specified. [color:blue]No SQL field names ever display if the query is via calculation.

It appears to work this same way in vs. 8 and 8.5. I cannot test 7. But it worked properly in 5.5 (and DISPLAYED the SQL fields for selection) when calculation was specified because a manual import map had to be done FIRST then it just stored THAT! It *appears* that FM forgot this small change when moving to versions which no longer required a manual map, instead storing the import map within the script.

Has anyone else noticed this and is there something I’m missing or any way around this issue? It is annoying because, if I need to change the query, I need to revert it back to SQL text by inputting a dummy query just so I can view my SQL fields in the map again. And, unlike FM’s calculation box, the prior SQL query doesn’t remain for easy selection the next time.

LaRetta

[LaRetta]

I should clarify ... to get it to display ALL fields for mapping, I need to create SQL text or Query Builder of something like: SELECT * FROM "theSQLtable". I think you probably knew what I meant but I wasn't clear on it.

[LaRetta]

So nobody has tried using a calculation to import SQL? I would at least like to verify if others are seeing the same behavior or whether it is specific to one OS version. :

[librone]

Problem: When I build a calculation (by using a global text field which I set via script), the field mapping dialog will not display the SQL field names.

Has anyone else noticed this and is there something I’m missing or any way around this issue? LaRetta

Hi Laretta

Yes, it is so.

Filemaker don't display field names!

If you use also variable, it don't display field names.

But it work!!!

Global field and variable, they are the same thing.

Filemaker does not know the value until they are not defined.

Sorry for English. :

Ann

[LaRetta]

Hi Ann,

I have tried to understand the logic but it still evades me. I thought, because it was a calculation, FM didn't know the field names to display until the calculation evaluated. However, even once mapped and working, I can immediately go back into the map and the field names are still blank. And, as you've noticed, FM DOES know the fields are there and mapping/import works so it simply can't be because it's a calculation.

Thank you so much for verifying this for me. I plan to test this on my Mac this weekend but I believe the results will be the same. I appreciate your help and your english is great!

LaRetta

[cjaeger]

Hi LaRetta,

FileMaker will display the field names when you actually perform the query (unchecking the no dialog box), but not if you are setting the import in the scripting import map dialog.

The easiest and safest way to perform a match is a calculated SQL Query like this

SELECT "tablename"."fieldname" 'filemaker target field name' FROM ...

an do a "match names" import.

[cjaeger]

And you guessed right: Filemaker cannot know the field names from a calculation because these can change anytime. It simply remembers the names from the last query performed (the sort by submenu in the match dialog shows "last order".

I think this is logical behaviour, because a calculated query can

- use a different table

- a different number of fields

- different field order

- different field names

everytime the query is performed. That's why the field names are only displayed when the actual query is performed.

And yes, it's the same on a Mac.

The fact that your calc is returning the same fields is pure coincidence, but nothing FileMaker can forsee.

[LaRetta]

I haven't responded because I'm still working on this issue. I'm not convinced that it is behaving properly. Ha ha! And I refuse to call FileMaker and ask because I don't believe them either (they usually answer via a dart board) and I feel that, if I prove it's not proper behavior then I'll be reporting a bug; and if I can make logic out of it then I won't call them at all.

Regardless, I wanted you both to know that I very much appreciate you responding. Working with SQL is like wadding through molasses but I'm finding many shortcuts. And FileMaker DEMANDING a unique field (or key) before it can even put a SQL table in my graph is insantiy. But then again, I'm having a blast and wouldn't trade it for anything! Thank you again!!

LaRetta