locked
SqlSyncScopeProvisioning - How to add complex filter RRS feed

  • Question

  • Hello,

             I am in trouble now. I am able to add filters on the column that directly belongs to table. But I want to add filter on the column that does not directly belongs to the table. e.g. I have two tables Patient, Doctor and Address. Patient & Doctor tables have reference to the Address table which contains the address detials.

    Address table's columns : AddressUid, City, State, Zip, Country, .....

    Patinet table's columns : PatinetUid, FirstName, LastName, ............, AddressUid

    Doctor table's columns : DoctorUid, ListName, Specialty, ........., AddressUid.

    I need sync only two tables Patient & Address, so I'll define the scope with these tables. I want to sync all rows from Patient tables but from Address table I want to sync only rows those are referenced in Patient table (I want to sync only patinet's addresses and not of the Doctors).

    I need help in defining filter on Address table so that only the Patient's addresses will be synched.

    Thanks in advance.

     

    -Ajinat

    Wednesday, May 26, 2010 6:01 AM

Answers

  • Hey Ajinat,

    By default, SyncFX supports adding filters on the columns in the table. For the cross-table filter scenario you have, you could set the FilterClause with Join of the PK and FK of those 2 tables. And if you also want to sync delete, you need to make sure adding the FK column to the filter column list so the tracking table will have the FK column.

     

    Thanks,


    Ann Tang
    Wednesday, May 26, 2010 6:40 PM

All replies

  • Hey Ajinat,

    By default, SyncFX supports adding filters on the columns in the table. For the cross-table filter scenario you have, you could set the FilterClause with Join of the PK and FK of those 2 tables. And if you also want to sync delete, you need to make sure adding the FK column to the filter column list so the tracking table will have the FK column.

     

    Thanks,


    Ann Tang
    Wednesday, May 26, 2010 6:40 PM
  • Thanks Ann Tang.

                    I tried this but no luck :(.  Here is what I did :

    My database contains Practice, Address, Patient, Doctor tables as :

    Practice table's columns : PracticeUid, Name

    Address table's columns : AddressUid, City, State, Zip, Country, .....

    Patinet table's columns : PatinetUid, FirstName, LastName, ............, AddressUid, PracticeUid

    Doctor table's columns : DoctorUid, ListName, Specialty, ........., AddressUid, PracticeUid

    I have created scope with Practice, Address & Patient tables with the filters :

    Practice => with PracticeUid = 'practice1Uid'

    Address => I added the column 'PatientUid' in FilterColumns but it throws exception as PatientUid column does not directly belong to this table. Then I removed the PatientUid column from the FilterColumns and specified the FilterClause = 'INNER JOIN Patient ON [side].AddressUid = Patient.AddressUid WHERE PatientUid = 'practice1Uid' ' but it also throws an exception saying that the 'FilterColumns' property could not be NULL.

    Patient => with PracticeUid = 'practiceUid'.

    Can you please tell me what I am doing wrong in case of Address table's filter. If you give an example of the FilterCaluse with JOIN, it will be helpful for me.

    Waiting eagrly for your reply.

     

    -Ajinath

     

    Thursday, May 27, 2010 6:52 AM
  • havent tested this but can you try this:

    for table Address, set the FilterColumn to AddressUid

    then for the FilterClause set it to : [side].AddressUid IN (SELECT AddressUid FROM Patient)

    Thursday, May 27, 2010 7:32 AM
  • Thanks JuneT. It worked.

     But having IN clause instead of JOIN will affect performance, don't you think? My DBA will kill me if I use IN instead of JOIN. Is there any other way which will give me good performance. I am concerned with the IN clause because moving forward, If I want to synch addresses (for the example given in original post)of more entities like Doctors, Staff but excluding some specific entities. In the sub-query I would be required to use UNION of all the addresses of including entities which will be very unmanagable as hundreds of Address like entities I need to synch with same conditions.

    Please guide me in proper direction.

     

    -Ajinath

    Monday, May 31, 2010 5:06 AM
  • you'll find it difficult to put a join clause. If you look at the SelectChanges stored procedure, the FilterClause that you specify is actually concatenated as an extra condition (using AND) to an existing WHERE clause with all the other criterias like timestamps, etc...

     

    Monday, May 31, 2010 12:10 PM
  • Thanks for the information.

    Now I caught up in a new trouble. Here is the problem with steps :

    1.) I created a new address (new entry in the Address table) in Peer1 database

    2.) Initiated the sync with Peer2 database.

    3.) Newly created address dint came over to the Peer2 (because it was not referenced to any Patient).

    4.) I created a Patient (new entry in the Patient table) in Peer1 database and referenced the address that was created before sync.

    5.) Initiated the sync with Peer2 database.

    6.) Newly created patient dint came over to Peer2. Reason is : 'The INSERT statement conflicted with the FOREIGN KEY constraint'. I checked the statistics and found that only the Patient entry was sent to the Peer2 but not the Address entry that is referenced by this Patient entry.

    7.) Then I updated the Address entry and initiated sync again.

    8.) This time I see two changes were uploaded. But I see same thing with the Patient entry i.e.  'The INSERT statement conflicted with the FOREIGN KEY constraint'. And the Update of the Address failed with the conflict 'LocalCleanedupDeleteRemoteUpdate'.

     

    Here I was expecting the Address entry to come over in the 2nd sync cycle, but it dint. I will run in the same situation many times like the above, so can you please tell me what I am supposed to do to handle this ?

    Please help.

     

    -Ajinath

     

    Tuesday, June 1, 2010 10:04 AM