pc ODBC Imports

[ryyno10]

I'm scripting an ODBC import (Microsoft SQL Server) from a hospital information system.  I'm using this infomation to construct a report containing "notes" which can be viewed/printed else where in a different FM11 file. 

 

I'm unsure as to the best way to approach importing these records and I was hoping to get some opinions on the circumstance: 

 

Background Info:  I have a relational key that can be used to join imported records to existing filemaker records.  I have an ODBC driver installed on each workstation in order to access the appropriate SQL tables via FM 11 ODBC import. 

 

Approach:  I could import reports at the moment an employee executes/navigates to a particular report layout.  I could create an SQL import with the appropriate "WHERE" clause which would reduce the import performance hit (versus importing 10000 + records each time). 

 

Question 1:  With the latter approach, I'm concerned about importing duplicates.  To avoid duplicates in each imported table, will validation rules prevent duplicate records from updating?  For example, if I put a validation rule on a primary key field and a import is conducted while a found set is present, will this alway result in the importation of new records?

 

Tests: I have executed scripts to import records, including validation rules as described above, and it seems to be working to fit my need (not importing duplicates, even when found sets exists).

 

Any comments or suggestions would be greatly appreciated!!

 

 

[comment]

 To avoid duplicates in each imported table, will validation rules prevent duplicate records from updating?  For example, if I put a validation rule on a primary key field and a import is conducted while a found set is present, will this alway result in the importation of new records?

 

I don't understand your question. What do you mean by "prevent duplicate records from updating"? If you have a field validated (always) as unique, then it is not possible to create duplicates by importing (adding) new records. It does not matter if a found set is present or not before starting the import.

 

If your import is updating existing/matching records, and the source contains duplicates, then each duplicate will update the corresponding target record in turn - resulting in the last duplicate record overriding all its preceding siblings. Again, no duplicates will be created by this import, even if you have checked 'Add remaining data as new records'.

[ryyno10]

Sorry comment, I meant to say will validtion prevent the duplication of imported records, regardless of the found set

 

The SQL tables will not contain duplicate ID numbers, either.

[ryyno10]

I don't understand your question. What do you mean by "prevent duplicate records from updating"? If you have a field validated (always) as unique, then it is not possible to create duplicates by importing (adding) new records. It does not matter if a found set is present or not before starting the import.

 

If your import is updating existing/matching records, and the source contains duplicates, then each duplicate will update the corresponding target record in turn - resulting in the last duplicate record overriding all its preceding siblings. Again, no duplicates will be created by this import, even if you have checked 'Add remaining data as new records'.

 

I suppose more specifically, will the validation settings prevent the importation of unique values that aren't in the found set when the import is executed?

[comment]

I suppose more specifically, will the validation settings prevent the importation of unique values that aren't in the found set when the import is executed?

 

I am am still having problems with your formulation. Let me put it this way: if a field is validated as unique, validate always, then you cannot import (add) a record that contains a value that already exists in the target table. It doesn't matter if the existing value is in the found set or not; that only affects which records will be updated - if you are using the update method.

 

If there's a scenario you're not sure of, why don't you construct a simple test and see for yourself?