Lists and a lookup

HCL Domino Domino Forum
1

HiI’ve an in-house timecard application. Edit: Notes Client only

I’m trying to add an illness self-certification form to it. The idea is that when the user selects the work code for “illness”, exiting the field will force a validation to see if there is a form in place for this day, if there is, then the timecard can be completed, otherwise the user will be forced to complete a certificate.

The self certification form has a couple of date fields (start and return) which i’ve exploded into a date list. I’m displaying the list in a view. The view has the usename as the key and the date list as the second column. I want to lookup the view and return the documents belonging to that user then check to see if the date on the time card exists in the list of dates already in place for that user.

I’m thinking DBColumn to return the entire list of dates for the user, but will the restriction of 64kb return data be enough if there are too many dates? Am I barking up the wrong tree?

I hope the description is understandable and I’m mainly a Formula coder. Any pointers gratefully received.

2

Subject: Lists and a lookup

If you have an option to modify that view (i.e. that view is only used for the purpose of looking up whether an employee has filed a form for sick leave), then I would do the following:

Make the first column of the view a sorted text column that contains the concatenation of the user’s common name (assumed in this example) as well as the date in a sortable format (may I suggest yyyymmdd).

Then, you need only iplement some variation of the code below. You should not have any danger of hitting the 64k because you are using @DbLookup instead of @DbColumn (unless your employees are taking A LOT of sick leave) and you get to code in formula.

sickDay := DateFieldNameGoesHere;

view := “YourLookupViewName”;

key := @Name([CN]; @UserName) + “_” + @Text(@Year(sickDay)) + @Right(“0” + @Text(@Month(sickDay)); 2) + @Right(“0” + @Text(@Day(sickDay)); 2)

x := @DbLookup(“”; “”; view; key; 1);

@If(

@IsError(x);

@Return("");

""

);

@Prompt([OK]; “Form not filled”; “Please make sure that you fill out a form for your sick day before trying to claim a sick day leave of absence”)

3

Subject: RE: Lists and a lookup

Thank you, that does make sense to me. I’ll try it next week and let you know.

4

Subject: Lists and a lookup

Is this a web-application or browser-based/

5

Subject: RE: Lists and a lookup

Sorry, it’s Notes Client based, no web involved.

6

Subject: RE: Lists and a lookup

Then look at using lotusscript on the onblur event to do the checking. That way you can get around the lookup limitations.

7

Subject: RE: Lists and a lookup

As I’m stuck with formula, I’ve started it this way:

Create a reference document for each user. The document has name and datelist fields.Putting the documents in a view with the UNID showing, I can DBlookup the UNID and use:

datelist:=@GetDocField(DocUNID; “sc_dates”);

to get the datelist and do an @IsMember to see if the current document date exists in the list.

My only problem now is calling @Command([COMPOSE]) from within an @If formula - I don’t think it can be done…

8

Subject: RE: Lists and a lookup

Why are you “Stuck with Formula”?

9

Subject: RE: Lists and a lookup

I’m not “stuck” with Formula necessarily, but I don’t know Lotusscript. So far I’ve managed to do everything I need to in Formula. Thx.