FM requiring unique in SQL

[LaRetta]

Hi everyone,

I've been attaching some SQL tables (external data sources) and displaying them in my graph. FM requires a unique field (or fields) but not all of the tables have one uniqueID (and yes, I can't imagine why they don't either). Here is my question and I'm asking because I can't test it and I'm hoping someone knows the answer for me:

I have this fear that I've specified several fields as the unique identifier in a SQL table and it is when I add it to the graph (because FM lets me) and it all works well but later that SQL table has a duplicate come through!! I cannot control what I am attaching to; it is data coming in from People Soft and various sources. And they don't always validate and they sometimes pull data from other sources (who accidently imports twice and THEY don't validate).

To give myself a 'guaranteed-forever' unique ID, I'd be willing to concatenate even more fields but I don't feel I can EVER BE CERTAIN! Do you feel my mounting tension here? So, since this is something I cannot control, I need to know the results of such a break ...

So ... [color:blue]if a SQL table is in my graph and FM thinks it has a uniqueID and later it doesn't, what happens? I hope I explained my fears appropriately.

LaRetta

[LaRetta]

I thought I'd add more because I might have been a bit unclear ...

FM requires we select field(s) which make a unique identifier when we add a SQL table to the graph. FM then tests what we've chosen. If it is indeed unique, FM lets us add that SQL table to the graph; otherwise FM will REFUSE to allow it in the graph.

So, once added in the graph, what happens if later a duplicate is in the SQL table (which is a dynamic table, updating all the time)? Will FM not display ANY data? Does that SQL table disappear from the graph when there are duplicates (which later populated it)? Does it explode? If FM insists there be a unique ID then it must be important; therefore, something terrible must happen if there are duplicates added after the fact!

I'm not sure I even understand totally what FM DEMANDS that there be a unique ID. In theory, I understand from a database perspective but not from an ESS perspective. After all, we are talking about data we may wish to display only; like adding a table which doesn't have a relationship. So why does FM insist upon it? And again, what happens if that rule is broken?

Even half-blown theories would be appreciated.

LaRetta :smirk:

Edited November 2, 2007 by Guest
Added sentence

[The Shadow]

I imagine FileMaker is going to use the unique identifier to identify the external records you're working on.

For instance, if you connect to a EDS table and give "uniqueId" as your unique field, FileMaker will probably generate queries like:

FileMaker Find:

  select uniqueId from extTable where <...find criteria converted to sql...>

Then, when you want to display a record on a layout, if it isn't already cached, FileMaker will do something like:

  select uniqueId, from extTable where uniqueId in ()

You make a change and want to write it back to extTable, it would probably go something like:

  update extTable set = where uniqueId = 678

Delete record:

  delete from extTable where uniqueId in ()

If you're familar with SQL, you can see what might go wrong - in FileMaker, you will only see a single record, but you'll actually be operating on several in the SQL source, making changes across all of them. This will likely confuse FileMaker 9, but I wouldn't expect explosions.

[LaRetta]

Hi Shawn,

I appreciate your response very much. I spent time pouring over all FM documentation on SQL and ESS and even poured through SQL sites. But I was still stuck and I finally had to call FM tech support.

I realize now that I didn't tell you the most important parts of this: 1) This problem isn't about SQL queries (importing) but rather just accessing information from SQL tables in our graph and 2) We aren't allowed to modify, add, delete or write back to these SQL tables. The tables reside in the FM graph and I relate them to my data on various things so the data can be displayed in FM; so I can add calculations and summaries; and so I can write data through from SQL to FM as needed. They do not always give us ONE uniqueID but rather, tell us to concatenate several fields. When FM asks, I select these fields and FM is then happy to place the table in our graph.

My concern is that these SQL tables can have duplicates according to those concatenated fields although they might have their own uniqueID in their original SQL files! I worried about what would happen if, between when the SQL table was placed in the graph and later when it opened, someone from the SQL side added a 'duplicate' record according to those concatenated fields; whether there might be an error produced; whether FM simply wouldn't open that table at all, or whether it would explode (smile). Upon examination of the SQL tables I'm about to connect, I already have duplicates of these concatenated fields!

