Pulldata with column name as variable


Is it possible to have a variable in the pulldata function?

I have attached a CSV file to my KOBO form and it works fine if I used something like this:

pulldata('csv_file', 'column_1', 'id_col',  ${id})

I need to change ‘column_1’ to with ‘column_2’ or another value based on prior conditions. I tried the solution mentioned here but it didn’t work.

I have created a calculated column cal_column with the calculation being: concat("'"column_"", ${myValue}, "'")
and I display this calculation in a note column afterwards and it displays the column name inside quotes like this 'column_2'

Then in my pulldata becomes: pulldata('csv_file', ${cal_column}, 'name', ${id})

I also tried concat inside pulldata and it didn’t work. It only works when I explicitly set the column name inside pulldata.

I have managed to construct my pulldata fuction using concat and store it in a calculation named c1. In my next question, I put ${c1} under calculation but it results in the pulldata being displayed as text. How can I read ${c1} as a function under calculation? Meaning, applying a calculation from string. Is this possible?

1 Like

The 2 is part of the column I am trying to pull. Please see my edit above.

@Isslam, could you share the relevant part of your XLSForm along with the CSV file with the community? The community should be able to try a workaround for you.

Hi @Kal_Lam here is my XLSForm pulldata_variable.xlsx (11.3 KB) with all the methods I tried so far.
And this is my CSV file hosts.csv (1.8 KB)

In the CSV, each seacrh_code could have up to 10 “Hosted” in columns Hosted_1 and so on.
I am trying to pull all Hosted_* values for each code entered in the form.

I get the Index of the repeat group and use the index to pull the corresponding column from CVS, Index 1 will get column Hosted_1 and so on.

  • The First method: cal_column1 concatenates the string “Hosted_” with the Index

  • The second method cal_column2 adds single quotes to cal_column1

  • The third method cal_column3 constructs the pulldata using the index and results in a string

Screenshot of XLSForm.

The only workaround that worked for me was a long nested if-statement. An If-statement with 10 conditions in each repeat iteration. But my data could have more than 10 possible values that’s why I need to make the pulldata get as many values as the user adds repeats.

You can use the code 86 and add members to the repeat group up to 5 members and you’ll see that the nested if-statement pulls the data correctly and keep an eye on the notes I put to display the three non-working methods in each iteration (cal_column1 to 3).

@Isslam, let me further understand your requirement. So it seems like you have a matching variable (search_code) and 10 other column headers (e.g., Hosted_1, Hosted_2, …, Hosted_10) in your CSV file.

So it your requriement something like, if the user selects 73 as search_code, the entry should be 50d996e6. Similarly, if the user selects 16 as search_code, the entry should be 158d03408b3994c520ec07640f21a215. Is this what you are seeking?

Thank you @Kal_Lam for your reply.

In the second case seacrh_code = 16, The repeat group should make it possible to retrieve each "Hosted_#" column in a separate repeat iteration, not to return all "Hosted_#" values as one string. So in the repeat with Index 1, the pulldata should have "Hosted_1" in the return column. When the user adds a second repeat iteration, the Index is 2 and the pulldata should have “Hosted_2” as the return member and so on, And the user keeps adding in the repeat until it displays nothing. Then they’ll know that this search_code has no more "Hosted_#" values.

If you deploy the form and upload the csv, put 86 in the Host Code question, then add 5 repeats and you’ll see that each repeat has it own "Hosted_#" value.

Hi @Kal_Lam , any ideas?