Connect two projects and bring the codes in a list dynamically with Dynamics data attachment

Hola a todos.
Estoy conectado dos proyectos con Dynamics Data Attachment pero no me trae los codigos que genero en mi proyecto A. Estos códigos para el proyecto B deben quedar como opción en una pregunta de selección unica para que pueda ser seleccionada. y así se puedan relacionar los formularios.

Agradezco cualqueir ayuda.

1 Like

I am connecting two projects to show in project B the codes generated in project A. I need to show the codes in project B in a single select one question, I have managed to show the codes in a text type question but I am missing in a select one question.

attached image and XLSX document the proyect B

I appreciate any help from you

seguimiento_alert.xlsx (10.7 KB)

Welcome back to the community, @tomas_cuadrado! Seems like you wish to display the choices of a selcet_one question from project A to project B. Could you let us know if the choies you wish to display in project B from project A is from a select_one question or if it’s from a other question types.

As of now, you should not have any issues pulling/displaying the choices questions from project A to project B using the DDA feature.

But feel free to correct me if I understood your issue differently.

Hi Kal,
They belong to a calculate in project A and the idea is to display those calculate in a select one question in project B.

How can I make this possible?

Hi @tomas_cuadrado,

Let me know if the following matches what you are trying to do:

I have two different projects:

  • Project A, where we register products that we distribute to users. With each response, a unique product code is generated
  • Project B, where we do user survey, after they receive their product. The code generated in project A are displayed in a select_one question in project B.

I did that about a year ago, and as far as I know, there is no direct way to do that in Kobo (I would be happy to be corrected if i am wrong)!

However, I was able to come up with a work around which does exactly that, automatically.
I would happy to share that work around, if it matches what you are trying to do (but it is a tad long to write-up, so I will wait for you to confirm :)) ! Please let me know,

Hi @Mat23

This is exactly what I am looking to do in Kobo, would you be so kind to explain me the step by step,
please.

Sure, here it goes!

As bit of context, you can have a look at this discussion here, which describes what you are trying to do, and where one person gives another workaround which could be of use to you:
Using dynamic data attachment to populate choice lists for select_one and select_multiple fields. Back then (2021), the feature you are describing was not available, and as far as I know, it still isn’t (that discussion explains why it is very hard to implement).

