?
Solved

Too many Arguements foe @ Function

Posted on 2003-12-02
41
Medium Priority
?
409 Views
Last Modified: 2013-12-18
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );


@If( @IsMember(@Text(CurrentDates); @Text(Booked))
 ; @Failure( "Conflict"; "Already you have booked vacation on these dates";@Success))This is the line

I have tried IsNotMEmber etc...

Ineed this in a validation formula or I nee the following to work in a button

It failed in abutton in that when i pushed the Submit button nothing happened

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates"); "");


@If(@Prompt([YESNO]; "Submit Request?"; "Are you sure you wish to Submit this request")=1; ""; @Return (""));

@If(Approver_2="";@Do(@Prompt([OK];"Warrning";"You must choose a final approver to send your request!");@Return(""));"");
@If(Requestor="";@Do(@Prompt([OK];"Warrning";"You must enter your Email address for your request!");@Return(""));"");
@If(Type="";@Do(@Prompt([OK];"Warrning";"You must enter your leave type for your request!");@Return(""));"");
@If(StartDate>EndDate;@Do(@Prompt([OK];"Warrning";"Please enter an end date greater or equal to the starting date");@Return(""));"");

@Command ( [EditDocument] ; "1" ) ;
@SetField("SaveOptions";"1");

REM "For updating history";
his_info:=("Submitted by:  ")+ @Name([CN]; @UserName) + " -- " + @Text(@Month(@Today))+"/"+@Text(@Day(@Today))+"/"+@Text(@Year(@Today));
@SetField ("History";his_info);@NewLine;

@SetField ("Reviewer";Approver_1) ;
@SetField("ApprovalStatus" ; "Processing" );
@If(Approver_1="";@SetField("Status_Flag";"3");@SetField("Status_Flag";"2"));
@SetField("ApproverComments_1";"");
@SetField("ApproverComments_2";"");
@SetField("Submit_Date";@Now);
@Command([FileSave]);

ApproverList:=@If(Approver_1="";Approver_2;Approver_1);
MailTo:= ApproverList;
MailCc:= "";
MailSubject:=SubmitMailSubject+ "# "+@Trim(DocNum);
MailRemark:=SubmitMailRemark+@NewLine+"Best Regards";
notify := " A notification will be sent to " + @Name([CN];MailTo) + " ." ;
@Do(@Prompt([OK];"Send Notification";notify);@MailSend(MailTo;MailCc;"";MailSubject;MailRemark;"";[IncludeDoclink]));
@Command([FileSave]);
@Do(@SetField("SaveOptions"; "0");@PostedCommand([FileCloseWindow]))

0
Comment
Question by:adspmo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 15
  • 5
  • +1
41 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9858829
The error simply means that you have missed to end either with quotes or ;

Let me look at it and post the corrected formula

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9858858
I see no errors in the formula,

The @Return statement is blocking you from proceeding further.. If you want you can raise some error instead of script being quite.. replace first 4 lines of code to this one


temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(@Prompt([ok]; "Start Date"; "No Start Dates found in this range")); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(@Prompt([ok]; "Start Date"; "No Start Dates found in this range")); temp1);
0
 

Author Comment

by:adspmo
ID: 9858859
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If(@IsMember(@Text(CurrentDates); @Text(Booked))
 ; @Failure( "Conflict    Already you have booked vacation on these dates");@Success))

Works However when the validation runs it closes the document, I s this the Execution of the Submit button(see Above)

This doesn't have to be a validation formula, If I can get it to work in the submit button would be OK as well
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9858903
Closing document automatically ?? It should not.

When you try to save this validation will not allow the user to save but it can be closed without saving.. so is that a problem ??
0
 

Author Comment

by:adspmo
ID: 9859272
Hi

I have it working in the Submit button

I need to get the formula to stop running if there is a problem

Maybe an @Return I am playing with it have not got it to work yet

@If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates"); "");


0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9859423
Ok, use this formula in place of prompting you have to stop execution of the code beyond that error

@If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @RETURN(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); "");

0
 

Author Comment

by:adspmo
ID: 9860835
This is not working



It Is in a Submit button

See above

It will not submit

