Link 2 databases and suppress records if exist in both

[KingPhoenix]

Hi all,

Ok, i have an issue that i cant get my head around.

I have a report which exports a CSV file, to allow a SQL DTS to import automatically into another database. What i need in the report, is for it to check the new database, and if it already exists in there to suppress the record detail otherwise the DTS fails.

The issue is.... in Database 1, the Job number is stored as 33500 or 33500A ... in Database 2 the Job number is stored as IM-33500-1 or IM-33500A-1

The IM part is plain text, and the -1 on the end comes from another field in database 1...

I considered the idea of using shared variables and sub reports, so that if i ran a sub report in group header1A, then had the rest of my info in group header B, i could suppress conditionally based on a formula in the subreport but cant work out a forumla to return a 1 if the record exists in both databases etc...

Any help much appreciated

 

[KingPhoenix]

Just to add (sorry)

Im using Crystal Reports XI with a MS SQL 2000 Database

 

[lbass]

Are the fields otherwise the same in both databases? Instead of using a subreport, you might want to consider using a union statement to return only distinct records, where you use a formula for the database 2 job number so that it exactly corresponds to the database 1 job number. Go to database->database expert->add command and enter something like:

Select db1.`jobno`, db1.`field1`,db1.`field2`
From `db1` db1
Union
Select mid(db2.`jobno`,4,len(db2.`jobno`)-5), db2.`field1`,db2.`field2`
From `db2` db2

This should return only unique records.

-LB

 

[KingPhoenix]

Hi.... Thanks for that,

No the fields in both databases are completely different, 1 is our MIS system, and the other is our Time Planning system. The only true link between them is the job number, of which has been modified slightly in the planning database.

Also, i need it to not display the record, if it exists in the planning database, but if it doesnt then to display it so that i can dump the csv file to import it through the SQL DTS...

Not sure if that makes sense....

 

[lbass]

Still don't know which database is where, but if you are only using one table to suppress records in the other, then you could do something like the following. Place db1 in the subreport and place the subreport in a group header_a based on job number. Use the formula from the above command suggestion to convert the job number to match the other database. Link the subreport to the main report on this field/formula. In the subreport create a formula and place it on the subreport report footer:

whileprintingrecords;
shared stringvar jobno := {db1.jobno};

Then in the main report, go to the section expert->group header_b and details section->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar jobno;
mid({db2.jobno},4,len({db2.jobno})-5) = jobno

This assumes the conversion is in the main report where you are using db2. You can suppress all sections within the subreport, and also format GH_a to suppress blank section and format the subreport itself to suppress if blank.

-LB