Solved

2 fields from different forms - one calculated - in 1 view? HemanthaKumar

Posted on 2004-03-30
9
216 Views
Last Modified: 2013-12-18
Hello,

I have 2 different forms (lets call em formA and formB), formA has a field name 'datecomplete' formB has a field named 'edcomplete'. Both are date fields.

I need to display the date in a view - if the form is A then I just need to display the date in the 'datecomplete' field. If the the form is B I need to display 'edcomplete' + 10 WORKING days in the view.

I tryed something along the lines of:

@IF(form = "FormA"; datecomplete; form = "FormB"; @Adjust(edcomplete; 0; 0; 10; 0; 0; 0;); "Unknown")

But that doesnt seem to work and cant account for working days (no weekends).

Using Notes R5...Since qwaletee snagged the points earlier and this question is based on a proposed answer from HemanthaKumar, I'm looking in his direction for a solution - but will take anything that works.

Thanks in advance!
-jim
0
Comment
Question by:yim11
  • 5
  • 2
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10718046
You have to work on number of working days .. What is this based on ?

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10718103
ok I get it now...


futuredates := @Adjust(edcomplete; 0; 0; 10; 0; 0; 0);
t := @Explode(@TextToTime(@Text(edcomplete) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Right(@Replace( concat; t; ""); @Length(concat) -1 )

@IF(form = "FormA"; datecomplete; form = "FormB";dts; "Unknown" )

THis should work for you...
0
 

Author Comment

by:yim11
ID: 10718123
Its a development datebase, one office uses formA (and has a date complete field) another office uses formB (but I have to calculate date complete). I have code that works for working days [see below] but not sure how to integrate it.

Let me know what other info I need to provide.

Thanks,
-jim

---code---

temp := (@Date(EDComplete) - @Date(DateToED)) / 86400;

weekend_days := @Integer((temp / 7)) * 2;

wkday := @Weekday(DateToED);

adjust := @If(((wkday + @Modulo(temp; 7) - 7) > 0); 2; 0);

working_days := temp - (weekend_days + adjust) +1;

@If(EDComplete = ""; 0; working_days)

---EndCode--
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10718171
or more appropriately,

futuredates := @Adjust(edcomplete; 0; 0; 15; 0; 0; 0);
t := @Explode(@TextToTime(@Text(edcomplete) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Subset( @Trim(@Right(@Replace( concat; t; ""); @Length(concat) -1 )); 10);

@IF(form = "FormA"; datecomplete; form = "FormB";dts; "Unknown" )
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10718249
Then try this

@if(form = "FormA"; datecomplete; Form = "FormB"; ""; @Return("Unknown"));

temp := (@Date(EDComplete) - @Date(DateToED)) / 86400;
weekend_days := @Integer((temp / 7)) * 2;
wkday := @Weekday(DateToED);
adjust := @If(((wkday + @Modulo(temp; 7) - 7) > 0); 2; 0);
working_days := temp - (weekend_days + adjust) +1;
@If(EDComplete = ""; 0; working_days)
0
 

Author Comment

by:yim11
ID: 10718407
This code:
---
futuredates := @Adjust(edcomplete; 0; 0; 15; 0; 0; 0);
t := @Explode(@TextToTime(@Text(edcomplete) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Subset( @Trim(@Right(@Replace( concat; t; ""); @Length(concat) -1 )); 10);

@IF(form = "FormA"; datecomplete; form = "FormB";dts; "Unknown" )
---
Marks all entries in the view as Unknown.

The other code:
---
@if(form = "FormA"; datecomplete; Form = "FormB"; ""; @Return("Unknown"));

temp := (@Date(EDComplete) - @Date(DateToED)) / 86400;
weekend_days := @Integer((temp / 7)) * 2;
wkday := @Weekday(DateToED);
adjust := @If(((wkday + @Modulo(temp; 7) - 7) > 0); 2; 0);
working_days := temp - (weekend_days + adjust) +1;
@If(EDComplete = ""; 0; working_days)
---
Empties the view, all entries are gone.

So I'm thinking that we may need to tweak the first code some<?>

Here is the EXACT code I used (changed var and form names to actual names being used):
---
futuredates := @Adjust(DatetoED; 0; 0; 15; 0; 0; 0);
t := @Explode(@TextToTime(@Text(DatetoED) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Subset( @Trim(@Right(@Replace( concat; t; ""); @Length(concat) -1 )); 10);

@IF(form = "Sample Login"; DateDue; form = "AAEDFORM";dts; "Unknown" )
---
I think the above code may be easier to work with since in most cases EdComplete field will be blank.

What information do you need from me at this point?

Thanks!
-jim
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 200 total points
ID: 10718922
My first formula gives list of 10 business dates from the DatetoED.. Is this what you want or do you want the 10th business day from the datetoED ?

Both can be done from the formula that I gave.. But it giving 10 "unknown" values is something that I din't expect.

We have to modify the formula so that unnecessarily we don't calculate the date calculation

@IF(form = "Sample Login"; @Return(DateDue); form = "AAEDFORM"; PERFORMBELOWCALC ; @Return("Unknown") );
futuredates := @Adjust(DatetoED; 0; 0; 15; 0; 0; 0);
t := @Explode(@TextToTime(@Text(DatetoED) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Subset( @Trim(@Right(@Replace( concat; t; ""); @Length(concat) -1 )); 10);
dts

If you don't want 10 business days but 10th business day, just use this

@IF(form = "Sample Login"; @Return(DateDue); form = "AAEDFORM"; PERFORMBELOWCALC ; @Return("Unknown") );
futuredates := @Adjust(DatetoED; 0; 0; 15; 0; 0; 0);
t := @Explode(@TextToTime(@Text(DatetoED) + "-" + @Text(futuredates)) );
weekdays := @Replace( @Text(@Weekday(@TextToTime(t) )); "1" : "7"; "");
concat := weekdays + t;
dts := @Subset(@Subset( @Trim(@Right(@Replace( concat; t; ""); @Length(concat) -1 )); 10);-1);
dts

Sincerely, I would keep all this calculation on the form so that column doesn't have to do this, inturn enhancing the speed of the view

Your call.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Lotus Notes 8.5 1 158
Domino not switching to TLS 1.0 4 802
IBM Notes 9 crashed when open any attachment file 16 515
lotus notes, exchange 7 111
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question