If the dates are booked I want it to stop the submission,else sen her thru

James
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9860914
You said, It will not submit... meaning it is raising error saying the dates are booked ?

@RETURN(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); in the above formula will stop execution providing the prompt.. does it happen ?

Or does it raise error and the prompt you to submit ?  What actually happens
0
 

Author Comment

by:adspmo
ID: 9861239
It does nothing

It sits there
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9861850
Paste the modified code here.
0
 

Author Comment

by:adspmo
ID: 9861885
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @RETURN(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); "");


@If(@Prompt([YESNO]; "Submit Request?"; "Are you sure you wish to Submit this request")=1; ""; @Return (""));

@If(Approver_2="";@Do(@Prompt([OK];"Warrning";"You must choose a final approver to send your request!");@Return(""));"");
@If(Requestor="";@Do(@Prompt([OK];"Warrning";"You must enter your Email address for your request!");@Return(""));"");
@If(Type="";@Do(@Prompt([OK];"Warrning";"You must enter your leave type for your request!");@Return(""));"");
@If(StartDate>EndDate;@Do(@Prompt([OK];"Warrning";"Please enter an end date greater or equal to the starting date");@Return(""));"");

@Command ( [EditDocument] ; "1" ) ;
@SetField("SaveOptions";"1");

REM "For updating history";
his_info:=("Submitted by:  ")+ @Name([CN]; @UserName) + " -- " + @Text(@Month(@Today))+"/"+@Text(@Day(@Today))+"/"+@Text(@Year(@Today));
@SetField ("History";his_info);@NewLine;

@SetField ("Reviewer";Approver_1) ;
@SetField("ApprovalStatus" ; "Processing" );
@If(Approver_1="";@SetField("Status_Flag";"3");@SetField("Status_Flag";"2"));
@SetField("ApproverComments_1";"");
@SetField("ApproverComments_2";"");
@SetField("Submit_Date";@Now);
@Command([FileSave]);

ApproverList:=@If(Approver_1="";Approver_2;Approver_1);
MailTo:= ApproverList;
MailCc:= "";
MailSubject:=SubmitMailSubject+ "# "+@Trim(DocNum);
MailRemark:=SubmitMailRemark+@NewLine+"Best Regards";
notify := " A notification will be sent to " + @Name([CN];MailTo) + " ." ;
@Do(@Prompt([OK];"Send Notification";notify);@MailSend(MailTo;MailCc;"";MailSubject;MailRemark;"";[IncludeDoclink]));
@Command([FileSave]);
@Do(@SetField("SaveOptions"; "0");@PostedCommand([FileCloseWindow]))
0
 
LVL 24

Assisted Solution

by:HemanthaKumar
HemanthaKumar earned 800 total points
ID: 9861974
Well I don't see my modification described at Date: 12/02/2003 10:24AM EST

Replace these lines

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);

with

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(@Prompt([ok]; "Start Date"; "No Start Dates data found !")); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(@Prompt([ok]; "End Date"; "No End Dates data found !")); temp1);
0
 

Author Comment

by:adspmo
ID: 9862044
It does not submit
It pops up and says No Start date found and then thats it
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9862106
Try this one..

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); ""; temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); ""; temp1);
Booked := @if( sDate = "" | eDate = ""; ""; @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) ) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@if( Booked = ""; ""; @If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @RETURN(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); "") );

@If(@Prompt([YESNO]; "Submit Request?"; "Are you sure you wish to Submit this request")=1; ""; @Return (""));

@If(Approver_2="";@Do(@Prompt([OK];"Warrning";"You must choose a final approver to send your request!");@Return(""));"");
@If(Requestor="";@Do(@Prompt([OK];"Warrning";"You must enter your Email address for your request!");@Return(""));"");
@If(Type="";@Do(@Prompt([OK];"Warrning";"You must enter your leave type for your request!");@Return(""));"");
@If(StartDate>EndDate;@Do(@Prompt([OK];"Warrning";"Please enter an end date greater or equal to the starting date");@Return(""));"");

@Command ( [EditDocument] ; "1" ) ;
@SetField("SaveOptions";"1");

