Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

Dynamic checkbox fields from column lookup needs correct value

I have a column lookup which has value/results in field "Lkup" as follows:

1.0 - checklist item 1.0
2.0 - checklist item 2.0
3.0 - checklist item 3.0
4.0 - checklist item 4.0
'
'
10.0 - checklist item 10.0

The checklist values can change based on keyword document and the list can grow to say 10.0, in which case the column lkup would have 1.0 thru 10.0 .

There is a field name for each checklist item above up to 10 checkbox fields.  
I only want to display an item in a checkbox field if it is in the lkup.
The fieldnames are  checkitem1.0 ,  checkitem2.0...... checkitem10.0

Field named  checkitem1.0 has the following formula for choices which results in the value "1.0 - checklist item 1.0" :

     tmp := @Subset(@Subset(@Text(Lkup) ; 1 ); -1 )  ;
              @If(@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; tmp ;
                     !@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; FIELD Checkitem1.0 :=  "error" ;
               FIELD Checkitem1.0 := Checkitem1.0 )

Field named  checkitem2.0 has the following formula for choices which results in the value "2.0 - checklist item 2.0" :

     tmp := @Subset(@Subset(@Text(Lkup) ; 2 ); -1 )  ;
              @If(@Contains(tmp ; "2.0" ) & flag2.0 = "0"   ; tmp ;
                     !@Contains(tmp ; "2.0" ) & flag2.0 = "0"   ; FIELD Checkitem2.0 :=  "error" ;
               FIELD Checkitem2.0 := Checkitem2.0 )

I continue this upto 10 fields.
I hide the checkbox field if there is no item and/or checkbox field equals "error".

The point is for users to have somewhat of a dynamic checklist based off keywords.

The problem I'm experiencing is that there may not be an item or key listed from 5 through 7, and the column lkup would look like this:

1.0 - checklist item 1.0
2.0 - checklist item 2.0
3.0 - checklist item 3.0
4.0 - checklist item 4.0
8.0 - checklist item 8.0
9.0 - checklist item 9.0
10.0 - checklist item 10.0

and my checkbox values are out of order for checkitem fields as follows:
checkitem5.0 thru 7.0 value is "error"  - this is what I want

checkitem8.0 is found but has wrong value because it's looking for Line 8, hence
tmp := @Subset(@Subset(@Text(Lkup) ; 8 ); -1 )  ;

When this occurs my checkbox formula for choices picks up the wrong checklist item
because it assumes I know ahead of time which line (1, 2, 3, 4....10) to grab the key.

How do I make the checkbox formula more accurate to find the correct line number item to give the correct result?

Thanks in advance.
0
KTTKTT
Asked:
KTTKTT
Advertise Here
  • 7
  • 5
1 Solution
 
Sjef BosmanGroupware ConsultantCommented:
If I'm guessing right, you don't need the code above, the loops etc. It can probably be done with some simple formulas, but I need to understand first what you want exactly. The description above isn't precise enough, I'd really like a simple example, just with letters or so. Describe what you want the code to do.

Some clues:
- did you put the calculations BEFORE the checkbox fields?
- did you use a computed for display field to compute intermediate values?
- did you enable Refresh fields on the main selection field?
- did you enable Refresh choices on doc refresh, for the checkbox fields?
- did you use alias values, like "This is a text|CODE123"?
0
 
KTTKTTAuthor Commented:
Hi,
Essentially trying to make each checkbox editable field (1-10) to take on the selection for each line entry of the computed when composed column lookup if the checkbox field matches the entry in the "Lkup".

Results for Computed when composed column lookup field name "Lkup":
1.0 - checklist item 1.0   - this should be the selection for checkbox field Checkitem1.0
2.0 - checklist item 2.0   - this should be the selection for checkbox field Checkitem2.0
3.0 - checklist item 3.0  - this should be the selection for checkbox field Checkitem3.0
4.0 - checklist item 4.0  - this should be the selection for checkbox field Checkitem4.0
8.0 - checklist item 8.0  - this should be the selection for checkbox field Checkitem8.0
9.0 - checklist item 9.0  - this should be the selection for checkbox field Checkitem9.0
10.0 - checklist item 10.0  - this should be the selection for checkbox field Checkitem10.0

Checkitem1.0 field is an editable checkbox that needs to have 1 selection matching 1.0 above :   "1.0 - checklist item 1.0" .
This is currently accomplished using the selection formula or FORMULA FOR CHOICES in the Checkitem1.0 field.  I need to ensure that 1.0 is in the Lkup value - if not mark it "error" and
use a hidewhen to not show this field for the user since it's not in the todo checklist.
tmp := @Subset(@Subset(@Text(Lkup) ; 1 ); -1 )  ;
              @If(@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; tmp ;
                     !@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; FIELD Checkitem1.0 :=  "error" ;
               FIELD Checkitem1.0 := Checkitem1.0 )