Here is the response from Tech Support when they tested it (Case# 0711070000129). FM in blue, my concerns in black:

[color:blue]If later, that SQL table allows duplicates then FM will stop the process ... go to that layout (which displays the SQL table) and place the cursor in the field which is no longer unique (what if all the concatenated fields are duplicated?). [color:blue]There is no error which can be trapped but it will produce the message, "A non-unique value has been entered. Please correct the entry." (but with these SQL tables, we do NOT have rights to modify their tables!). This process is ran WITHOUT USERS also! It is a self-running, self-updating process.

It was explained to me that, if we attempted to exit the field, it would place us in a perpetual loop by putting the cursor back in the field etc etc. If I cannot turn on error capture; if I cannot override the demand that the field be unique, then using concatenated fields becomes unsafe, in my opinion. Upon reviewing error messages, 504 (value in field is not unique as required in validation entry options) would seen to fit but it does no good if I can't override it.

I feel we are stuck in demanding that ALL SQL tables come to us with their own SINGLE uniqueID. Is that a fair assumption or are there workarounds? Also, I feel this should be reported to FM as a major problem (if it can't be handled). Tech support told me to just demand one unique ID but that goes against the apparent ability to concatenate to create a unique ID.

LaRetta

[The Shadow]

Okay, well that *is* more like explosions.

I do find it really hard to believe that whoever you're working with has a SQL table with no primary key declared, and they are unwilling to add one.

[LaRetta]

I do find it really hard to believe that whoever you're working with has a SQL table with no primary key declared, and they are unwilling to add one.

You and me both, Shawn, particularly from what I've been reading ... that a unique ID is NOT an unusual nor difficult request and that indexing fields is a common request as well. In the one very large SQL pull (People Soft using Oracle client 10g), we have one table with 500,000 records to attach to with one key we create which concatenates 5 fields. No indexing either. I was told People Soft doesn't have a unique ID!!??!

I have been asked that if FM hits this validation failure, it could send an email to System Admin or write to an error log. My answer? No. Because FM will be locked in field-level validation failure and file can't fire another script. If anyone knows of a way around this, please let me know (possibly event trigger plugin)?

In meantime, allowing concatenated keys can be dangerous since unique validation might fail. My concern is that other people may be biten by this as well so I plan to follow up with FileMaker on it and report it as an 'unexpected behavior'. I still question whether People Soft truly doesn't have their own unique ID! I can't even imagine it and wonder if it is true. But I'm hot on their butts about it; that's for sure. And we will either be provided a single uniqueID or I'm getting it in writing so a break because of it won't be a surprise to anyone.

Ideas on a way to trap for a looping field-level validation failure (in case I don't succeed on unique ID)? :crazy2:

[Horacio]

Had the same problem myself, unbelievable that any DBA would have a table without unique id...

My solution was to have the dba (this was an Oracle system for a Higher Ed student information system) do a view that had a rownum/id column added... of course I can never be assured that 0001 will always be one but can be assured of no explosions... a compromise sure but you do what you have to do with legacy systems/people ;-)

[LaRetta]

Thank you! Yeah, we can't always get what we want when dealing with huge IT departments from different companies. But, even if we received a true unique ID, it could break. Some of these tables are written to, and they contain EXACT duplicates 25 seconds apart (according to creation timestamps). Then what? FM validation will still fail.

Although I've been searching and studying, I still do not understand how SQL works, ie, some of the SQL tables are dynamic - they display new information as soon as I Flush Cache SQL Data. It's like I'm connected to them live. Other SQL tables and views don't refresh until nightly (they appear to be written to once a day). If they are 'written to' then there might be possibility of them mis-writing or writing twice, no?

I think this People Soft table would break immediately anyway ... there are 66 identical duplicates in it (it is written to nightly). They are all TOTALLY BLANK records. Doesn't FM consider them duplicates if all of the concatenated key fields are blank? Of course I can't search for ! in a SQL field that isn't indexed so I can't search that concatenated key to verify it. But if I import that table into FM then search, FM says they are dups so I think it safe to assume it would break and lock because of validation failure.

I'm afraid that having SQL in my graph (without their OWN SINGLE uniqueID) is not worth it until we have an option to override field-level validation. I am very new to the world of SQL so I really appreciate all the input I can get. I'm putting in my own work but I still feel like total newbie on it.

LaRetta

[LaRetta]

Thankfully I am wrong ... if I search for ! in a blank field, it does NOT bring up all records with that field blank as DUPLICATES! I believe that field-level validation on unique will ignore these blank records in this SQL situation. I hope I'm right. :wink2:

[Ilyse Kazar]

Had the same problem myself, unbelievable that any DBA would have a table without unique id...

This is actually not uncommon at all in the world of SQL back ends, most particularly in join tables where what is known as a "compound PK" -- two or more fields which together function as unique identifiers for a row -- are often used. In some application develoment environments (such as Servoy) when accessing a table with no single PK field one can specify multiple fields as "row identifiers.

For example, a "user" table will have a user_id, a "group" table will have a group_id, and the join table assigning users to groups will have as the unique row identifiers user_id and group_id, without a usergroup_id field. IOW there is no primary key. This is not at all uncommon.

I would suggest, especially since many large IT departments will find it aggravating to have to revise the structure of their SQL bases in order to accommodate FileMaker's ESS, that anyone who wishes to use -- and see improvements in -- ESS post a feature request on FileMaker's site asking for multiple-column row identifiers to be possible when a table has no PK.

kazar

[cbum]

"many large IT departments will find it aggravating to have to revise the structure of their SQL bases in order to accommodate FileMaker's ESS, that anyone who wishes to use -- and see improvements in -- ESS post a feature request on FileMaker's site asking for multiple-column row identifiers to be possible when a table has no PK."

Seconded - I just ran into the same exact problem.

The DBA just looked at me blankly when I asked him "what do you mean, the tables don't have unique IDs..." -(