Registration with unique email address

[Yannick François]

Hi,

When a user made its registration, can we check if the address email already exists in the database and reject the inscription if exists ?

My idea was:

Use a user views on "fake" registration (create when the web page is loaded with each fields null) to check the address email from the form. If exists, I delete this registration or the user must change its email, else I update the registration with information from the form.

But it's not the good way I guess, also I don't want to use an external website.

Thanks for helping me 👍

[couch]

You do not say what XMPie product you are using.

1) uStore

uStore uses the email address as the "username" for login/authentication.

Therefore, it is not possible for someone to register with the same email address.

2) Cross media.

This is more tricky and you need to write the code yourself. You can write a small web page that can be called via AJAX that is used for validation of the registration form.

[Yannick François]

Hi,

Yes sorry to forget it, I'm using Circle with internal website. Indeed it's tricky to do but it could be a nice alternative. And with Plan, can we made the address email unique ?

[couch]

uPlan only worries about uniqueness of the primary key of the recipient table.

You could make the email address the primary key (not very secure from a privacy perspective).

And, even then, uPlan handles the uniqueness by adding either a sequential or random number to the end of the primary key - so it would also break the email address from working as an email address.

The plan file is behind the scenes handling the logic of the data and not the communication back to the customer who is self-registering or referring a friend in the website.

What you want is a good feature request, but I think it is more for the XMPL team rather than the uPlan team.

Today, it is possible only with custom code in your website.

[Robin Sharp]

Hey a little off this subject, but do you have any idea what this error is? I get when trying to link a new mail list. I can link an old list no problem.

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xmpie-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xmpie-users/df7f03f0-c744-4ced-86b3-caf23d59e377n%40googlegroups.com.

[couch]

Hi Robin,

Sorry, but I deleted your other message since it included recipient data, which is most likely sensitive and should not be published publicly.

Anyhow, your problem is that the data contains two columns with the same name "ST" and "St". Simply open the file in a text editor and change one to say "ST1" or something else. 

You cannot have two columns with the same name.

[Robin Sharp]

Thank you I'll try that

To view this discussion on the web visit https://groups.google.com/d/msgid/xmpie-users/3e4b1085-6ab0-4c50-af02-b42854354429n%40googlegroups.com.

[Yannick François]

Thanks for this response 👌

But I tried something else:

- You must have 1 recipient in your recipient table, it's important because I get its xmpieRecipientKey

- In my register page (anonymous), I fetch my recipient adors with my first recipient. One of his attributes is a user views to get each recipients.

- With javascript, I check if the address email is include in the user views

Maybe it's more comprehensible in my screenshot:

But I don't understand why my first user has 1 row in user views, the second has 2 rows, the third has 3 rows and so on. My plan isn't good ?

[Robin Sharp]

Thank you, it worked....

To view this discussion on the web visit https://groups.google.com/d/msgid/xmpie-users/3e4b1085-6ab0-4c50-af02-b42854354429n%40googlegroups.com.

[couch]

I see where you are heading and am not sure that this is a good approach for these reasons:

1) How will it scale? How many recipients are you expecting to get in your campaign? Creating a user view with hundreds of thousands of email addresses every time someone hits a web page will not be good for performance, and every time someone is added to the database, the problem will get worse.

2) Security. Effectively, you are exposing every email address in your customer database to anyone who can get to the referral page.

Having a simple ajax call check if an email address exists and getting true/false back will scale without performance degradation, and is a much smaller security risk.

[Yannick François]

It's a tiny website and there's not a big traffic. You right, in the network inspector we can see the request so .. the email address. But I don't know how to do with ajax call because I must checked the database with a get or post request and the API doesn't provide these routes. This solution will be the better. What's your idea to perform a get/post request (with internal website) ?

[couch]

You need to write the code for the page which is called by ajax to test if the user exists or not.

If you are not up with writing this kind of code, you can work around by using another small circle project that accesses the same database. 

I will try to create  you a better documented example next week, but here is the basic run down:

Create a plan file which has just one field in the recipient schema "email" and just one text ador object "email" that reads the schema value. There is no need for filters, queries etc.

Create a new circle project and link to the same uProduce server that you are using for the other project and upload the plan file.

