Validation on open ended survey link

Question

Happy new year all. Fingers crossed things are going to start getting better some time soon!

I have a bit of a random one here... Is there any way to validate a respondent based on the data collected?

I have a survey that is going to have an open link, I want to be able to look at the IP address of the respondent and check against the IP addresses for all other respondents and either return a question that says "It appears someone from your household has already completed this survey, would you like to continue?" or at the least create a flag for me to filter on.

I've validated across columns for one respondent on so many occasions, but validating one column across many respondents is new to me (and I have no idea if it is even possible) - any advice appreciated!

Answer 1

This is possible, but it will require querying your own database using unverified Perl.  The Community Question Library includes a starting point for this here:

https://sawtoothsoftware.com/resources/question-library/read-database

I've modified this code to create skip logic that should fire when a duplicate IP address is found:

Begin Unverified Perl
# Parameters
my $question = 'sys_IPAddress';
my $table = '';

my $databaseName = '';
my $databaseUser = '';
my $databasePass = '';
my $databaseHost = '';

# Read database
my $dbh = DBI->connect('dbi:mysql:' . $databaseName . ':' . $databaseHost, $databaseUser, $databasePass);
my $sth = $dbh->prepare('SELECT `' . $question . '` FROM `' . $table . '` WHERE `' . $question . '` IS NOT NULL AND `' . $question . '` <> ""');
$sth->execute();

my $ip = IPADDRESS();
my $duplicate = 0;
while (my $row = $sth->fetchrow_arrayref()) {
    my $resp = $row->[0];
    if ($resp eq $ip) {
        $duplicate = 1;
        last;
    }
}

$sth->finish;
$dbh->disconnect;

return $duplicate;
End Unverified

You'll need to upload your survey and adjust lines 4-9.  The link above offers more info on what the correct values to use for these are.