combining pulldata calculations

682
5
12-08-2017 11:27 AM
Highlighted
Occasional Contributor II

Is it possible to combine pulldata calculations into 1 field but pulling from multiple tables depending on relevant statements in previous questions? I have multiple tables with data for different features. Each feature has the same attribute fields. I want to use 1 field in my survey to collect that attribute value for all features. Some can be prepopulated from existing tables. Based on how a previous survey questions was answered, can I pulldata from the appropriate table into 1 field?

So if previous question is answered A, then Field X populates from a pulldata function readingTable A. If previous question is answered B, then same Field X populates from a pulldata function reading table B. I've tried to combine multiple pulldata calculations into 1 string using "and", "or", no spaces, and "," but I'm not able to return the result I am looking for.

Reply
0 Kudos
5 Replies
Highlighted
Esri Esteemed Contributor

Hi Adam,

First, I would recommend keeping pulldata options separated in multiple questions - the form processing API doesn't spot pulldata functions that do not begin a question, potentially leading to missing references to the tables.

Second, it sounds like what you want to accomplish can be done through either if() or coalesce functions.

if() allows you to specify two different results, depending on whether a given is statement is true.  Let's say you have pulldataA and pulldataB and you're checking to see if the feature should look from A or B:

if(${typeQues} = "typeA", ${pulldataA}, ${pulldataB})

if questions can be nested to add additional branches

coalesce will return the first non-null value from two questions.  If a pulldata fails, it will have null value (nothing).  in that case

coalesce(${pulldataA}, ${pulldataB})

will return pulldataA if that succeeded or pulldataB if pulldataA did not succeed but pulldataB did.  Like the if statement, these can be nested.

Reply
0 Kudos
Highlighted
Occasional Contributor II

James,

Thank you for the help but I still don't seem to be entering this quite right. I've tried several combos but keep getting  ODK validate error/bad node msgs and can't seem to find a full written out example for these. Could you tell me whats missing or point me to an example?

if(${QuestionX}='A', pulldata('csvfile1', 'returnColumn', 'lookupColumn', ${lookupValue1}), pulldata('csvfile2', 'returnColumn', 'lookupColumn', ${lookupValue2}))

coalesce(pulldata('csvfile1', 'returnColumn', 'lookupColumn', ${lookupValue1}), pulldata('csvfile2', 'returnColumn', 'lookupColumn', ${lookupValue2}))

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

Hi Adam,

Without seeing the messages I cannot say for certain, but most likely it is because the form validator cannot detect pulldata functions within other functions (that's why my example assumed another question that was only the pulldata function).

Reply
0 Kudos
Highlighted
Occasional Contributor II

I was able to get coalesce to work which will be very useful. Thank you! 

The if statement wont take. I have some fields I only want to call a lookup table for based on a previous answer, otherwise the field remains null to manually fill out when other values are selected in that previous question.

if(${question}='TypeA', pulldata('csvfile','return','lookup', ${lookupValue}))

if(${question}='TypeA'), pulldata('csvfile','return','lookup', ${lookupValue})

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

The if statement needs a value if false, even if only a ''.

Reply
0 Kudos