Pull Data with If Condition from 2 external Files

Hi @Kal_Lam

Can you please guide , how can we create pull data condition from 2 external file with if condition.

we have 2 external files

  1. recoveryupload
  2. odupload

Please help to correct the below if condition

if

Field Name - work_type

is equal to ‘regular_repayment_visit’,

then

pulldata(‘recoveryupload’,‘state’,‘memloanid’,${Loanid}

else

pulldata(‘odupload’,‘state’,‘memloanid’,${Loanid}

Cheers !

Neeraj

Can anyone Please guide

its a bit urgent

Hi @Ysr3322 @Kal_Lam

Please help in this , if possible

Cheers !

Neeraj

Hi, can you try attaching the xlsform and csv files (or a part of the files not containing any real data)? This should make it much easier to troubleshoot. It should be possible to do this with 2 different csv files. There’s probably just a small problem in syntax.

Noel

1 Like

Hi @NoelCartONG

Thanks for replying

Please find enclosed the form and external sheets ( Dummy ) for your kind refrence

overduecollection_Solved.xlsx (11.7 KB)
odupload.csv (241 Bytes)
recoveryupload.csv (203 Bytes)

Just a Brief for your reference

  • We have 3 types of working in field which is to be done by field worker
  • Relevant data for the field visit or household will be in external files which will be separately uploaded in media
    -He will start his working by selecting which type of work he is going to do like od recovery or zero collection or recovery
  • then he will fill unique code which may be related to above selected working

on the basis of above unique code data should be pulled from that particular external file which will be uploaded in media folder for eg : if he selected recovery in very first question the external data should be pulled from recovery upload sheet on the basis of unique code filled in 2nd question

and so on

the form i uploaded has one sheet pull example but i tried in first pull row for if condition which was not successful

I really appreciate any help in this

Cheers !

Neeraj

Hi,
I made some changes to your form to make the pull data questions separate for the OD and recovery. I then pulled the final state visible to the interviewer using the if command as shown below

Just to confirm, I have tested this and it works fine with your files except the recovery. I noticed you had the column headings in capitals while you had referenced them using small letters in the pull data query. I changed the csv to small and it worked just fine. Please find your forms reattached for you to test this on your end.
recoveryupload.csv (203 Bytes) odupload.csv (241 Bytes) overduecollection_Solved (1).xlsx (11.9 KB)

Stephane

1 Like

Hi @stephanealoo

Thank you for your solution , but as I could see that for each such field we have to create two separate columns.

In our case we have total 6 options with same field name like recovery , Od , zero collection

So in this case we have to create 6 rows In form and then one for final result , and their are total 11 fields which we need to settle like this , by this way forms will have 66 rows extra and same 66 rows will appear in downloaded excel file as well

What I need is to avoid these 66 rows .

Is their any possibility that the pull formula can be used with if condition

Thanks for your patience to read and helping me out

Cheers !

Neeraj

Hi @caneeraj
Normally a form is just as good as how we customize it for our needs. I think you can do more customization of your form or get someone from the job board to assist with that process.

However to answer some of your specific questions:

NO, I tested it and saw that it is not possible at all. However you can use this a bit differently using the search appearance as indicated [here](https://xlsform.org/en/#dynamic-selects-from-pre-loaded-data) I have worked out a sample form which you can use to really manipulate your current form
As per the topic below, this should work

countriesbyregion.csv (36.9 KB) Select One and Multiple from CSV.xlsx (12.2 KB)

Stephane

1 Like

Hi @stephanealoo

Thanks for updating

However i could able to the same ,

just sharing in case any one from community need it

if(${work} = ‘1’,pulldata(‘recoveryupload.csv’, ‘branch’, ‘memecode_key’, ${ecode}),pulldata(‘odupload.csv’, ‘branch’, ‘memecode_key’, ${ecode_x}))

Above formula was used to pull data from two different external file basis pull method

Please check and guide in case i missed something ,if any

type name label required relevant appearance calculation read_only
start start
end end
today today
username username
simserial simserial
subscriberid subscriberid
deviceid deviceid
phonenumber phonenumber
select_one type work Please Select the Type of Working you are going to Start true
text Loanid_r Please Provide LAN Number of Any Member from Group true ${work} = ‘1’ numbers
calculate Loanid false string(${Loanid_r})
text Loanid_x Please Provide LAN Number of Any Member from Group true ${work} = ‘2’ numbers
calculate loanido false string(${Loanid_x})
begin_group Group_Data Branch Details false field-list
text state State false if(${work} = ‘1’,pulldata(‘recoveryupload.csv’, ‘state’, ‘memloanid_key’, ${Loanid}),pulldata(‘odupload.csv’, ‘state’, ‘memloanid_key’, ${Loanid_x})) TRUE
text areaoffice Area Office Name false if(${work} = ‘1’,pulldata(‘recoveryupload.csv’, ‘areaoffice’, ‘memloanid_key’, ${Loanid}),pulldata(‘odupload.csv’, ‘areaoffice’, ‘memloanid_key’, ${Loanid_x})) TRUE
text branch Branch Name false if(${work} = ‘1’,pulldata(‘recoveryupload.csv’, ‘branch’, ‘memloanid_key’, ${Loanid}),pulldata(‘odupload.csv’, ‘branch’, ‘memloanid_key’, ${Loanid_x})) TRUE
text villagename Name of Village false ${work} = ‘2’ pulldata(‘odupload.csv’, ‘villagename’, ‘memloanid_key’, ${Loanid_x}) TRUE
text coname Name of Credit Officer false if(${work} = ‘1’,pulldata(‘recoveryupload.csv’, ‘coname’, ‘memloanid_key’, ${Loanid}),pulldata(‘odupload.csv’, ‘coname’, ‘memloanid_key’, ${Loanid_x})) TRUE

Cheers!

Neeraj

2 Likes

Thank you for sharing the solution to the entire community! It should help the community who wish to accomplish the same in the upcoming days!