We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
8 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Dynamic checkbox fields from column lookup needs correct value
Posted on 2012-04-02
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
@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
@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
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.
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
@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
@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
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.
[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
-
7
5
12 Comments
Active today
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"?
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"?
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
@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
@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 )
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
@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
@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 )
Active today
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?
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?
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
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
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)
Active today
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.
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.
Active today
To learn, without any knowledge: 1-4 weeks
Estimated net implementation time for this feature: 1-2 hours
Estimated net implementation time for this feature: 1-2 hours
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. :)
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(
---
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(
!@Contains(WFLkup_Sub ; "1.0.4 - " ) ;
Field testleftback2_1_1 := "error" ;
Field testleftback2_1_1 := testleftback2_1_1 )
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(
---
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(
!@Contains(WFLkup_Sub ; "1.0.4 - " ) ;
Field testleftback2_1_1 := "error" ;
Field testleftback2_1_1 := testleftback2_1_1 )
Active today
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(
testleftback2_1_1
)
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(
testleftback2_1_1
)
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Notes 8.5 Archiving Steps and Tips
This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month8 days, 1 hour left to enroll
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.