Least Filled / Lowest Bucket / Auto Answer .. all needed ... - Solved

So I have read and re-read the posts around this and there seems to be a lot of broken out information but nothing that I can use to adapt or modify for my needs so throwing this out there to see if there has been a solution found by anyone else.

We have a need to classify participants based on a "lowest bucket" "least filled" approach.

They can be be sorted into 1 of 15 groups based on data from the sample (token attribute) or from answers to previous questions.

I know what I need to do but I am struggling on the exact implementation for this .....


Simply ... have a single choice question ...lets call it segment .. that has 15 answer options and is hidden

Take the data from the sample token and the answers given to determine which of the 15 answers is permissible for that participant.

Check the counts from the database that returns the allowed answers

Sort into lowest numerical answer and populate that answer in the hidden segment question and move on with the rest of the survey.

I know that this can be accomplished by ...

1) An Ajax call in a question that calls a PHP file
2) A PHP file populated by the aforementioned Ajax Call that returns the counts from the database
3) Some JQuery that sorts the data returned from the PHP file (probably in an Array) and then identifies the lowest filled option
4) An equation type question that takes the code of the lowest group and sets that answer in the hidden question

What I need help on is the code that does this ... Not sure if this is a common request but it is a feature of the more established survey packages out there and should be fairly doable based on scripts I have seen in here in relation to Ajax / PHP calls

Thanks so much for you help in advance
Dave

Obligatory Version 2.6.4 LTS with a view to upgrading when we have downtime

That's strange as this is what I see on the admin screen.


I understand what you are saying about the level of work involved but for now I will try to solve with scripts from other posts on here.

Any pointers on relevant posts would help

DenisChenu wrote: There is no 2.6.4lts , 2.6.7 only (and it's not a long time support currently).

To state that there is no 2.6.4-lts is confusing people.


Currently LimeSurvey is only supporting LS 3.X.
When LS 4 is released, the LS 3.X will be LTS.

Denis is offering a branch of LS 2.6.7 LTS.
github.com/SondagesPro/LimeSurvey-SondagesPro

Any investment in modifications of LS should be based on LS 3.X now.
Investing in modifications of 2.6.X LTS should be based on the code Denis is offering.

Personally I wouldn't want to use such workarounds. I would like to see a solid ground inside the LimeSurvey core. Access to database via ExpressionScript.

If LTS is part of the version naming (as clearly seen in the screenshot), it's just existing.
And contradicting that with your statement is adding confusion. It can be read as if such a version (2.6.4-lts) was never released.

I hear what you are saying re: no downtime, however I do have a planned maintenance window set up ... so Christmas it is ...

In terms of development of this then I will probably just hard code this now for this one survey using a simple SQL call to determine the lowest number and auto punch that as the code ... here is where I am at so far and it seems to be working to do what I need....


SELECT {SIDXGIDXQID}, count(*) as totals
FROM surveys.survey_{SID)
where {SIDXGIDXQID} is not null and {SIDXGIDXQID} in (1,3,4) // Range of allowable values that this participant qualifies for this is drawn from the Expression Manager
group by {SIDXGIDXQID}
order by totals asc
LIMIT 1;

So this pulls the answers from the data base counts and sorts them in desc order (so the lowest is at the top) and then returns the code that is the lowest...

Now to wrap this in PHP wrapper .. call it with an Ajax call and use an expression to set the pre / post actions ... all in all simple way to get counts.

just one more thing to do which is to add in 15 dummy completes each with a valid code in the question response that can be deleted later so that the Null select clause returns all the valid values at least at the start of the survey.

I have spent the time working with the posts from here (old time ones as I don't have the new shiny server) and a little trial and error .... although looking at this the hardest thing was the SQL aspect of things so it may work with the new versions with some tweaks... and I thought I would share it here with anyone that is interested.

What this does
It takes answers from a participant and then allocates them to segments that they qualify for on a "least filled" basis so if I am qualified to be in segments 3, 5 and 7 then this approach will look at the counts from the database and assign me to the segment that has the lowest number of respondents in there.

Step 1: Create a multiple choice question which has all the segments in there and populate it however you want to ... mine is a mix of data from sample along with answers they have given (an equation question populates this)

Step 2: Use a second equation question to concatenate the answers from the first, this allows me only to return the relevant codes from the database here is the equation script I used ...

{join ( if (Q1Set_1=="Y","1,",""),if (Q1Set_2=="Y","2,",""),if (Q1Set_3=="Y","3,",""),if (Q1Set_4=="Y","4,",""),if (Q1Set_5=="Y","5,",""),if (Q1Set_6=="Y","6,",""),if (Q1Set_7=="Y","7,",""),if (Q1Set_8=="Y","8,",""),if (Q1Set_9=="Y","9,",""),if (Q1Set_10=="Y","10,",""),if (Q1Set_11=="Y","11,",""),if (Q1Set_12=="Y","12,",""),if (Q1Set_13=="Y","13,",""),if (Q1Set_14=="Y","14,",""),if (Q1Set_15=="Y","15,",""))}

I had 15 possible segments ... note this adds in a final comma and some whitespace that is removed in the next step

Step 3 in a short text question I added the following script ... I used a short text question as this gave the ability to process the Ajax and also a place to hold the data that was returned from the call ... I am sure there is more elegant way of doing this .... but this works for me

Here is the script I added

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {

var apiFilePath = 'https://yoursurvey/balance.php';
var sid = 134625;
var gid = 1393;
var qid = 31145;
var ans= "{GroupList.shown}";
var answers = ans.substr(0,ans.length - 5); //IMPORTANT TO STRIP THE LAST COMMA AND WHITESPACE


$.ajax({
url: apiFilePath, /
async: true,
cache : false,
data: {
sid: sid,
gid: gid,
qid: qid,
answers: answers

},
success: function(results){
$('#answer134625X1393X31146').val(results);
},
error: function(){
alert('Could not connect!');
}
});
$('#answer134625X1393X31146').hide();
});
</script>

So this calls an external PHP file that accesses the data base and then returns the code with the least counts associated to it .. here is the PHP file (username and password changed) .. The way the SQL code is structured means that it returns all answers to the question even those with 0 entries so far .. there is no need in this method to add in dummy data as I was thinking about doing in an earlier post.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$servername = 'localhost';
$dbname = 'surveys';
$username = 'xxxx';
$password='xxxx' ;


$iSurveyID = $_GET["sid"];
$iGroupID = $_GET["gid"];
$iQuestID = $_GET["qid"];
$iAnswers = $_GET["answers"];;



// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT answers.code, COUNT(survey_".$iSurveyID.".`".$iSurveyID."X".$iGroupID."X".$iQuestID."`) AS Total, answers.answer FROM survey_".$iSurveyID." RIGHT OUTER JOIN answers ON survey_".$iSurveyID.".`".$iSurveyID."X".$iGroupID."X".$iQuestID."` = answers.code WHERE answers.qid = ".$iQuestID." and answers.code IN ( ".$iAnswers." ) GROUP BY answers.code ORDER BY Total Limit 1;";

$result = $conn->query($sql);


if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo $row["code"];
}
} else {
echo "0 results";
}

