Solved

Dynamic checkbox fields from column lookup needs correct value

Posted on 2012-04-02
12
1,142 Views
Last Modified: 2013-12-18
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
Comment
Question by:KTTKTT
  • 7
  • 5
12 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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
 

Author Comment

by:KTTKTT
Comment Utility
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
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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
 

Author Comment

by:KTTKTT
Comment Utility
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
 

Author Comment

by:KTTKTT
Comment Utility
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
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:KTTKTT
Comment Utility
hmmm...how long to learn how to do this using XPages.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
To learn, without any knowledge: 1-4 weeks
Estimated net implementation time for this feature: 1-2 hours
0
 

Author Comment

by:KTTKTT
Comment Utility
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
 

Author Comment

by:KTTKTT
Comment Utility
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
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
Comment Utility
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
 

Author Comment

by:KTTKTT
Comment Utility
thanks much. will look into dynamic table example when get more time.  thanks for shorter version of formula.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now