If the first/main project is using a SQL table already, great, you will know where and what it is and can setup the new project datasource to be exactly the same database/table.

However, if you uploaded excel or csv, then you need to identify the right table and create a view from it... Login to SQL Mgt Studio. Go into the XMPDBHDS database and expand the tables list.

If the file you uploaded has a distinctive name, you should be able to find the SQL table pretty easy. If, like me, you have many "customer" files uploaded, you will first need to identify the schema ID that your campaign is using. If you need that, go into the XMPDB2 database, and open the TBL_CAMPAIGNS table. identify your campaign name, and get the campaign id. then in the TBL_DATA_SOURCE and get the scheme number from the datasourceConnection column of your campaignid row.

Now, back in the XMPDBHDS table create a new view which is basically a simple query: SELECT email FROM XMPieHDSSchema00000.tablename - obviously, you need to set your schema ID and tablename. Name the view something sensible like the tablename + "_view"

Back in circle, after uploading the plan file, go to the master list area of the library and setup the datasource pointing it to the SQL server and the XMPDBHDS database. When done, click the link to "add table" type in the name of the new view that you just created.

Now, go to the website tab and download the xmpcfg.js file.

OK. That's all you need to do in circle (no need to create touchpoints, or add a website.

Now to build your AJAX call, you simply call the XMPL REST api of the new project. The URL to call is determined by values in the xmplcfg.js that  you  just downloaded.

<url>/v1/projects/<accessToken>/adorvalues/context/?adors=email&rid=<emailToLookFor>

replace <url> with the url from the xmpcfg.js file

replace <accessToken> with the accessToken from the xmpcfg.js file

replace <emailToLookFor> with the email from the form page that you want to check before inserting...

The url will reply with either:

{"DisplayMessage":"Recipient ID is not valid","ExceptionMessage":"GetADORsValuesForRecipient failed. Exception details: The Port has returned an error message. Check uProduce Job Center, or Link to a different port. populationGUID=asdf recipientID=adsf","ErrorCode":5,"StatusCode":500,"RecipientID":"asdf","ServiceToken":"asdf","StackTrace":""}

or

{"result":{"email":"sco...@gmail.com"},"login":{"recipientID":"sco...@gmail.com","serviceToken":"asdf"}}

So based on the reply from the API, you can either go ahead with the insert because the email does not already exist in the data, or display a nice message to the user about the duplicate.

[Yannick François]

Woh! Amazing Job 👌, thanks you very much !

I'll try this method this morning and I'll give you a feedback (if its works for me 😁)

[Wayne]

If you are going to the trouble of going to the database why not just add a  CONSTRAINT to the SQL Table

Tested in uProduce but should also work for XMPL - see example below

  USE XMPDBHDS

  GO

  ALTER TABLE [XMPieHDSSchema4037].[Sheet1]

  ALTER COLUMN RecipientEmail nvarchar(255) -- Required because column needs to be less than 400 characters for indexing.

  GO

  ALTER TABLE [XMPieHDSSchema4037].[Sheet1]

  ADD CONSTRAINT AK_Sheet1 UNIQUE (RecipientEmail); 

Regards,

Wayne

[Yannick François]

Hi Wayne,

I guess it could be a solution but the previous solution allow to handle easily the response. Do you know what is the response from the server with your idea?

[Wayne]

XMPL should throw an error - as mentioned I tested in uProduce which errors when trying to add non unique emails.

Should be easy enough to handle the error in XMPL

The issue with doing all your validation or checks client side is that it can be easily circumvented - really depends if its a real requirement or just cosmetic.

Regards,

Wayne

[Yannick François]

@couch : I tried your method and it's works perfectly 👌 But if we replace or remove data source, we must rename the table name in the sql view. this is not a problem but you have to think about it

@wayne: I'll create a test project to try it 👍

[Viktor]

How about a more XMPL without that much frontend programming?

Maybe you could send everybody that does register to the webpage an confirmation email (maybe already the case).

When sending the confirmation email you could have an Ador checking if the email already exists or not.

If the email already exists, than you could flag the user as allowed in the db.

This way the already existing user will get a confirmation email that says: "Sorry you are already registred"

The new customers would get an email saying "Thank you for registring. Please confirm your email address with a link.

This way you would have made sure the email address is correct and you will have a clean unique db with approved users.