Common Token Across Multiple Surveys

More
3 years 1 month ago #112943 by bkane
bkane created the topic: Common Token Across Multiple Surveys
First, as a new LimeSurvey user, kudos to the LS team and contributors on a great software (and community) platform!

I have been reading about (and experimenting with) tokens, but have not found an answer on how to create and maintain a common token for each user across surveys. Here's my application (and why I think I might need to implement a common token):

I would like to use LimeSurvey to drive a multi-step work flow. The process requires a single user to complete multiple surveys. Some of the intermediate process steps may need to be handled outside of LimeSurvey (e.g. custom PHP coding). In order to link all the data collected, I would like to use a common token across all surveys (and external applications). EndURL/PASSTHROUGH parameters such as {TOKEN} seems the perfect approach. When the work flow is fully implemented, it might require 20-30 surveys in total. While the central participants database is helpful in authorizing access to all the surveys in the work flow, editing 20-30 token tables is awkward (even if using update SQL queries). I would even consider using LDAP (OPEN LDAP implementation). What is the best way to accomplish a common token/token table across surveys?

Thanks in advance. Any advice would be greatly appreciated.

Please Log in to join the conversation.

More
3 years 2 weeks ago #114094 by pvdemael
pvdemael replied the topic: Common Token Across Multiple Surveys
Hi bkane,

I am looking for the same thing, but no LDAP for me, just tokens.
Have you found a solution?

Please Log in to join the conversation.

More
3 years 2 weeks ago #114121 by bkane
bkane replied the topic: Common Token Across Multiple Surveys
Sorry for my late reply - preparing for a presentation later today. I have found a working solution and will post details as soon as I can.

Please Log in to join the conversation.

More
3 years 2 weeks ago #114154 by bkane
bkane replied the topic: Common Token Across Multiple Surveys
Credit to Mazi for guiding my thinking here on Changing Token Values

This solution is a work-in-progress...

Problem Statement:

- I have 10 surveys where I would like to use the same token per participant (to link the data).
- Data tables (created by Lime Survey) are named lime_survey_1, lime_survey_2... lime_survey_10
- The token tables (created by Lime Survey) are named lime_tokens_1, lime_tokens_2... lime_tokens_10
[Note: Actual survey numbers are 6 digits and assigned at random, not sequentially.]

Solution(Preliminary):

- Create a staging survey (Survey # 0) with one YES/NO question, activate the survey and enable tokens.
- Let's assume the associated data table is lime_survey_0 and the associated token table is lime_tokens_0 (actual survey # will be different).
- Add a user to the Central Participants Database, add the new participant to Survey 0, and then Generate Tokens.
- Important: If done correctly, the Token Control screen for Survey 0 should display the user you added and an alphanumeric value in the token column.
- The final step is to run a database query (INSERT|SELECT) where the token value is extracted from lime_tokens_0 via SELECT and inserted into lime_tokens_1 through lime_tokens_10 via an INSERT:

START TRANSACTION;
INSERT INTO assess.lime_tokens_1 (participant_id, firstname, lastname, email, emailstatus, token, language, sent, remindersent, remindercount, completed, usesleft)
SELECT participant_id, firstname, lastname, email, emailstatus, token, language,'N', 'N', 0,'N', 1000 FROM assess.lime_tokens_0;
INSERT INTO assess.lime_tokens_2 (participant_id, firstname, lastname, email, emailstatus, token, language, sent, remindersent, remindercount, completed, usesleft)
SELECT participant_id, firstname, lastname, email, emailstatus, token, language,'N', 'N', 0,'N', 1000 FROM assess.lime_tokens_0;
INSERT INTO assess.lime_tokens_3 (participant_id, firstname, lastname, email, emailstatus, token, language, sent, remindersent, remindercount, completed, usesleft)
SELECT participant_id, firstname, lastname, email, emailstatus, token, language,'N', 'N', 0,'N', 1000 FROM assess.lime_tokens_0;
# Note: Add INSERT/SELECT statements for Survey 4-10
COMMIT;

- In my situation, I have opted to set columns "sent", "remindersent", "completed" to 'N', "remindercount" to 0 and "usesleft" to 1000 and ignore columns "validfrom", "validuntil", and "mpid".
- I have also decided to wrap the INSERT|SELECT statements in a transaction. [Note: This part is incomplete, as it should have proper error handling and a rollback option.]
- I believe it is important to leave participants in Survey 0 so that token values are assigned uniquely, but I may be incorrect on how Lime Survey does this. [Note: See future enhancements on better way to handle.]
- And here is a way to link data across multiple surveys:

SELECT * FROM lime_survey_1 AS x INNER JOIN lime_survey_2
WHERE x.token='alphanumerictokenvaluegoeshere';

Future Enhancement Ideas:

- Explore adding an attribute to the central participants database for Survey 0 ("completed"). Initially this would be NULL. Revise the SELECT query to only include records WHERE "completed"=NULL. As the last statement in the transaction, UPDATE the "completed" column to some value, say "Y" to exclude this record going forward.
- As previously mentioned, restructure the MySQL transaction block for proper error handling and rollback of the token/other tables on failure.
- Explore using a database trigger on lime_tokens_0 to cascade token creation.
- Explore automation (RemoteAPI, AJAX, etc.) to reduce manual steps.

Suggestions?
The following user(s) said Thank You: pvdemael

Please Log in to join the conversation.

More
3 years 2 weeks ago #114206 by Mazi
Mazi replied the topic: Common Token Across Multiple Surveys
Thanks for sharing your solution!


Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)survey-consulting.com'"

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!