Can I add a Record Number to the Chart and use it in the Macro

[#1]

Hi,

I am working on a Macro to toggle select based on a field called RecordNumber (see the script below). What I am doing is to get the value in field RecordNumber  for row 25, and then using toggleselect to select the record with that RecordNumber. The script works well.

However, now I need to do the same sampling process for another chart, which does not have a RecordNumber in the table when it is loaded. Is there anyway I can add a "Record Number" to this chart and toggle select based on the newly added Record Number? I know how to add the RecNo() as an expression in the Chart, but can not use that expression in my macro. Want to ask anyone would have an idea how I can use that.

SUB SampleRow

     vSampleRow  = 25

      val = ActiveDocument.Evaluate("FieldValue('RecordNumber',"& vSampleRow &")")

    'Selecting the record

    ActiveDocument.Fields("RecordNumber").ToggleSelect val  

End Sub

[marcus_sommer]

The easiest way seems to be to add such RecordNumber to this table within the script perRecNo or RowNo?‌

- Marcus

[marcus_sommer]

If I look a second time on this - you don't need such a record-number for this kind of selection then fieldvalue() returned the values in load-order.

What is the reason to select a further (random) value to an existing selection per macro?

- Marcus

[#4]

Hi Marcus, thanks for the response. So my original goal is really to randomly select X rows from the chart. I put in my question like select the 25th row to simplify the question.

Since you said I don't need such a record-number for this kind of selection then fieldvalue(), would you please explain in detail how I should select an entire row in a Chart without the record number?

Thanks,

Yang

[marcus_sommer]

I would rather use an approach like this:

sub RandomSelecting

RandomCount = ActiveDocument.Variables("vRandomCount").GetContent.String

set val = ActiveDocument.Fields("Member").GetPossibleValues

for i = 1 to RandomCount

    j = int(rnd() * val.Count-1) + 1

    str_temp = val.Item(j).Text

    if instr(str, str_temp) >= 1 then

        i = i - 1

    else

        if i = 1 then del = "" else: del = "|"

        str = str & del & str_temp

    end if

next

'msgbox str

ActiveDocument.Fields("Member").Select "(" & str & ")"

end sub

Not very nice at this stage and it might need some error-handler (if val.Count < RandomCount) but in general it worked.

- Marcus

[#6]

Thanks Marcus. Your script gave me a lot good ideas. I will look through and let you know if I have further questions.

[#7]

Thanks to Marcus. I updated my sampling process to make it more efficient and accurate

Also at the same time, I figured out how to sample dynamically on any field. All I need is a input box to link the field to V_SampleID.  And then the script will run using the field that was put in the input box.

I am attaching my final script here to show the solution.

SUB SelectRandom

    v_Identifer = ActiveDocument.Variables("V_SampleID").GetContent.String

msgbox("You are about to sample on field '"&v_Identifer&"'.")

    'Clear any exsiting selection on the identifer field

ActiveDocument.Fields(v_Identifer).Clear

'Unlock any exsiting selection on the identifer field

ActiveDocument.Fields(v_Identifer).Unlock       

ActiveDocument.lockall

'Pull in the sample size from the slider

vSampleSize = CInt(ActiveDocument.Variables("vAuditSelect").GetContent.String)

    'Pull all the possible values in the identifer field

    set ID_Values_array = ActiveDocument.Fields(v_Identifer).GetPossibleValues(100000000)

'Pulls in the total count of possible identifer values

vPopulationSize = ID_Values_array.count

    'Check if the unqiue identifer field count is more than sample size

    If vPopulationSize < vSampleSize Then

            msgbox ("Sample size can not be more than population size")

Else       

vSampleItem = -1

del = "|"

str = ""

    while i < vSampleSize

'Generate a random sample row number

vSampleItem = int(Rnd() * vPopulationSize-1)+1

'Get the value of the identifer field at that random row

    val=ID_Values_array.item(vSampleItem).text

            ' check if the value has been selected if not

if instr(str,del&val&del) < 1 Then

         'add the value to the string that stores the selected value

         str = del & val & str

         'select the records with the sampled value in identifer field       

         ActiveDocument.Fields(v_Identifer).ToggleSelect val

         i=i+1                               

End if               

Wend

End if

ActiveDocument.Unlockall

End Sub