none
SFx 2.x complex filtering: how to define scope filter … or can I use a view to filter? RRS feed

  • Question

  • I am upgrading my project from SFx 1.0 to SFx 2.x.  In 1.0 I was able to modify SELECT statements directly in the adapters.

     

    I must not be correctly understanding 2.x “scope filtering” … since it appears that I can only filter with the simplicity of  “[ColumnName] =@value” for any given table.  What am I missing?

     

    I want my filter to generate a WHERE clause that looks something like this:

    "WHERE AdmitKey In(" +

     

    "SELECT PS.AdmitKey AS ID " +

    "FROM PatientScheduleVisits PSV " +

    "JOIN PatientSchedule PS ON PSV.ScheduleKey=PS.ScheduleKey " +

    "WHERE PSV.ProviderKey=@POC_UserID AND PSV.VisitDate>=@POC_GoLiveDate " +

    "GROUP BY AdmitKey " +

     

    "UNION " +

     

    "SELECT PatientAdmitKey AS ID " +

    "FROM PatientVisit PV " +

    "LEFT JOIN PatientVisit_VisitLock PVVL ON PV.VisitLockID=PVVL.VisitLockID " +

    "WHERE PV.EmployeeKey=@POC_UserID AND PV.VisitStartTime>=@POC_GoLiveDate AND Not PV.NonBillable='True' " +

    "GROUP BY PatientAdmitKey " +

     

    ")";

    How do I provision a complex filter like this?

    Tuesday, May 31, 2011 2:51 PM

Answers

  • never mind ... figured it out - it is simple ... just replace the filter clause with whatever you want your where clause to be (w/o the WHERE word)

    • Marked as answer by P H T Tuesday, May 31, 2011 7:21 PM
    Tuesday, May 31, 2011 7:20 PM

All replies

  • never mind ... figured it out - it is simple ... just replace the filter clause with whatever you want your where clause to be (w/o the WHERE word)

    • Marked as answer by P H T Tuesday, May 31, 2011 7:21 PM
    Tuesday, May 31, 2011 7:20 PM
  • are you synching the tables in the join query as well? are you synching deletes?
    Thursday, June 2, 2011 3:49 AM
    Moderator
  • Yes I am synching (and filtering) the joined tables as well.

    I think I have decided that deletes are so rare in my scenario that I am not going to filter them ... but just allow all deletes to flow to all clients.

    Thursday, June 2, 2011 7:50 PM