Tunneling Relationship to find unrelated records?

[MSPJ]

Hi - I'm trying to do something that seems like it should be doable, but I can't quite figure out how to do it - I've thought about using relationships and SQL, but neither quite gets me there. 

I have 4 tables: Visits, VisitClaims, Claims, Account. The relationship between Visits and Claims is many to many, thus the join. 

When I'm standing on the Claims table , I can easily see all the visits that are assigned to that Claim - simple. 

If I'm standing on Account, I can see all the visits that have been assigned to claims using that account 

What I'm trying to do is stand on Claims, and find Visits that have NOT been assigned to claims using the same account as the current claim. 

In other words, when I go to create a claim, I don't want to see visits that have previously been assigned to claims associated with the same account as the current claim.

I'm sure I could do this with some kind of multi-step find -- Find the set of visits that were assigned and then omit those from the full set - but I'm not sure how to create a portal filtered by that definition.  I could do a script that runs the find, but I'd have to rerun it every time something changes.  That's a possibility, but I'd prefer not to do it that way.

It seems like there must be a more straightforward way to do this...

 

Thanks!

[eos]

If you need a found set:

# [ in the Claim context ]
Find Matching Records [ Claim::id_account ]
Go to Related Record [ Visits ]
If [ not Get ( LastError ] ) 
  Show Omitted Only
Else
  Show Custom Dialog [ "All visits have NOT been assigned to claims using the same account as the current claim" ]
  # [ or Go to Visits, Show all Records ]
End If

If you want just the IDs:

ExecuteSQL ( " 
  SELECT id 
  FROM Visit 
  WHERE id NOT IN (
    SELECT id_visit 
    FROM VisitClaim 
    WHERE id_claim IN ( 
      SELECT id 
      FROM Claim
      WHERE id_account = ? ) )
  " ; "" ; "" ; Claim::id_account
)

which may be rather slow; If so, try without the nesting:

Let ( [
  claimsIDsThisAccount = // assume textual keys throughout
    "'" & 
    ExecuteSQL ( " 
      SELECT id 
      FROM Claim
      WHERE id_account = ? 
      "; "" ; "','" ; Claim::id_account ) 
    & "'" ;

   visitIDsTheseClaims = 
      "'" & 
      ExecuteSQL ( " 
        SELECT id_visit 
        FROM VisitClaim 
        WHERE id_claim IN (" & claimsIDsThisAccount & ") 
        " ; "" ; "','" )
       & "'" 
   ] ; 
  ExecuteSQL ( " 
    SELECT id 
    FROM Visit
    WHERE id NOT IN (" & visitIDsTheseClaims & ")
    " ; "" ; "" )
)

which has the drawback that you need to format your arrays depending on the data type – and trap for empty result sets!

[MSPJ]

Wow, thanks!  Will try out soon - much appreciated!

 

[MSPJ]

Ok - so I got this to work perfectly in the Data Viewer - used nested ExecuteSQL statements.  

The goal is to use it as a selection criterion, sitting on a claim, looking at a portal of visits.

To do this, I set the portal filter to Visit ID = big long ExecuteSQL expression.

This works fine on one particular claim, but when I switch to a different claim, it no longer works, even though the Execute SQL statement in the data viewer continues to work.  In other words, the Portal Filter seems to be record-context sensitive, while the Execute SQL code in data viewer, does not have that same context sensitivity. 

Does this make sense?

 

Edited July 28, 2015 by MSPJ
Solved original problem but created another