Build a date from text

lgp

Hi,
I need to build a date from text so I can use it in an Aging calculation. The client uses part of the Lot No. to represent Manufacturing date, so that the first five characters of the Lot No. 08126-FG-082 equals the 126th day of 2008 (first 2 characters 08 = 2008, next 3 characters 126 = 126th day of the year). I need to print the Mfg. Date on the report, then do an aging calculation so I can show Days Aged in the next column. I have gotten as far as using the Evaluate function to convert the characters to date -

String:='08126';
EVALUATE(D,COPYSTR(String,3,3));
EVALUATE(M,COPYSTR(String,5,2));
EVALUATE(Y,COPYSTR(String,1,2));
MfgDate:= DMY2DATE(D,M,Y);

However, I am missing M, so this isn't working very well...any ideas? :?
Thanks!

kapamarou

I don't know if it'll work but here's some brainstorming:

You could use the Date virtual table. Filter the table to show records of type "Date".
Filter the records to show only the year's dates. Then do a DateRec.NEXT(126);


Could such a thing work :-k (It should...)

lgp

kapamarou wrote:

I don't know if it'll work but here's some brainstorming:

You could use the Date virtual table. Filter the table to show records of type "Date".
Filter the records to show only the year's dates. Then do a DateRec.NEXT(126);


Could such a thing work :-k (It should...)

Thanks - it worked - sort of! It works on the first record (I am using Item Ledger Entries in a report). When it moves to the next record, it does not seem to be starting at the first record in the filter when it finds the NEXT date, even though I have done a RESET on the record variable. Here is the code:

DateTable.RESET;
fromdate:=DMY2DATE(1,1,mfgyear);
todate:=DMY2DATE(31,12,mfgyear);
DateTable.SETFILTER("Period Type",'=Date');
DateTable.SETRANGE("Period Start",fromdate,todate);
DateTable.NEXT(Day);
MfgDate:=DateTable."Period Start";

For example, on the first record, Day=126, indicating the 126th day of 2008, so MfgDate=5/5/08. On the next record, Day=150, so MfgDate=5/29/08. However, the program sets MfgDate to 10/02/08. 10/02/08 is the 276th day of 2008, 150 + 126 =276, so what it is doing is starting at the last date it found and adding the next value.

Does RESET work differently with Virtual Tables?

Thanks again!

DenSter

How about this.... MyYear is an integer variable, and MyDateExpression is a text variable

IF NOT EVALUATE(MyYear,COPYSTR(LotNumber,1,2)) THEN // should evaluate the integer to the first two characters, so that would be 8 in this case
  MyYear := DATE2DMY(TODAY,3); // if that fails, use the year portion of the system date
MyDateExpression := '+' + COPYSTR(LotNumber,3,3) + 'D'; // building a date expression here, so you would end up with '+126D'
MfgDate := CALCDATE(MyDateExpression,DMY2DATE(1,1,MyYear)); // calculating the number of days from January 1 of the year

kapamarou

In your code you need to add a FINDFIRST after applying the filters and before calling next.

DenSter

What do you mean FINDFIRST and NEXT? I'm not using the Date table, I'm not setting any filters, I am concatenating a year number and a dateformula out of part of the Lot number, and calculating the MfgDate out of that.

kapamarou

DenSter wrote:

What do you mean FINDFIRST and NEXT?

I didn't clear it out... My post was referring to lgp's post.

Thanks - it worked - sort of! It works on the first record (I am using Item Ledger Entries in a report). When it moves to the next record, it does not seem to be starting at the first record in the filter when it finds the NEXT date, even though I have done a RESET on the record variable. Here is the code:

DateTable.RESET;
fromdate:=DMY2DATE(1,1,mfgyear);
todate:=DMY2DATE(31,12,mfgyear);
DateTable.SETFILTER("Period Type",'=Date');
DateTable.SETRANGE("Period Start",fromdate,todate);
DateTable.NEXT(Day);
MfgDate:=DateTable."Period Start";

For example, on the first record, Day=126, indicating the 126th day of 2008, so MfgDate=5/5/08. On the next record, Day=150, so MfgDate=5/29/08. However, the program sets MfgDate to 10/02/08. 10/02/08 is the 276th day of 2008, 150 + 126 =276, so what it is doing is starting at the last date it found and adding the next value.

Does RESET work differently with Virtual Tables?

DenSter

kapamarou wrote:

My post was referring to lgp's post.

Right that makes sense now

lgp

Hi All!
I had the brainstorm about the FINDFIRST last night - and that worked. Thanks so much for all the responses! DenSter - I tried your code but I couldn't retrieve the year from TODAY or WORKDATE.

DenSter

MyYear := DATE2DMY(TODAY,3); // from the system date
MyYear := DATE2DMY(WORKDATE,3); // from the workdate

MyYear is an integer. Check the syntax for DATE2DMY in the C/SIDE Reference Guide.