We have a customer switching from C/SIDE to SQL server, and they have some dates in their system that SQL server doesn’t accept, anything before 1753. These dates are obviously typos (like year 0000). I wanted to make a tool to traverse all dates in the system and give me a report on where all the bad dates are, or just automatically change them. I’m able to find the table number and field number for all Dates in the system by using the Field table, but this does not give me access to the actual data. If I can change the table that a Record variable references on the fly I would be all set, but I don’t think that is possible. Any suggestions? [:)]
You are looking for Migrate.fob… It is in the conversion kit.
But there’s no way to do it right??? Just curious [:D]
quote:
If I can change the table that a Record variable references on the fly I would be all set, but I don’t think that is possible…
Search the C/SIDE Reference Guide on ‘recordref’.
Hi Matt, Yes there is a way to figure out those dates. You need to write a new report with 1) DataItem Table: Field 2) Set the TableView property DataItem to “SORTING(TableNo,No.) WHERE(Type=CONST(Date)” 3) three New Global Variables: RecRef RecordRef FieldRef1 FieldRef FieldValue Date 4) Copy the following code on the “Field - OnAfterGetRecord” of the report RecRef.OPEN(Field.TableNo); IF RecRef.FIND(’-’) THEN FieldRef1 := RecRef.FIELD(“No.”); FieldValue := FieldRef1.VALUE; IF (FieldValue > 01011900D) OR (FieldValue = 0D) THEN CurrReport.SKIP; 4) Put the fields on the section of the report to print. Also Print the FieldValue Variable in setion. 5) run the report, this will print only the fields with the table name and will tell you what is wrong with the dates. Hope this will help you. Naveen Jain
If you like, you can use my report here below. It will generate a file with the wrong dates. Its is an old one, but it works like a charm. If using on tables with MANY records remove the window.updates as it will run much faster. OBJECT Report 58577 Demo Data FindIllegalSqlDates { OBJECT-PROPERTIES { } PROPERTIES { ProcessingOnly=Ja; OnPreReport=BEGIN Window.OPEN('Tabel No. : #1########\' + 'Table Name : #2############################\' + 'Record No. : #3##################'); END; } DATAITEMS { { PROPERTIES { DataItemTable=Table2000000058; DataItemTableView=SORTING(Objekttype,Objekt-id) WHERE(Objekttype=CONST(Table)); OnAfterGetRecord=BEGIN RR.OPEN(AlleObjMedTitel."Objekt-id"); RR.SETVIEW(RR.GETVIEW + ' ORDER(1)'); Window.UPDATE(1,RR.NUMBER); Window.UPDATE(2,RR.NAME); END; ReqFilterFields=Objekt-id; } SECTIONS { } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table2000000026; DataItemTableView=SORTING(Nummer) WHERE(Nummer=FILTER(1..)); OnAfterGetRecord=BEGIN IF Nummer = 1 THEN BEGIN IF NOT RR.FIND('-') THEN CurrReport.BREAK; END ELSE BEGIN IF RR.NEXT() <> 1 THEN CurrReport.BREAK; END; Window.UPDATE(3,Nummer); END; } SECTIONS { } } { PROPERTIES { DataItemIndent=2; DataItemTable=Table2000000041; DataItemTableView=SORTING(TableNo,No.) WHERE(Type=FILTER(Date)); OnPreDataItem=BEGIN Field.SETRANGE(TableNo,RR.NUMBER); END; OnAfterGetRecord=VAR locDate@1000000000 : Date; i@1000000001 : Integer; BEGIN FR := RR.FIELD(Field."No."); IF FORMAT(FR.VALUE) <> '' THEN IF EVALUATE(locDate,FORMAT(FR.VALUE)) THEN IF locDate < datSQLLimit THEN BEGIN IF NOT booFileOpen THEN BEGIN fileOut.CREATE('C:\SQL_Error_' + DELCHR(COMPANYNAME,'=','/\') + '.txt'); fileOut.WRITEMODE(TRUE); fileOut.TEXTMODE(TRUE); booFileOpen := TRUE; END; fileOut.WRITE(STRSUBSTNO( 'Table: %1, %2\' + 'Record: %3\' + 'Field: %4, %5\' + 'Value: %6', RR.NUMBER,RR.NAME, RR.GETPOSITION(TRUE), FR.NUMBER,FR.NAME, FORMAT(locDate) )); fileOut.WRITE(''); END; END; } SECTIONS { } } } REQUESTFORM { PROPERTIES { Width=9020; Height=3410; } CONTROLS { { 1000000000;TextBox;3410 ;0 ;1700 ;440 ;CaptionML=DAN=SQL Date Limit; SourceExpr=datSQLLimit } { 1000000001;Label ;0 ;0 ;3300 ;440 ;ParentControl=1000000000 } } } CODE { VAR RR@1000000000 : RecordRef; FR@1000000001 : FieldRef; Window@1000000003 : Dialog; datSQLLimit@1000000002 : Date; fileOut@1000000004 : File; booFileOpen@1000000005 : Boolean; BEGIN END. } }
I did NOT include functionality for automatically changing the wrong dates into allowed dates, because the customers should check (and correct) each and every date manually. What’s your opinion on this?