//AB/ Project Change Crystal Table Links //AB/ Object prj is a View_Project //AB/ Set Size to 230 325 //AB/ Set ProjectName to "Change Crystal Table Links" //AB/ Set ProjectFileName to "ChangeTableLinks.vw" //AB/ Set GenerateFileName to "NONAME" // Project Object Structure // oChangeCrystalTableLinksView is a dbView // oJoinTypeChangeButton is a Button // oCrystalReport is a cCrystal // Register all objects Register_Object oChangeCrystalTableLinksView Register_Object oCrystalReport Register_Object oJoinTypeChangeButton //AB-IgnoreStart Use dfClient.pkg Use Windows.pkg Use cCrystal.pkg //AB-IgnoreEnd DEFERRED_VIEW Activate_oChangeCrystalTableLinksView FOR ; ; Object oChangeCrystalTableLinksView is a dbView Set Border_Style to Border_Thick Set Icon to "Default.Ico" Set Label to "Change Join Type Demonstration View" Set Location to 2 2 Set Size to 45 231 //AB-DDOStart //AB-DDOEnd Object oJoinTypeChangeButton is a Button Set Label to "Change JoinType to crJTLeftOuter" Set Size to 14 140 Set Location to 5 5 //AB-StoreStart Procedure OnClick Send ChangeJoinType Of oCrystalReport crJTLeftOuter End_Procedure // OnClick //AB-StoreEnd End_Object // oJoinTypeChangeButton Object oCrystalReport is a cCrystal //AB/ Set Location to 4 203 Set psReportName to "Order1.rpt" //AB-StoreStart Struct tTableLink Variant vSourceTable Variant vDestinationTable Variant vSourceFields Variant vDestinationFields CRLinkJoinType iJoinType CRLinkLookUpType iLookupType Boolean bPartialMatchEnabled Short iIndexUsed End_Struct // The original tablelink class misses two properties. The are not // exposed because their flag is set to hidden by Crystal. Since we want // to have the tablelinks as close as what they were we expose the properties // in a subclass Class cCrystalTableLinkForChange Is A cCrystalTableLink // Gets table link partial-match enabled. Function ComPartialMatchEnabled Returns Boolean Handle hDispatchDriver Boolean retVal Get phDispatchDriver to hDispatchDriver Get InvokeComMethod of hDispatchDriver 659 OLE_VT_BOOL to retVal Function_Return retVal End_Function // ComPartialMatchEnabled // Gets table link IndexUsed Function ComIndexUsed Returns Short Handle hDispatchDriver Short retVal Get phDispatchDriver to hDispatchDriver Get InvokeComMethod of hDispatchDriver 660 OLE_VT_I2 to retVal Function_Return retVal End_Function // ComIndexUsed End_Class // cCrystalTableLinkForChange Procedure ChangeJoinType CRLinkJoinType iNewJoinType String sDataPath sReportName sDestinationFieldName Handle hoReport hoDatabase hoTableLinks hoTableLink Handle hoSourceFields hoSourceField hoDestinationFields hoDestinationField Handle hoWorkspace Variant vTableLinks vTableLink vSourceField vDestinationField vNewTableLink Variant[] vSourceFields vDestinationFields Integer iTableLinks iTableLink iSourceFieldItems iSourceFieldItem iPaperSize Boolean bReportOpened tTableLink[] ReportTableLink // Open the report Get OpenReport To bReportOpened If (Not (bReportOpened)) Begin Error DFERR_PROGRAM "Cannot open report" Procedure_Return End // Get the handle to the report object Get ReportObject To hoReport // Get the handle to the database (collection of tables) Get DatabaseObject Of hoReport To hoDatabase If (hoDatabase <= 0) Begin Error DFERR_PROGRAM "No database object" Send CloseReport Procedure_Return End // Create DataFlex objects for the COM objects we need to // change the table link Get Create U_cCrystalTableLinks To hoTableLinks Get Create U_cCrystalTableLinkForChange To hoTableLink Get Create U_cCrystalDatabaseFieldDefinitions To hoSourceFields Get Create U_cCrystalDatabaseFieldDefinition To hoSourceField Get Create U_cCrystalDatabaseFieldDefinitions To hoDestinationFields Get Create U_cCrystalDatabaseFieldDefinition To hoDestinationField // Get and use the dispatch ID for the tablelinks Get ComLinks Of hoDatabase To vTableLinks Set pvComObject Of hoTableLinks To vTableLinks // Get the number of tablelinks Get ComCount Of hoTableLinks To iTableLinks // Retrieve the tablelinks and store the information in an array // We do this because we want (need) to create the new links in the // same order as they had before. Note that the 0-element of the array // is not used. For iTableLink From 1 To iTableLinks Get ComItem Of hoTableLinks iTableLink To vTableLink Set pvComObject Of hoTableLink To vTableLink Get ComSourceTable Of hoTableLink To ReportTableLink[iTableLink].vSourceTable Get ComDestinationTable Of hoTableLink To ReportTableLink[iTableLink].vDestinationTable Get ComJoinType Of hoTableLink To ReportTableLink[iTableLink].iJoinType Get ComLookupType Of hoTableLink To ReportTableLink[iTableLink].iLookupType Get ComSourceFields Of hoTableLink To ReportTableLink[iTableLink].vSourceFields Get ComDestinationFields Of hoTableLink To ReportTableLink[iTableLink].vDestinationFields Get ComIndexUsed Of hoTableLink To ReportTableLink[iTableLink].iIndexUsed Get ComPartialMatchEnabled Of hoTableLink To ReportTableLink[iTableLink].bPartialMatchEnabled Loop // Remove all tablelinks in reverse order. When deleting from 1 to n the // tablelinks are renumbered and after deleting 1 link the N-th linknumber becomes // invalid Move iTableLinks To iTableLink While (iTableLink > 0) Send ComDelete Of hoTableLinks iTableLink Decrement iTableLink Loop // Enumerate the array and in particular find out what the source and destination fields // for each link are. The Crystal RDC documentation for ComAdd (TableLink class) is very vague; // The parameters are called srcFields and destFields but if you pass the collection retrieved // above Crystal throws an exception in the game. Calling ComAdd with the individual members of // SourceFields and DestinationFields seems to work but as soon as your tablelink consists of // two source fields linking to two destinationfields in the same destionation table the first // link is lost. Via trial and error I found out that you need to pass a variant with an array // of dispatch ids. For iTableLink From 1 To iTableLinks Set pvComObject Of hoSourceFields To ReportTableLink[iTableLink].vSourceFields Set pvComObject Of hoDestinationFields To ReportTableLink[iTableLink].vDestinationFields // Enumerate the sourcefield dispatch ids and place them in an array Get ComCount Of hoSourceFields To iSourceFieldItems For iSourceFieldItem From 1 To iSourceFieldItems Get ComItem Of hoSourceFields iSourceFieldItem To vSourceFields[iSourceFieldItem - 1] Get ComItem Of hoDestinationFields iSourceFieldItem To vDestinationFields[iSourceFieldItem - 1] Loop // Add a new tablelink Get ComAdd Of hoTableLinks ReportTableLink[iTableLink].vSourceTable ReportTableLink[iTableLink].vDestinationTable vSourceFields vDestinationFields iNewJoinType ReportTableLink[iTableLink].iLookupType ReportTableLink[iTableLink].bPartialMatchEnabled ReportTableLink[iTableLink].iIndexUsed To vNewTableLink // Reset the array to 0 elements Move (ResizeArray (vSourceFields, 0)) To vSourceFields Move (ResizeArray (vDestinationFields, 0)) To vDestinationFields Loop // Destroy the dynamically created DataFlex objects Send Destroy Of hoSourceFields Send Destroy Of hoSourceField Send Destroy Of hoDestinationFields Send Destroy Of hoDestinationField Send Destroy Of hoTableLink Send Destroy Of hoTableLinks // Get the current paper size (A4, Letter etc) and set the property to // the retrieved size. If we do not do that the SaveAs will default to the // papersize on the users machine and it may destroy your layout Get ComPaperSize Of hoReport To iPaperSize Set ComPaperSize Of hoReport To iPaperSize // Retrieve the first directory from the workspace datapath Get psReportName Of hoReport To sReportName Get phoWorkspace Of ghoApplication To hoWorkspace Get psDataPath Of hoWorkspace To sDataPath Get PathAtIndex Of hoWorkspace sDataPath 1 To sDataPath Move (Trim (sDataPath)) To sDataPath If (Right (sDataPath, 1) <> Sysconf (SYSCONF_DIR_SEPARATOR)) Begin Move (sDataPath + Sysconf (SYSCONF_DIR_SEPARATOR)) To sDataPath End // Store Report in a new directory Send ComSaveAs To hoReport (sDataPath + 'ChangedReports\' + sReportName) crDefaultFileFormat // Close the report Send CloseReport End_Procedure // ChangeJoinType //AB-StoreEnd End_Object // oCrystalReport CD_End_Object // oChangeCrystalTableLinksView //AB/ End_Object // prj