×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

My First Formula

My First Formula

My First Formula

(OP)
Hi all,

I'm doing some maintenance on a Notes database, and I have no experience with the system.  I've gotten almost everything figured out for this assignment except for one thing:

I have a formula that should return a Part Number concatenated with a date code (don't ask why... I'm dealing with a very ugly database here) like so:

102381990013

Which is read as follows:

Part 10238199 run on Julian date 001 (Jan 1) 2003.

I can access the Part Number as a string and the date as a standard date.

Here's my formula so far (doesn't work):
TBPARTNUM+(TBDATE - @Date(@Year(TBDATE);1;1))+@right(@Year(TBDATE);1)

Can anyone help me out?

TIA
Ben

RE: My First Formula

Maybe something like this ...

list := 0:31:28:31:30:31:30:31:31:30:31:30 ;
priorMonths := @Subset( list ; @Month(TBDATE) ) ;
julianDay := @Sum( priorMonths : @Day(TBDATE) ) ;
yearDigit := @Right( @Text(@Year(TBDATE)) ; 1 ) ;
partDate := @Text(TBPARTNUM) + @Text(julianDay) + yearDigit ;
partDate

Dale

Find me @ onlinecorporatesoftware.com

RE: My First Formula

(OP)
Exactly what I needed; thanks!

RE: My First Formula

Actually the julian day should be zero filled according to your spec.

partDate := @Text(TBPARTNUM) + @Right("000"+@Text(julianDay);3) + yearDigit ;

and if TBPARTNUM is numeric then you might require the same sort of processing for it too.

Cheers

Find me @ onlinecorporatesoftware.com

RE: My First Formula

(OP)
Thanks so much for your help, Dale,

I have just one more question.  I've been trying to add a leap year calculation to the field:

tmpyr:=@year(TBDATE);
If @modulo(tmpyr;4)=0 Then
   If @modulo(tmpyr;100) != 0 or @modulo(tmpyr;400) = 0 Then
     list := 0:31:29:31:30:31:30:31:31:30:31:30 ;
   else
     list := 0:31:28:31:30:31:30:31:31:30:31:30 ;
   End if
else
   list := 0:31:28:31:30:31:30:31:31:30:31:30 ;
End if

I can't get these comparisons to work.  I got the syntax from Domino Designer help; it says something about an operator or " expected at the = sign in the first "if" statement.  Can you set me straight?

Ben

RE: My First Formula

(OP)
Never mind; I got it!  I was using the LotusScript "If" in a formula... once I straightened that out, it works fine.  Thanks again for your help, Dale.

Ben

RE: My First Formula

In this case (the leap year code) check for what happens when @modulo(tmpyr;100) is not equal to zero and what happens when it is. I think you will find that or is not a valid formula statement. To perform a logical OR in formula use the | vertical bar. I still occasionally (usually late at night) confuse the LotusScript use of "+" and "&" with the formula usage and I've been doing both for a longggg time.

Find me @ onlinecorporatesoftware.com

RE: My First Formula

(OP)
Absolutely correct, Dale,

I had a little trouble with the "or"; but I eventually got the "|".  Here is the solution that I am using:

tmpyr:=@Year(TBDATE);

 list:=@If ((@Modulo(tmpyr ;4)=0) & (@Modulo(tmpyr;100) != 0 | @Modulo(tmpyr;400) = 0) ; 0:31:29:31:30:31:30:31:31:30:31:30 ; 0:31:28:31:30:31:30:31:31:30:31:30);

priorMonths := @Subset( list ; @Month(TBDATE) ) ;
julianDay := @Sum( priorMonths : @Day(TBDATE) ) ;
yearDigit := @Right( @Text(@Year(TBDATE)) ; 1 ) ;
partDate := @Text(TBPARTNUM) + @Right("000"+@Text(julianDay);3) + yearDigit ;
partDate

The algorithm I used for leap years was found on a website somewhere; I Googled for "Leap Year Algorithm".

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close