REM "For updating history";
his_info:=("Submitted by:  ")+ @Name([CN]; @UserName) + " -- " + @Text(@Month(@Today))+"/"+@Text(@Day(@Today))+"/"+@Text(@Year(@Today));
@SetField ("History";his_info);@NewLine;

@SetField ("Reviewer";Approver_1) ;
@SetField("ApprovalStatus" ; "Processing" );
@If(Approver_1="";@SetField("Status_Flag";"3");@SetField("Status_Flag";"2"));
@SetField("ApproverComments_1";"");
@SetField("ApproverComments_2";"");
@SetField("Submit_Date";@Now);
@Command([FileSave]);

ApproverList:=@If(Approver_1="";Approver_2;Approver_1);
MailTo:= ApproverList;
MailCc:= "";
MailSubject:=SubmitMailSubject+ "# "+@Trim(DocNum);
MailRemark:=SubmitMailRemark+@NewLine+"Best Regards";
notify := " A notification will be sent to " + @Name([CN];MailTo) + " ." ;
@Do(@Prompt([OK];"Send Notification";notify);@MailSend(MailTo;MailCc;"";MailSubject;MailRemark;"";[IncludeDoclink]));
@Command([FileSave]);
@Do(@SetField("SaveOptions"; "0");@PostedCommand([FileCloseWindow]))
0
 

Author Comment

by:adspmo
ID: 9862227
This one doesn't check the fields

So I can submit another with the same date
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9862338
Here's yoru submitr problem:

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);


The sdate and edate say, "If the lookup failed, abort via @Return(0)."  The logic is that we are validating the current dates against the existing dates.  If there is already a match, we want a failure.  If there is no match, we want success.  If there is nothing to match against, then we ave no matches, so we should have success.  If the lookups fail, we assume that there are no matches, and hence, we are good to go.

Now, the "abort" bit makes sense in the validation formula, since if there is nothing to lookup, we should "abort" with "success" --  @Success is the same as zero, so @Return(0) in a validation is @Return(@Success)m which means instantly let the validation "pass successfully."

In the case of your submit button, what you want instead is that if the dbLookups fail, just jump past the validation checks.  So, remove the @Return(0), and change it to a null string -- "" -- without the @Return.  I believe that will be enough!
0
 

Author Comment

by:adspmo
ID: 9862619
This is not working

It does not check the view it just submits

Are we better off doing this as a field validation

James
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9863660
adspmo,
> This is not working
What doesn't work?  My suggested revision?

Usually, I DO NOT suggest e-Mailing a database, because that tends to lock out some Experts, and make the final answer unavailable.  However, if this back-and-forth continues much more, it will go beyond ridiculous.

If James and Hemantha agree, I suggest that James create a dummy database containing the form and the view used in the calculations, and some sampe data.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 9864777
I agree. go ahead Qwaletee.
0
 

Author Comment

by:adspmo
ID: 9866438
Sure thing


I can do this today
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9866892
Well the final formula removes those returns and executes the formula !

James, what prompts do you see while submit ?
0
 

Author Comment

by:adspmo
ID: 9867119

I get no prompts beyond Are you sure you wish to Submit this request and  A notification will be sent to

Here is what I have in the current button:

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); ""; temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); ""; temp1);
Booked := @If( sDate = "" | eDate = ""; ""; @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) ) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( Booked = ""; ""; @If( @IsMember(@Text(CurrentDates); @Text(Booked))
; @Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates"); "") );
0
 

Author Comment

by:adspmo
ID: 9867185
I added @Prompt([OK];"";sDate);@Prompt([OK];"";eDate); and they come up blank

In the test I chose December 8th abd the doc was approved I entered another request including Dec 8 and as i said it did not return the value in the prompt
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9867243
I don't know how you are checking this but the booked variable will be "" if either start or enddates are ""..

Just prompt out the dates and see the lookup is giving out the right values ?

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); ""; temp);

@Prompt([ok]; "SDate"; @Implode(@Text(SDate)) );

temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); ""; temp1);

@Prompt([ok]; "EDate"; @Implode(@Text(eDate)) );

