Forums

So here's the setup, It's taken me over a year on and off to get to the point I'm at, and I have to admit.... there have been tears. BUT I've persevered and read pretty much every tutorial, post and youtube that I could find (I don't admit to understanding it all).

I have a form which visitors can fill in to book our services. They have the option to either pay on receipt of an invoice from us, or they can pay online - using the full PayPal redirect. If they pay online, they have the option to pay a deposit or the full amount - and all of that (eventually) works very well. It even stores the record in a database table.

Because some people do just pay a deposit, I had to create a second form that allows them to pay any outstanding balance, and I'm ALMOST there, but I have a couple of problems I could REALLY do with some help in sorting. I've currently got it set up so that I would email the client with a link to their form - this is basically the URL of the form with their unique ID passed as a parameter. The form has a DB Read action which works - so when the user clicks on the link in the email, it brings up the details of their booking in the form. All the fields are disabled, as I don't want the user being able to change anything at this stage. I also have two of the fields duplicated and hidden - these are the item and amount fields. I've had to do that as I don't think you can pass disabled field values to PayPal. So far, so good,when you click on "Submit" it transfers the user to the PayPal screen with the item data and amount completed as you would expect - and they can complete the payment normally.

Now this is where I have the problems. There are two things that I need to happen, but only if the IPN is verified. There is an IPN listener set up for the form, and it works (I set up the email to send the post form data to me, and that shows what came back from PayPal as you would expect), but what I need is for the record in the database to be updated with the PayPal transaction ID, and also for the record to be updated with a flag that says the booking has been paid in full (I plan to check the flag in the future if someone clicks on the link in the email again, so it should (eventually) say that the booking is already paid in full - and not take them to the form.

I have added a field to the database called txn_id which seems to be what is in the post data from the IPN, and there is a DB Save action in the 'on verified' section of the IPN listener, but it doesn't update the field with the transaction id. Also, I'm setting that 'paid in full' flag in a field on the form which has the same name as the field in the database table (PaidInFull), and I'm not loading the value of that field in the DB Read -( otherwise it wipes out the vale I have set for the PaidInFull field in the form, as to start with, the field in the database is blank). I've not used the Model ID - as when I tried, it didn't load any data to the form on the DB Read.

I'm now at a big loss as to what to do next - there was a post from someone who had a similar problem about a year ago, but unhelpfully, he posted that he'd sorted it with a DB Read and some custom code - but didn't share the custom code :-(

All help gratefully appreciated, and thank you for taking the time to read all the way down.

Regards,

Rob.

So just a bit of an update, following on from the above, I added the following code into the Extra params of the PayPal redirect action:

custom=id

My form uses the field id (rather than cf_id) as the unique key, and when the email with the dump of the post data comes back (on IPN verified), the field custom in the array does have the correct id of the record in it.

After the email action in the 'IPN verified' section, I have also inserted a custom code block with the following code:

<?php
$form->data["id"] = $form->data["custom"];
?>

The idea of the above php was to populate a field called id with the value in the custom field in the array returned by the IPN message in the form post data so that when I do a DB Save it would match against the existing record in the database to update it.

After that, I have the DB Save action, and I've set the table to the correct one, but it doesn't update the record in the table - My assumption was that because the post data had the id (against the custom field in the array), and because I had (tried) to set id to be the value held in custom (using the php above), and because my table also has a field called txn_id (which also exists in the post data IPN message) that it would update that record with the transaction ID from the returned IPN data - but it doesn't.

What else am I missing? should I be putting something in the Update Conditions in the DB Save action?

H E L P ! ! ! ! (please)

Rob.

Hi Rob,

Here's how I would do this.

Create a transaction ID (TXID) just as you have. (I use my Unique ID [GH] action to create a short unique string).

After the first submission create the ID and save the record to a database table before the PayPal redirection, email, etc. Update a status column in the table with whatever info is useful here. For PayPal include the TXID in the data you send - I would use the invoice number but a custom parameter would also work.

With PayPal you are supposed to verify the data they send you back before the payment is complete. (IIRC the PayPal Listener doesn't do that it just says OK).

When you get a record back from PayPal it should include the TXID so you can use that to update the transaction record - probably to change the status. I would also use a second table to save the raw data back from PayPal as an audit trail. You can just JSON encode it and save it in a single text column along with a time stamp and the TXID.

You do need to check the data returned from PayPal to look for confirmation of payment. Check their docs - I think it is status or payment_status = Succeeded - you may get several messages back from PayPal with a 'pending' status or something else.

I would check this separately from the PayPal Listener to be absolutely clear what the status is. I'm not sure that the Listener actions are 100% reliable. You can ignore them and do the checking after the Listener action.

There's a difference between having 'disabled' elements in your form and 'readonly' ones - disabled don't submit a value, readonly do.

I hope that helps a bit.

Bob

Thanks Bob, I get most of that, but the bits I don't understand are: How do I create a field called id in the post data and assign it the value of custom from the post data returned from the IPN message, and then get the DB Save action to update the record with that id and also update the value of txn_id in the database record to be the value of txn_id returned in the post data returned from the IPN message - I'm sorry if it doesn't make sense, but I really don't understand what I'm doing.

Thanks again,

Rob.

Hi Rob,

Not quite clear what you are asking here.

To get the value of custom you can use a Custom Code action with code something like this

<?php
$form->data['id'] = $form->data['paypal_xxx']['custom'];
?>

I'm not what the actual value of paypal_xxx is here, dumping the returned data in a debug email will tell you. Add this in the email template:

<?php
echo'<div> $form->data: '.print_r( $form->data, true).'</div>';
?>

We may be at cross-purposes about the transaction ID, I suggest that you create one when the form is first submitted and use that to identify the transaction at each stage - so you send it to PayPal and get the same ID back again. (PayPal may also assign a transaction id but that is not relevant here.)

Bob

Hi Bob,

Thank you again for the response.

So here's the sequence I'm trying to achieve:

Person who has already booked us, and has already paid their deposit wants to pay the outstanding balance of their booking.
There is already a record in the database with their details, and how much is outstanding.
They receive a manual email from us with a link to their booking form - this is simply calling the booking form URL along with their unique id (this is the 32 character id that exists along side their record in the database).
The form has a DB Read action, which loads their record with the unique id that was passed in the URL.
The user then clicks on a submit on the form which calls the PayPal Redirect(full) and brings up the payment screen with the item and amount pre-filled (passed to PayPal in the item and amount fields in the redirect).
There is an IPN listener set up on the form. When the user has made payment, and if the IPN is verified, then the form data is returned back by paypal (example below).

IPN Verified. Here is what Paypal had to say. 
$form->data: Array ( 
  [option] => com_chronoforms5 
  [chronoform] => BookingForm 
  [event] => ipn 
  [transaction_subject] => 
  [payment_date] => 16:24:23 Aug 25, 2016 PDT 
  [txn_type] => web_accept 
  [last_name] => Sealey 
  [residence_country] => GB 
  [item_name] => Partypicz - Outstanding balance 
  [payment_gross] => 
  [mc_currency] => GBP 
  [business] => Sales@zzz.com 
  [payment_type] => instant 
  [protection_eligibility] => Ineligible 
  [verify_sign] => AwsY9US4HFPqe0AuoaqJ-OSIasthAAdTXWzupaa1j3jVoog54NpAzkJA 
  [payer_status] => verified 
  [payer_email] => robert.sealey@xxx.com
  [txn_id] => 4RS435098M848542B 
  [quantity] => 1 
  [receiver_email] => Sales@zzz.com 
  [first_name] => Robert 
  [payer_id] => TGJK4JNMFLNTE 
  [receiver_id] => BC92JKRA3NXLU 
  [contact_phone] => +44 8450042091 
  [item_number] => 
  [payer_business_name] => ZzzLtd 
  [payment_status] => Completed 
  [payment_fee] => 
  [mc_fee] => 0.23 
  [mc_gross] => 1.00 
  [custom] => 78 
  [charset] => windows-1252 
  [notify_version] => 3.8 
  [ipn_track_id] => 27cae8bc2ee57 
)

At the point where the IPN listener receives the IPN Verified message from PayPal, I was Chronoforms to update the database record that it's just been working on (in the above case, the record with the id of 78 - as shown in the custom field) with the transaction id from the array element txn_id (in the above case it's 4RS435098M848542B).

Everything apart from the update of the record in the database with the transaction ID works, so all I really need is how to get the DB Save action to update the database record that has the id which equals the value of the custom element of the IPN post data array with the value of the post data array element txn_id. The database structure has all the fields associated with the booking including the unique key field which is id (which is an integer) and also a field called txn_id which is a varchar big enough to take the value from the post data.

At the moment, in the 'On Verified' section of the paypal listener I have a custom code block which reads:

<?php
$form->data["id"] = $form->data["custom"];
?>

and prior to that, there is an email block - which is where the post data above came from

Finally in that section, there is the DB Save action, which apart from the database table, doesn't have any other modifications.

Does that help in explaining it at all?
I know I need to check the ipn data and all that, but at this stage, I just want to store the paypal transaction ID in the database record - but as far as I know, the only way to get the id is from that ipn data (unless I'm wrong on that too).

Thanks again.

Rob.