Search() vs pulldata()?

Support
1

This is going to be hard to explain so I'll give some background first.

I am using ODK Collect as a method to capture inspection data from a manufacturing process. The operator enters the part number into the ODK form which uses the search() function to populate a series of select_one questions from a set of pre-loaded .csv files. Each .csv file contains two columns: a pn_key column that lists all of the part numbers which can be entered, and a dim_xx column, where "xx" is replaced by a dimension type, for example dim_od is outside diameter. There is one .csv file for each dimension on the part.

I use a VBScript macro in excel to generate the .csv files. They consist of a list of acceptable dimensions for each feature broken down in .001" increments. For example:

pn_key dim_od
part1 .105
part1 .106
part1 .107
part1 .108
part2 .130
part2 .131
part2 .132

There are about 250 different part numbers and 8 different dimension .csv files.

I use the following function in the appearance column to create the list of options:

quick search('hss_parts_od', 'matches', 'pn_key', ${pn_barcode})

hss_parts_od is the name of the .csv file for the outside diameter dimensions.
pn_barcode is the variable that stores the part number, scanned from a barcode in an earlier question.

This system works, but it simply doesn't allow the operator to enter dimensions that fall outside the defined tolerance range. What I want to do is increase the dimensional range for each part by .002 in both directions, then have ODK check if a feature is entered outside the original acceptable range, and flag the operator that the result is nonconforming.

The method I had intended to use was to check the number selected by the operator against the minimum and maximum numbers within the .csv file for that part number and dimension. I originally tried doing this by putting a search() function within the 'relevent' column of the survey, for example:

${dim_od} >= (max(search('hss_parts_od', 'matches', 'pn_key', ${pn_barcode}))-.002

However, leaving aside the syntax for the max function or -.002, this wouldn't even process through XLSForm.

Next I tried a pulldata() function instead:
pulldata('hss_parts_od', 'dim_od', 'pn_key', ${pn_barcode})

However I found that this function only seems to populate the variable with the first row it finds matching 'pn_key' rather than all of the matching rows.

I guess my fundamental questions are:

  1. Does the search() function work in the calculation or relevant columns?
  2. Where search() seems to pull all matching rows, does pulldata() only pull the first matching row?

Here are examples of the files I'm working on:

HSS_Inspections_Test.xlsx (14.3 KB)
hss_parts_od.csv (68.4 KB)

2

Hi @AaronF,

First, I start answering your two fundamental questions:

  1. Does the search() function work in the calculation or relevant columns? No it does not. It only works in appearance column
  2. Where search() seems to pull all matching rows, does pulldata() only pull the first matching row? Yes you are right. Pulldata get only first matching record

Second, I am not sure If I understand well your situation. What if you define your csv in a different way and include min and max values. Then you can use pulldata to find limits.

image

3

That was how I originally intended to build the files, but it didn't seem to be possible to define a range with a variable min and max which is the fundamental requirement. If there is no other way to do it I guess I could add another set of CSV's that define min and max for each feature.

4

What if you change select_one s by decimal widget with label "Write diameter between ${min} and ${max} and constraint between ${min} and ${max}.

5

That would work, but it's not what I'm looking for. I want the operators to select from a list or range, not type in a number. Typing the number in introduces more potential for error.