Booked := @If( sDate = "" | eDate = ""; ""; @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) ) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( Booked = ""; ""; @If( @IsMember(@Text(CurrentDates); @Text(Booked))
; @Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates"); "") );
0
 

Author Comment

by:adspmo
ID: 9868355
The prompts come up empty, blank, nada, zippo etc....

I tried prompting on the temp temp1 as well

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9868464
That should be some dates isn't it ?
0
 

Author Comment

by:adspmo
ID: 9868603
YEs ther should

I checked the view FullName and it does contain dates
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9868729
Also use @trim(@text on dblookup to avoid blanks
0
 

Author Comment

by:adspmo
ID: 9868782
The lookup is not working

I am testing a lookup in a button and it is not working
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9868813
Make sure that you are looking at correct view and for correct fields and the key passed is valid
0
 

Author Comment

by:adspmo
ID: 9868855
The view alias is FullName

The fields ar StartDate and EndDate

The key is a field NamesFull

In the view I am using Field for Selection

I have triple checked this

James
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9868946
Now you are confusing me..

"I am testing a lookup in a button and it is not working " .. what does this mean. Are you getting errors or unexpected data
0
 

Author Comment

by:adspmo
ID: 9870140
I am testing just the lookup for one thing

@DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate"); and it does not work
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9870302
so you are getting error !

Do a DBCOlumn

@DBColumn( "Notes":"NOCache"; @DBName; "FullName"; 2);
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9872164
Once more, send a minimal database with some samples...
0
 

Author Comment

by:adspmo
ID: 9874515
Hi Qwaletee

I have sent you a copy..

I forgot to mention it is in the Submit buttom of the Leave_fm  form that I would like this ,or a Input Validation on the End Date or StartDate

THanx

James
0
 

Author Comment

by:adspmo
ID: 9883685
Did you get the db
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9884427
Yes, I got it, but had some critical things to take care of.  I hope to get to it soon.
0
 

Author Comment

by:adspmo
ID: 9886433
Hey no problem

I appreciate your help

James
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 1200 total points
ID: 9895090
adspmo,
I copied your profile, changed it to my name.

Created a leave request
Clicked Personal
Entered 12/12-12/22
(Form calculates "7 days")
Clicked submit
Message "Submit Request?///Are you sure you wish to Submit this request?"
...confirmed YES
Prompt: A notification will be sent...
Found message in outbound mailbox to the approver, with working DocLink.

So far so good?

Created new leave request
Identical setup (personal, 12 to 22=7), submit
Identical result.  Should have caught the dup.

Then I added the code from your 12/02/2003 01:39PM post, and I indeed observed that pressing the button does nothing:
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( @ISMember(@Text(CurrentDates); @Text(Booked))
; @RETURN(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); "");
@If(@Prompt([YESNO]; "Submit Request?"; "Are you sure you wish to Submit this request")=1; ""; @Return (""));
...etc...

Change the @Return(0) statements:
sDate := @If(@IsError(temp); @Return(@Prompt([ok]; "Error1"; @Text(temp))); temp);
eDate := @If(@IsError(temp1); @Return(@Prompt([ok]; "Error2"; @Text(temp))); temp1);

Lo and behold!  Error1///Entry not found in index or view's index not builtOK, so let's look atthe definition for temp:
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
Which means, go to the FullName view, search by key NameFull, and return value(s) for the StartDate field.  I find you have a view named "(FullName)|FullNameLU" -- you are not sing either alias.  I change the DbLookup to use FullNameLU.

I still get teh same error... but legitimately this time, because I have no leaves i Approved status. So, I manipulate the two documents in your name there so they have my name instead.

Now, I get the Hemanthian message: Conflict///Alrady you have booked vacation on these days.

Now, I chose completely different dates, so why the messages? Put in some debugging propts: Incorrect data type for operator or @Function.  That applies to both CurrentDates and Booked. Some more debugging...

And then I realize:

Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );

These are nearly identica, the format being of the center part being:
@TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate))

The two halves of that are nearly identical:
@TextToTime(@Text(StartDate))

That returns a date-time.  So, what we have is:
date-time + "-" + date-time.

Bingo!

There shoudl be only ONE @TextToTime, and that shoudl surround the entire "textual-date1 - textual-date2" formula.  Plus, it should be " - " not "-"

