Jump to content

ODBC Imports


ryyno10
 Share

This topic is 2840 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!

 

 

Link to comment
Share on other sites

 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'.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 2840 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.