|
I have an existing report that takes the Part ID from an Excel spreadsheet from an end of month physical inventory scan and compares it to the Part ID in a SQL database. If the two match, it will list the Part ID, the inventory totals from both the spreadsheet and the database and subtract one from the other to give a difference, if any.
The issue I have is twofold.
First, because of the way the barcode scan lists the data in the barcode, and the way the Excel formulas separate out the Part ID, some of the Part IDs in the Excel spreadsheet will not have a corresponding match in the SQL database.
Second, depending on the accuracy of the person entering the data, when the Part ID is entered into the SQL database, it may not have a part type selected.
When either of these issues happen, the report leaves the Excel Part ID off the report.
I need to have ALL the Part IDs from the Excel spreadsheet and their totals listed on the report and those where no match is found should have a note that there is no match found in the SQL database. This will allow us, for the first issue, to then edit the barcode data in the spreadsheet so the formula returns the correct part number. And for the second issue, we can correct the Part ID entry in the SQL database by selecting the part type.
Ideally, I would like to have the unmatched Part IDs listed alphanumerically among the matched IDs to make them easier to find in the spreadsheet, but the Selection formula of the Main Report precludes this. So, I have created a subreport with no links to the Main Report and a Selection formula of:
{Excel_Spreadsheet.PartID} <> {SQL_DB.PartID}
I have {Excel_Spreadsheet.PartID} linked by Left Outer Join to {SQL_DB.PartID} in the Database Expert.
Unfortunately, it returns absolutely no results even though there are at least 12 Excel Part IDs that do not match.
I changed the Link Type (Database Expert > Links > right-click the link and select Link Options) to Not Equal, !=. This just listed ALL the Excel Part IDs.
I removed the Record Selection formula and it started returning tens of thousands of pages all with the same Part ID before I canceled the preview.
Does anyone have any ideas on how to get this to list ONLY the unmatched parts? Thank you!
|
|
it should be a left outer join that in sql would look like:
select * from excel join sql on sql.partid = excel.partid
Crystal has some odd ideas, as part of the link is to enforce joins or not, which I don't really understand, as I do my selects in stored procedures (which won't work in this case). DBlank seems to understands, and from what I have gleaned, if you place a field from both tables, the link criteria should be enforced.
I don't know what is in you selection formula, as that could be removing the records that you are after.
for that matter, and I could be completely wrong as I don't know your data, there shouldn't be a selection criteria as you want all records from the excel file and the matching values in the sql database. if you put any conditions on the sql database, you are probably removing values that you seek.
depending on what part/how the id is not matching, you might be able to 'tune' the selection criteria so that the sql id more closely resembles the excel id.
HTH
|
IP Logged