So, ...
Booked := @Explode( @TextToTime(@Text(sDate) + " - " + @Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate) + " - " + @Text(EndDate)) );

Excellent, no false conflict.  Then I test using tue conflict dates, 12/2 to 12/3.  Waitaminute!  It goes through w/o a booking error!  Hmm, 12/2-12/3 does give the "already booked" message.  Seems like it has to be an exact match.  Ah!  Incorrct usage of @IsMember.  The function reurns true if list A is a subset of list B.  We want it to return true if ANY member is shared in common, including "offset overlaps."  We can use @IsNotMember, but we would have to use it both ways.  Or, we can use *=.  I'll go for the latter.

Perfect.

One smal issue (which I raised in an earlier post). If theer is no existing record in FullName for the person, this does not work correctly.  That's because you had the @Return(0) on error, which I changed to @Return(@Prompt(...)).  But if the user has no existing approved documents, then it also has an @IsError, and you also end up with @Return.  So, let's chaneg them to CONTINUE...

Voila! It now passes tests if there are no docs, passes if there are docs but no overlap whatsoever, and failes if there are full or partial overlaps.

One problem remains in my view.  This still allows a single person to double book vacation days, as long as they do the second booking before the first is approved.  I'll leave that one to you.

Final code:

temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); CONTINUE... ; temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullNameLU" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); CONTINUE... ; temp1);
Booked := @Explode( @TextToTime(@Text(sDate) + " - " + @Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate) + " - " + @Text(EndDate)) );
@If( CurrentDates *= Booked
; @Return(@Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates")); "");


@If(@Prompt([YESNO]; "Submit Request?"; "Are you sure you wish to Submit this request")=1; ""; @Return (""));
@If(type = "Personal" & PersonalDaysAH_2 ="0" ;@Return(@Prompt([OK];"Reminder";"You have no personal days available"));"");
@If(type = "Vacation" & AllowanceAH_2 ="0" ;@Return(@Prompt([OK];"Reminder";"You have no vacation days available"));"");
@If(Approver_2="";@Do(@Prompt([OK];"Warrning";"You must choose a final approver to send your request!");@Return(""));"");
@If(Requestor="";@Do(@Prompt([OK];"Warrning";"You must enter your Email address for your request!");@Return(""));"");
@If(Type="";@Do(@Prompt([OK];"Warrning";"You must enter your leave type for your request!");@Return(""));"");
@If(StartDate>EndDate;@Do(@Prompt([OK];"Warrning";"Please enter an end date greater or equal to the starting date");@Return(""));"");

@Command ( [EditDocument] ; "1" ) ;
@SetField("SaveOptions";"1");

REM "For updating history";
his_info:=("Submitted by:  ")+ @Name([CN]; @UserName) + " -- " + @Text(@Month(@Today))+"/"+@Text(@Day(@Today))+"/"+@Text(@Year(@Today));
@SetField ("History";his_info);@NewLine;

@SetField ("Reviewer";Approver_1) ;
@SetField("ApprovalStatus" ; "Processing" );
@If(Approver_1="";@SetField("Status_Flag";"3");@SetField("Status_Flag";"2"));
@SetField("ApproverComments_1";"");
@SetField("ApproverComments_2";"");
@SetField("Submit_Date";@Now);
@Command([FileSave]);

ApproverList:=@If(Approver_1="";Approver_2;Approver_1);
MailTo:= ApproverList;
MailCc:= "";
MailSubject:=SubmitMailSubject+ "# "+@Trim(DocNum);
MailRemark:=SubmitMailRemark+@NewLine+"Best Regards";
notify := " A notification will be sent to " + @Name([CN];MailTo) + " ." ;
@Do(@Prompt([OK];"Send Notification";notify);@MailSend(MailTo;MailCc;"";MailSubject;MailRemark;"";[IncludeDoclink]));
@Command([FileSave]);
@Do(@SetField("SaveOptions"; "0");@PostedCommand([FileCloseWindow]))
0
 

Author Comment

by:adspmo
ID: 9899005
excellent

What I will do is modify the approvers button to check if there are over booked when they are approving, They can then reject and send it back

James
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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

765 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