Hi All,
I have searched this forum and the 4/5 forum for an answer to this with no solution. I have an excel spreadsheet that I am loading data from into notes forms. Everything I am doing works fine except for the load to a checkbox field. My checkbox field has the following properties:
Allow multi-values is checked, refresh fields on keyword change, refresh choices on document refresh, and the third tab has under multi-value options (greyed out) - seperate values when user enters… comma is checked.
If I have “value1” in the cell it updates on the form correctly (Value1 is checked on the field).
However, the field does not update with multiple values? In the excel spreadsheet it has “value1,value2” and I tried “value1, value2”… thinking that the doc refresh would update it correctly (checking both values), but it does not??
In my script I have tried to refresh the document in the ui, refreshed the doc AFTER loading it (hoping the field had the data in there but just needed to be updated another way, but it does not.
I have also tried ComputeWithForm as well with no success.
Is there something I could do in the script agent to solve this, or am I missing something else that needs to be in the field? The check box field is an editable field on the form…
Any help would be appreciated…
TIA
Mick
Subject: Import Excel cell data to notes checkbox field
You cannot give a “checkbox” field the ability to separate entries based on a user’s response (i.e. separated by a comma). Although it is there, it should be greyed out.
What you should do is have another, multi-value hidden field which operates as a source for the checkbox’s choices - Let’s call that field “cbChoices”. That field name should then be represented in the “Choices” property of the checkbox field (“Use formula for choices”).
When you import the Excel values, assign those values to the new multi-value field. That will provide the new choices for your checkbox field. If you then want them all checked, simply assign the value of the multi-value field to your checkbox field, and you should be good to go.
Hope that helps!
T.
Subject: RE: Import Excel cell data to notes checkbox field
Thanks for the response Terry, that is actually a pretty good solution for another issue I was having, but unfortunately will not work in this one. I am not creating any “new” values for this check box field. The values that are in the notes field match the names from the excel spreadsheet. I just have to update the check box field “checking” each value from the spreadsheet. I was thinking that maybe I could use an alias value on the forms checkbox field:
Choices:
Value1 | 1
Value2 | 2
Value3 | 3
…
and then in the script apply the cell value to the associated number and updaye it thru an array with a forall statement or something like that… just not sure where to start, or if this is a viable enough solution to start investigating…
Is there any other ideas that I could try?
Like I said in my original topic, if there is ONE of the values in the cell (does not matter which one), it updates (checks the appropriate box) successfully!! If there are multiples, it does’nt. I am using just this in the script:
doc.ValueCheckBox = .Cells(row, 6 ).Value
and hoping that the way the field is set up would take care of it… Nope
Thanks for the response…
Mick
Subject: RE: Import Excel cell data to notes checkbox field
Perhaps the value you are retrieving from the Excel worksheet is a single string, and that wouldn’t equate to any of the available checkbox options?
In that case, determine what the delimiter is (e.g. a comma), and use the lscript “split” command to separate the values and assign them to the field individually.
Maybe that’ll do it?
T.
Subject: SOLUTION: Import Excel cell data to notes checkbox field
Hey Terry, I new it was something simple to do, but I just could not think of it… here is the solution:
Dim OrgStr As String
OrgStr = .Cells(row, 6 ).Value
Dim str_Org_Full As Variant
Dim str_Org_Temp As String
str_Org_Temp = “@Explode(” & Chr(34) & OrgStr & Chr(34) & “)”
str_Org_Full = Evaluate(str_Org_Temp)
Dim item As NotesItem
'…set value(s) of Org Checkbox…
Set item = doc.ReplaceItemValue( “ReqNameOrg”, str_Org_Full )
basically setting up a NotesItem, moving the value(s) in an array to that, and then do a replaceitemvalue.
This is for loading cell value(s) from and Excel Spreadsheet to a notes checkbox field. Also, the data on the spreadsheet cell must look like this: value1,value2,value4,value6 and so on, and must also match the checkbox selections on the notes field. Hope this helps somebody out… thanks Terry (T) for all your input, it is much appreciated…
Mick