Summary of my workaround:
Instead of dynamic data attachment, In project B, I use external CSV file (via “select_one_from_file”, which contains the list of product ID generated in project A. I have a process that completely automatically, and regularly, update the .csv abased on the data available in project A.

Main limitation:
This is not a perfect solution:

  • It requires a bit of coding: you will have to some level of understanding to be able to replicate it.
  • The .csv file is updated regularly (adjustable frecuency), but not live
  • Updating the CSV requires an internet connection (upload new data in project A, update the .csv file, and update the project B form).

How it works:
Data is collected and project via project A.

  1. I have an export to xls for project A (On Kobo: Form -->Data -->Download)
  2. I import that raw data into a Google Sheet (via a call to the Kobo API and a bit of Google App Script)
  3. My google sheet is set-up to reformat the raw data from project A into the format that the .csv file for project B (select_one_from _external)
  4. I have another bit of GAS code that automatically downloads the formatted data in a .csv file on my hard drive.
  5. Finally, I have a python script that update project B on Kobo’s servers by uploading the new version of the .csv file
  6. Everything is run automatically: (the GAS code via the time-trigger available through Google App Script, and the python’s script via Windows’ task scheduler).

As you can see, this is a tad convoluted. I am no professional developer, but this approach fits my needs (and hopefully, yours). In my context, there is about 1 year between the moment where a code is generated in project A, and when this code is needed in project B. My .csv file updates once per day, which is way more than good enough.

That frequency can be adjusted up or down, but again, it is not live (which is what we could get if DDA allowed us to do that).

The Google Sheet / GAS intermediate made sense in my context (I had to export everything to Google anyway), but is probably not necessary. I am fairly confident that you could do everything via a single python script, that downloads the data, format it into a .csv file and uploads it to Kobo.

I hope this helps! I will be sharing some of my code, so that you (and/or other people) can try and replicate it!

Python script to upload the .csv file:

[Side note1: Here, I upload several different .csv files, to several different Kobo forms, which adds a tad of complexity]

[Side note2: If someone would be kind enough to explain me how to properly insert code, I would be very happy]


#this work is based of this forum post:
#Using API to upload media files? - #8 by Josh

import requests, json
from datetime import datetime
import os
string_output = “”

#This uploads a media file whose path is given by FILENAME (if there is already a file with the same name, deletes it
#to the Kobo form whose asset_uid is XFORM
#If REDEPLOY (boolean) is true, then also redeploys the form on Kobo
def upload(XFORM,FILENAME,REDEPLOY):
URL = ‘KoboToolbox Form Building API
TOKEN = ‘YOUR TOKEN’
headers = {‘Authorization’: f’Token {TOKEN}'}
global string_output

#### TO DELETE OLD MEDIA FILE
try:
    data_url = "%sassets/%s/files/" % (URL, XFORM)

    response = requests.get(data_url, headers=headers, params={'format': 'json'})
    json_uid = response.json()
    #Look throught the different media files attached to the form, and look for the one whose filename matches the title of the file we want to upload
    for result in json_uid['results']:
        #print(str(result['metadata']['filename']) + " vs " + str(FILENAME.split("\\")[-1]))
        if result['metadata']['filename'] == FILENAME.split("\\")[-1]:
            media_uid = result['uid']

            
    
    asset_url = "%s%s/" % (data_url, media_uid)
    #If we have found a file, we will delete it. If not an error will be triggered and caught
    res = requests.delete(asset_url, headers=headers)
    print(res)
    print("Old media file deleted successfully")
    string_output += "Old media file deleted successfully" + "\n"
except:
    print("No media file deleted")
    string_output += "No media file deleted" + "\n"
    pass
#### TO UPLOAD NEW MEDIA FILE

try:
    KC_URL= "%sassets/%s/files.json" % (URL, XFORM)

    MIME = 'text/csv'

    payload = {'filename': FILENAME}
    files = {'content': open(FILENAME, 'rb')}
    data = {'description': 'Input and equipment media file', 'metadata': json.dumps(payload), 'file_type': 'form_media'}

    res = requests.post(url=KC_URL, headers=headers,data=data, files=files)
    print(res)
    print("New media file uploaded successfully")
    string_output +="New media file uploaded successfully" + "\n"
except:
    print("No media file")
    string_output += "No media file" + "\n"

#### TO REDEPLOY FORM

if REDEPLOY:
    asset_url = "%sassets/%s/" % (URL, XFORM)
    headers = {
        'Accept': 'application/json',
        'Authorization': f'Token {TOKEN}'
    }

    response = requests.get(asset_url, headers=headers, params={'format': 'json'})
    version_to_deploy = response.json()['version_id']
    deployment_data = {
        'version_id': version_to_deploy,
        'active': True
    }
    response = requests.patch(asset_url + 'deployment/', headers=headers, data=deployment_data)
    print(response)
    print("Form redeployed successfully")
    string_output += "Form redeployed successfully" + "\n"

#----------------------------------

#List of Kobo form asset_uid
[Put the here the ID of the form(s) to which the .csv file(s) should be uploaded]
FORM_1_ID = “”
FORM_2_ID = “”
#etc.

#Path to the different csv files to be uploaded

GENERAL_PATH = r"G:\Shared drives\Drive\Folder"
CSV_PATH = r"\CSV subfolder"
CSV_FILE_1_PATH = GENERAL_PATH + CSV_PATH + r"\csv_file1.csv"
CSV_FILE_2_PATH = GENERAL_PATH + CSV_PATH + r"\csv_file2.csv"
#etc.

print("project path: " + PROJECT_CSV_PATH)

#Linking the forms to the relevant csv
INPUT_LIST = {
“form1”: {“form_id”:FORM_1_ID,“csvs”:[CSV_FILE_1_PATH,CSV_FILE_3_PATH]},
“form2”: {“form_id”:FORM_2_ID,“csvs”:[CSV_FILE_2_PATH,CSV_FILE_3_PATH]},
#etc.
}

print(INPUT_LIST[‘user’][‘csvs’][0])
print(INPUT_LIST[‘user’][‘csvs’][0].split(“\”)[-1])
print(“\”)

note that we upload the .csv files one by one.
#It would surely be more efficient to upload once, for a given form, but I could not justify the time to do it!
for form in INPUT_LIST:
print ("updating " + form)
string_output += "updating " + form + “\n”
for csv in INPUT_LIST[form][“csvs”]:
print ("uploading " + csv)
string_output += "uploading " + csv + “\n”
if csv == INPUT_LIST[form][“csvs”][-1]:
upload(INPUT_LIST[form][“form_id”],csv,True)
else:
upload(INPUT_LIST[form][“form_id”],csv,False)
string_output += “\n”

#Write a little summary of the update in a newly created file

OUTPUT_FILE_PATH = GENERAL_PATH + r"some path "
complete_name = OUTPUT_FILE_PATH + datetime.now().strftime(“%d-%m-%Y %H-%M-%S”)+ “.txt”
file1 = open(complete_name,“w”)
file1 = open(complete_name,“w”)
file1.write(string_output)
file1.close()

And the GAS code that import the data from Kobo into Google Sheet.
Making and downloading the .csv file is (to me at least) more straight forward, but I can also provide that if needed

The constant are defined in another file.


//Gather all the data stored on the Kobo server, and fetch it to this spreadsheet
function fecthAllDataFromKobo(){
for (var key in MAP_FETCH_KOBO){
Logger.log("Fetching data for: "+key);
fetchDataFromKobo(MAP_FETCH_KOBO[key].formID,MAP_FETCH_KOBO[key].exportID,SpreadsheetApp.getActiveSpreadsheet().getId(),MAP_FETCH_KOBO[key].sheetName);
//Use the line below to update the placeholder data that is present on the report template
//fetchDataFromKobo(MAP_FETCH_KOBO[key].formID,MAP_FETCH_KOBO[key].exportID,ID_SS_TEMPLATE,MAP_FETCH_KOBO[key].sheetName);
}
}

// Make an HTML request to the Kobo toolbox API, via the export link generated on Kobo.
// Parse the data (that arrives as a csv), and transfer it to sheet.
// The form is identified via formID (asset_uuid of the form on Kobo)
// exportID is the uuid of the export setting.
// See the M&E documentation to know how to find those values
function fetchDataFromKobo(formID,exportID,ssID,sheetName) {
var url = BASE_KOBO_API_URL + formID + EXPORT_SETTING_SNIPPET_URL +exportID +“/data.csv”;
Logger.log(url)
// Set up the request headers
var options = {
‘method’ : ‘GET’,
‘headers’ : {
‘Authorization’ : 'Token ’ + API_TOKEN
}
};

// Fetch the data
var response = UrlFetchApp.fetch(url, options);
var csvData = response.getContentText();

// Parse the CSV data
var parsedData = Utilities.parseCsv(csvData, ‘;’);

// Get the relevant sheet and clear existing content
var sheet = SpreadsheetApp.openById(ssID).getSheetByName(sheetName);
sheet.clear();

// Insert parsed CSV data into the sheet
sheet.getRange(1, 1, parsedData.length, parsedData[0].length).setValues(parsedData);
}