nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Column index and iterate

TheRealDea

Say I have a dataset that has 20 columns; is it possible to grab the:

1)column name
2)column value(any given row)

where the column is in the tenth position(index)?

If that is possible; is it possible to:

1) iterate through the columns starting fifth position(index) and go to the max number of columns do the above mentioned routine

Find more posts tagged with

Comments

mwilliams

Hi TheRealDea,

Can you explain more about what you're looking to do? I'm not sure I'm quite understanding. Thanks.

TheRealDea

What I am trying to accomplish is, running a dataset 10 times changing the one parm at a time. Each time the dataset changes, the resulting dataset will be the 30 columns.

Hopefully, you are not lost so far.

Finally, I will iterate through the 30 columns looking for values in each column greater than zero. I will then concatenate the fieldname/value to a string for display. All of this will be done 10 times making 10 unique strings.

I have done stuff like this before in VBA/Acccess, but making it work here losses me.

Thanks,

mwilliams

TheRealDea,

I think I mostly understand. Do you think you can set up a report with the sample, classic models database that is set up kinda how you'd like it that I can work with?

TheRealDea

Hair color and ages

Age Range Red Brown Blonde
0-10 0 6 10
10-20 5 11 1
20-50 3 10 0

The strings:
Record 1 "0-10, Brown:6, Blonde:10"
Record 2 "10-20, Red:5, Brown:11, Blonde:1"
Record 3 "20-50, Red:3, Brown:10"

The goal is to build a string per each record based upon fieldnames and values in each fields. Except, where the value is zero.

Thanks,

mwilliams

TheRealDea,

You could do this in a computed column in your dataset and just display that string field in your report.

TheRealDea

You have my attention.

On a record-by-record basis, how would I build this computed field to display only the populated fields?

mwilliams

TheRealDea, 
 
Here's a screenshot of what the following code in a computed column does. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
output = row["Age Range"];
if (row["Red"] != 0){
output = output + ", Red: " + row["Red"];
}
if (row["Brown"] != 0){
output = output + ", Brown: " + row["Brown"];
}
if (row["Blonde"] != 0){
output = output + ", Blonde: " + row["Blonde"];
}
output;
</pre>
 
Let me know if this works for you.

TheRealDea

Is there any way to iterate the fields for names/value via a For/Next loop?

mwilliams

TheRealDea,

I don't think there is, but I've been wrong before.

What reason is there for wanting to do it this way rather than a simple computed column? Maybe if I understand, I can suggest another way.

TheRealDea

Ultimately what I am attempting to have is some preformatted string popup when when I click a hyperlink/html in a text control. 
 
On the report, which is privy, lets say I will have 20 text controls. These 20 controls will display 20 preformatted string variations from the 20 or so datasets and each will have approx 30 columns. 
 
In VBA, I would have ran this on each dataset: 
 
tmpString = "" 
rem The "i" represents the column index. 
rem You could have also replaced "i" with the real column name, but again - it's not dynamic. 
For i = 1 to 30 
If row.value <> 0 Then 
tmpString = tmpString & vbCrLf & row.name & " " & row.value 
Endif 
Next 
 
To me, this structure will be some much cleaner and safer since fields could be added or removed without much regard. Where as the checking of all 30 columns for a value greater than 0 is daunting. 
 
As always thanks,

TheRealDea

Does anyone have any thoughts about this?