The user checks this todo item off when complete and the input translation sets the flag1.0 to "1" for completion.

Checkitem5.0, Checkitem6.0, Checkitem7.0  fields should simply be "error" since they are not in the "Lkup".

This same formula for choices does not work for Checkitem8.0 because it grabs the wrong position (line 8).  Should I try to use @leftback to find selection: "8.0 - checklist item 8.0"
tmp := @Subset(@Subset(@Text(Lkup) ; 8 ); -1 )  ;
              @If(@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; tmp ;
                     !@Contains(tmp ; "1.0" ) & flag1.0 = "0"   ; FIELD Checkitem1.0 :=  "error" ;
               FIELD Checkitem1.0 := Checkitem1.0 )
0
 
Sjef BosmanGroupware ConsultantCommented:
Puzzled... Could you please send me a small test database that I can run over here? Some data, the lookup view, a few documents in it, so everything works, and I can get the look and feel of it? My mail address is in my EE-profile.

Is a checklist item field a one-value field? If so, why is that? Why do you use separate fields, and not one field with all collected values?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Access It Now
 
KTTKTTAuthor Commented:
I initially had a checkbox field for all collected values, but user wanted to stamp a date for each item checked and they wanted a separate row for each item to look like a spreadsheet with the checkbox to check off the item with the time stamp beside it.  I didn't know of another way to do it.  

Yes the checklist item field is a one value field selection.

Each row has a one value field selection and time stamp it was checked.

So I need the one value field selection for each field to pull the specific selection from the column "Lkup" if for instance field5.0 has 5.0 listed in the "Lkup".

Hopefully this clears it up explanation you need, otherwise, I'll still see about getting a test db. to you.  thx
0
 
KTTKTTAuthor Commented:
I thought lookups would work (to fine the corresponding checklist items 1.0, 2.0, etc., but the checklist fields could grow up to 100 so i didn't want a performance hit w/too many lkups (although it could be a one time lookup if new doc)
0
 
Sjef BosmanGroupware ConsultantCommented:
Oh dear, separate rows... that's a killer for most Notes applications. Now you have 10 rows and they want 11... Which is why I built a dialog-box based form to handle in-document multi-value fields. Example: an invoice with n lines. All line values are stored in multi-value fields, each field has n entries. The dialog box handles one line at at time. Maybe that's a way to approach your problem. There used to be some code in the Notes sandbox that could do this... This is somewhat similar: http://www-10.lotus.com/ldd/bpmpblog.nsf/dx/dynamic-table-1?opendocument&comments

Another way could be an embedded view approach, although it won't be easy. Let's say I mentioned it for completeness' sake.

And then there is XPages. Very easy.
0
 
KTTKTTAuthor Commented:
hmmm...how long to learn how to do this using XPages.
0
 
Sjef BosmanGroupware ConsultantCommented:
To learn, without any knowledge: 1-4 weeks
Estimated net implementation time for this feature: 1-2 hours
0
 
KTTKTTAuthor Commented:
so for me it would be 1-4 wks plus 2 hours of implementation, unless you're saying I can do this w/out knowledge within 2 hrs. :)
0
 
KTTKTTAuthor Commented:
ok since I already have the fields and workflow going and don't have the time to start over with something new,

I think performance will be ok doing the one time column lkup then have each checkbox formula for choices do the following as long as flag for each field is set to zero:

discovered this formula finds the associated line item :

 tmp :=  @Trim(@Implode(@RightBack(WFLkup_Sub ; "8.0 - "  )))  ; "8.0 - " + tmp

---
here's the final checkbox formula for choices for each field substituting the line item of course:
@If(

     @Contains(WFLkup_Sub  ;  "1.0.4 - " )  & WF_flag1.0.4 = "0" ;
         @Trim(@Implode(@RightBack(WFLkup_Sub ; "1.0.4 - "  )))  ;  

     !@Contains(WFLkup_Sub  ;  "1.0.4 - " ) ;  
         Field   testleftback2_1_1  := "error"  ;

  Field testleftback2_1_1  :=    testleftback2_1_1   )
0
 
Sjef BosmanGroupware ConsultantCommented:
Indeed, if you have no prior knowledge of XPages whatsoever it'll take you a few weeks. I'd really recommend you look into the dynamic table idea, it can help you in many places where you want to store a 1-to-n relationship in one document.

Here a shorter version of the last formula (I think):

@If(
     !@Contains(WFLkup_Sub  ;  "1.0.4 - " ) ;  
         Field   testleftback2_1_1  := "error"  ;
     WF_flag1.0.4 = "0" ;
         @Trim(@Implode(@RightBack(WFLkup_Sub ; "1.0.4 - "  )))  ;  
     testleftback2_1_1
 )
0
 
KTTKTTAuthor Commented:
thanks much. will look into dynamic table example when get more time.  thanks for shorter version of formula.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Instantly
  • 7
  • 5
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now