$conn->close();


?>

Then once this is back in the survey ... I have a final single choice question that is set by an equation question with the answer returned from the script and the logic can be used as normal ... also this allows me to set quotas against this to ensure that we hit targets.

{Q1=Q1Outcome.shown}

.......

So there it is ... not the best code I am sure and again there are probably steps that I have taken that I didn't need to, but it works so there is that and I have seen some questions on here asking for something similar so I thought I would share ....

All this is run on version 2.6.4 (lts or not lts dependent on your point of view)

Hi Dave,

I just wanted to say thanks for putting in the effort to look into this.
This was one of the main drawbacks with the quota system that was preventing me using Limesurvey for a lot of projects. I haven't replicated what you did just yet but this certainly gives me a guide in the right direction.

I have yet to make the move up to version 3 but I have a feeling this functionality is still missing in the current release.

Thanks again,
Paul

paulfiner wrote: I have yet to make the move up to version 3 but I have a feeling this functionality is still missing in the current release.

LS 3 is the only version with updates (bugfixes and new bugs) in 2019.

The description here ( www.limesurvey.org/examples/market-research ) seems to highlight the quotasystem for market research purpose.

Take LimeSurvey's inbuilt quota management, for example.Just set quotas to the region of your participants, so you may only get answers of the specific region you are researching. Or try to filter by income, even a lock by IP address is possible.

There might be the misconception that LimeSurvey already has a powerful quotasystem. Which is not the case.

A few feature requests about quota.
bugs.limesurvey.org/view.php?id=12402
bugs.limesurvey.org/view.php?id=11589

Totally agree about the lack of a powerful quota system...it's a very limiting factor for using Limesurvey in market research surveys for which this type of thing (least filled etc) is essential.