Store Account Id of Submitting User in Submission

[grahame]

Hi

Formtools 2.2.6

A bit of a newbie with Formtools but I dabble with PHP/MySQL.

I have been building Internal Forms. So I understand that my users will login to Formtools, choose a form, complete the questions and submit it… At some stage an ft_form_xx table is built to store the submitted answers (where xx is form_id). All great so far!

What I want to do, however, is store the details of the user submitting the form so that I can later retrieve their email and so on (i have added the extended account fields module). As far as I can see, I probably need to store their account_id from ft_accounts and add it into the ft_form-xx tables as they submit their form.

I've been reading up on the general concepts here:-

http://forums.formtools.org/archive/inde...898-1.html

but I'm still a little in the dark… so a couple of questions please:

Q1. - How can I ensure the ft_form_xx tables get created with an extra column 'account_id' automatically (I don't want my form builders to have to remember to add this as a field manually)?
Q2. - Out of the 100's of files on my web server, where do I grab the session variables (incl account_id) and push them into the intended ft_form_xx/account_id column as the form is submitted?

I am guessing I need to add some PHP code in one of the /public_html/formtools/admin/forms
files? Ahh yes, but which one? :o)

Thanks in anticipation

[grahame]

Ok I have found and installed the Hooks module which looks useful!

In an effort to teach myself how this fits together, added the following code as PHP and a Template Hook on:-

forms/add/index.tpl - add_form_page

$account_id = $account_info["account_id"];
$extended_settings = ft_get_account_settings($account_id);
print_r("account id=".$account_id);

Alas, when going to the Add Form page, I get the text part of the message but no account_id?

Any advice please? Thanks

[grahame]

Hi

I am getting so close I can taste it!

I have manually added a text field, account_id, to my form then changed my hook to a Code Hook in ft_create_blank_submission,end

Code:

$account_id = $account_info["account_id"];
$table_prefix = "ft_";
$query = "UPDATE {$table_prefix}form_{$form_id}
  SET account_id = 'Account_ID='{$account_id}
          WHERE submission_id = $new_submission_id";
@mysql_query($query);

Eh voila! My account_id field gets updated with the "Account_ID=" text ….but still not the $account_id…..

So anyone know how I grab the account_id of the currently logged in user please?

Thanks

[Joe]

Hey Grahame,

Reading through your thread I think connecting to an external form and using the http://modules.formtools.org/submission_accounts/ might be a better route to take. You might want to take a quick look at it.

Cheers,

Joe

[grahame]

Hi Joe

Thanks for the reply. I did have a quick look at that but in the meantime I seem to have cracked my problem. For anyone else who needs this, here's the code in my hook:-

Code:

$account_info = ft_get_account_info($_SESSION["ft"]["account"]["account_id"]);
$acc_id = $account_info["account_id"];
$table_prefix = "ft_";
$query = "UPDATE {$table_prefix}form_{$form_id}
  SET account_id = '$acc_id'
          WHERE submission_id = $submission_id";
@mysql_query($query);

Now I am off to crack the Enigma Code!! :o)

Thanks

[grahame]

And to automatically add an account_id field to a form I used:

Code:

$table_prefix = "ft_";
$query = "ALTER TABLE {$table_prefix}form_{$form_id}
          ADD account_id mediumint(8) DEFAULT 0";
@mysql_query($query);

In ft_finalize_form,end. Suggest a default of 0 is set in case any of your code references account_id and, for some reason, there isn't one set yet?

HTHs someone...

[Joe]

Awesome! Thanks for posting your solution Grahame!

Cheers,

Joe

[grahame]

Hi

It turns out there is a little more to do to get this working productively. When the admin creates a new form, I think a 'default' view is also created. Now although the Account_ID column has been added OK, you still cannot use it in the 'default' view because the new column has not been assigned to that view.

So… again, in the ft_finalize_form,end hook, first add the new column to the form_fields table:

Code:

$table_prefix = "ft_";
$query = "INSERT INTO {$table_prefix}form_fields (form_id,field_name,
  field_size,field_type_id,is_system_field,
  data_type,field_title,col_name,
  list_order,is_new_sort_group,include_on_redirect)

  VALUES($form_id,'Account_ID',
         'medium',1,'yes',
         'string','Account ID','account_id',
          (SELECT * FROM (SELECT MAX(list_order) +1 FROM {$table_prefix}form_fields
WHERE form_id = $form_id ) AS X),'yes','no')";
@mysql_query($query);

Then you need to add the column-view assignment

Code:

$query = "INSERT INTO {$table_prefix}view_columns (view_id,field_id,list_order, is_sortable,auto_size,custom_width,truncate)
  VALUES((SELECT view_id FROM {$table_prefix}views WHERE form_id = $form_id), (SELECT field_id FROM {$table_prefix}form_fields WHERE form_id = $form_id AND col_name = 'account_id'),(SELECT * FROM (SELECT MAX(list_order) +1 FROM {$table_prefix}view_columns
WHERE view_id = (SELECT view_id FROM {$table_prefix}views WHERE form_id = $form_id) ) AS X),'yes','yes','','no_truncate')";
@mysql_query($query);

Please keep in mind, I'm a newbie at Formtools, so check everything carefully!!

[Joe]

Thanks for posting back to the Forum. We love it when our users try out new code and post back their updates.

Cheers,

Joe