Find Missing Records

Jhstephenson

We think this should be fairly simple, but we are missing something...

We have two tables, one called 'links' on an external system and one called 'documents' in our Aware app.

Records are added to 'links' periodically and we want to run a process that will copy them over to 'documents'.

The idea is that if the content of 'filepath' from 'links' is not in a record in our 'documents' BO then the record should be copied over.

We have some SQL that shows us how to find those missing records:

SELECT * 
FROM links l
WHERE NOT EXISTS (
	SELECT * 
	FROM documents d 
	WHERE d.filepath = l.path
}

How do we put that into an Aware Query?

BLOMASKY

maybe something like:

FIND Links where NOT EXISTS Documents WHERE (Documents.filepath = Links.path)

maybe?

Bruce

tford

I have a BSV that does exactly that. I can take a look at it tomorrow & post the process here.

bssxfire8

BLOMASKY wrote
maybe something like:

FIND Links where NOT EXISTS Documents WHERE (Documents.filepath = Links.path)

maybe?

Bruce

Bruce, Thank you for your post. Jim and I are working on this together and I came up with a similar Find:

FIND Documents WHERE (NOT(EXISTS Documents WHERE (Documents.LKLINK=EmployeeDocuments.DocumentPath)))

The Documents Object is on an AS400 being accessed through the JDBC connector and EmployeeDocuments is within the Database Aware is setup on which is MySQL.

Everything I see points to this working but I am getting this error when I run it:

Internal error. Error reading business object.
[SQL0204] BASTESTDOMAINEMPLOYEEPORTAL_DOCUMENTS in NEWPRLIBRF type *FILE not found.

Have you ever seen anything like that error wise?

tford

In my scenario, Games is an existing external DB & GameAssignment is an AwareIM Automatic DB that has a 1:1 relationship to Games. Each GameAssignment instance references an instance of Games and has bunch of shortcuts as well as additional attributes.

I have a nightly scheduled process that creates a GameAssignment instance for any record in Games that doesn't yet have a GameAssignment instance.

Process: Z_1_Build_GameAssignments_1

FIND Games WHERE Games.EventDate>SystemSettings.Game_Sync_After IN BATCHES OF 1

If EXISTS Team WHERE (Team.SportCode=Games.Sport AND Team.LevelSC=Games.Level) Then
Z_1_Build_GameAssignments_2
Else
SEND OutgoingEmail_Game_Assignment_Sync_Add_NO_TEAM

Process: Z_1_Build_GameAssignments_2

SystemSettings.Game_ID_HOLD=Games.GamesID

If COUNT GameAssignments WHERE (GameAssignments.GamesID_in_KenticoNbr=SystemSettings.Game_ID_HOLD)=0 AND Games.EventDate>=SystemSettings.Game_Sync_After Then
FIND Team WHERE Team.SportCode=Games.Sport AND Team.LevelSC=Games.Level
CREATE GameAssignments WITH GameAssignments.Game=Games,GameAssignments.Team=Team,GameAssignments.Role_Article=Team.Default_Article,GameAssignments.Role_Photographer=Team.Default_Photographer,GameAssignments.Role_ScoreAbstract=Team.Default_Score_Abstract
SEND OutgoingEmail_Game_Assignment_Sync_Add

bssxfire8

Thanks Tom! That is fairly similar to what I have ended up doing.