Dateformula

Madhan · 2006-04-06

Great Day All,

How to convert a dateformula field to text or code.

For eg:

1. I am having a dateformula field of value 12M. I want the output as
12 Months.

2. If the value of the dateformula field is 12D. I want the output as
12 Days.

3. If the value of the dateformula field is 12Y. I want the output as
12 Years.

can any one help me.

jhoek · 2006-04-06

As far as I know, there's no possibility to do that in the run-time - I'm afraid you'll have to write your own parsing/formatting logic. Please take into account that the date formula's you use in your examples are relatively simple: more complex formula's could potentially be quite difficult to describe in normal language.

Good luck!

kriki · 2006-04-06

You can use FORMAT to put them in a string so it is easier to parse them, but remember that the characters can be different in different languages.
Something a lot of people seem to forget (even the Navision W1-developers).

Madhan · 2006-04-06

Hi kriki,

Using format function i have converted my dateformula field to string. Then, after that how to proceed further to reach my output what i want?

can you guide me....

Madhan · 2006-04-06

Thanx for you clue. I got the solutions......Thax a lot

kriki · 2006-04-06

A few examples of possible dateformulas (in english):
-12D = minus 12 days
+4M = plus 4 months
30D = 30 days
2W = 2 weeks
CM+10D = Current month plus 10 days
CM+1M = Current month plus one month
CQ+1M+20D=Current quarter plus one month plus 20 days
CW+1W = Current week plus one week
D15 = On the 15th of each month
M12 = on the 12th month

So we have to do a lot. I will not write all code (I'll also use some pseudocode).
And I didn't test anything, but at least you will have a good start

txtString := FORMAT(TheDateFormula);
txtResult := '';
WHILE txtString <> '' DO BEGIN
  txtToken := GetToken();
  CASE txtToken OF
    'CM': txtResult := txtResult + ' ' + 'Current Month';
    'CQ': txtResult := txtResult + ' ' + 'Current Quarter';
    ...
    '+': txtResult := txtResult + ' ' + 'plus';
    '-': txtResult := txtResult + ' ' + 'minus';
    'D': txtResult := txtResult + ' ' + 'day(s)';
    'M': txtResult := txtResult + ' ' + 'month(s)';
    ELSE BEGIN
      // remaining are 1,12,123,D1,D15,M12
      IF COPYSTR(txtToken,1,1) IN ['0'..'9'] THEN
        txtResult := txtResult + ' ' + txtToken
      ELSE BEGIN
        // remaining are D1,D15,M12
        IF COPYSTR(txtToken,1,1) = 'D' THEN
          txtResult := txtResult + ' ' + 
            STRSUBSTNO('On the %1th of each month',COPYSTR(txtToken,2));
        ELSE IF COPYSTR(txtToken,1,1) = 'D' THEN
          txtResult := txtResult + ' ' + 
            STRSUBSTNO('on the %1th month',COPYSTR(txtToken,2));
        ELSE
          ERROR('Unknown Token:%1',txtToken);
      END;
    END;
  END;
END;

Function "GetToken":
This function gets a substring and returns it.
The substring can exist of 1 or more chars. The chars must belong together.
Tokens can be:
CM,CQ,CW,-,+,1,12,123,D,M,Y,Q,D15,D1,M12

When you have the token, the chars must be removed from txtString.
Some code:

IF COPYSTR(txtString,1,2) IN ['CM','CQ','CW'] THEN BEGIN
  OtxtReturnValue := COPYSTR(txtString,1,2);
  txtString := COPYSTR(txtString,3);
  EXIT;
END;
IF COPYSTR(txtString,1) IN ['+','-'] THEN BEGIN
  OtxtReturnValue := COPYSTR(txtString,1,1);
  txtString := COPYSTR(txtString,2);
  EXIT;
END;
IF COPYSTR(txtString,1) IN ['0'..'9'] THEN BEGIN
  OtxtReturnValue := take the complete string of numbers
  txtString := COPYSTR(txtString,...);
  EXIT;
END;
IF COPYSTR(txtString,1) IN ['D','M','Y','Q'] THEN BEGIN
  txtHelp := COPYSTR(txtString,1,1);
  txtString := COPYSTR(txtString,2);
  IF NOT(COPYSTR(txtString,1) IN ['0'..'9']) THEN BEGIN
    // I only have D,M,...
    OtxtReturnValue := txtHelp;
  END
  ELSE BEGIN
    // I have something like D15 or M12 or D1
    txtHelp2 := GetToken();  // We're going recursive here and have something like 15,12,1 as return-value
    OtxtReturnValue := txtHelp + txtHelp2; // so our returnvalue will be D15 or M12 or D1
  END;
  EXIT;
END

ERROR('Unable to intpret the following string:%1',txtString);

Dateformula

Comments

Categories