How can I get a form to re-populate based on that user's previously submitted data? The data was previously submitted in a form called "Controller Registration" Form ID = 3.

I want the user to be able to return to this form and update their details. This means that I load the form and, if the user is logged in, I want all the fields to populate with the previously submitted data. There are about 10 fields in this first data set - StaffNo, title, name, surname, address1, address2, town, county, postcode, country, telephone, mobile, date of entry, etc. I believe I want a single PHP script in the "Script called on form display" area rather than an individual script in each field. Can anyone help me?

This is what I have got so far:

$user = JFactory::getUser();
$db = JFactory::getDbo();
$userId = $user->get('id');

// Is the user logged in?
if ($userId) {
// Grab the value from the Screening Controller database.
$db = JFactory::getDbo();
$db->setQuery("SELECT 'StaffNo' FROM #__rsform_submissions WHERE formid=3");
$StaffNo = $db->loadresult();
$_POST = $StaffNo;
echo "$StaffNo = ".$StaffNo;
}
else
{
$_POST = "User not signed in";
echo $_POST;
}

The result of the echo "$StaffNo = ".$StaffNo; line is "StaffNo = StaffNo"

Hello,

You won't be able to use $_POST nor would populating fields work this way. You'll need a different approach if you don't want to have a script in each of your field.

Achieving a dynamic functionality is rather tricky. Example:

- you'll firstly need to check if the user has submitted the form before. #__rsform_submissions (table) stores the UserId when a logged in user submits a form.

- either multiple queries or a single query (LEFT JOIN tables - this can be resource demanding) in order to return information on the followings:

a) Component Types - #__rsform_components (table) and #__rsform_component_types (table)

You'll need to know their type.

b) As you now know their ID and type, you'll require their name (basically the name you've added on your fields) #__rsform_properties (table)

c) Finally, the submitted values #__rsform_submission_values (table)

- the script then replicates, based on the queried data, a built-in RSForm!Pro URL syntax which is normally used to pre-fill form fields.

- eventually, the page reloads with these URL parameters that fill the form fields.

This can get quite complex and you'll need to understand the tables relationship in order for it to work properly.

I would still recommend, even for 10 fields (which is not that much in my opinion), using a separate script as mostly the query code is different.

Adrian, Thanks for the reply. The first problem that I have is that the original form (FormId = 3) that records all of the company information will not be submitted by a logged in user, it will be submitted by any member of the public. The data will be manipulated and a new field called "Account Number" will be created. The email that is sent to the company email address contains a link. Clicking the link takes the user to a form (FormId = 4) where they can register as a Controller. The Company name and Account Number fields are pre-populated from the URL. This is working well. The User then completes the form and submits it whereupon they receive a confirmation link to activate their account. Once done, they can then log in to use the facilities as a "Controller".

Once logged in, the Controller can add more Controllers (FormId = 4), executives (FormId = 5) and Applicants (FormId = 6). All of these users require a reference number that is linked to the Company Account Number stored in FormId = 3. All of these new users will receive a confirmation link and, for each Applicant added to the system, the Controller will have to make a payment, the invoice will be in the company name. Therefore, I need to be able to pull data across from the data stored in the Company Registration form (FormId = 3) at many different points in the operation.

Additionally, the Applicant will complete many forms to record their previous employment history, periods of further education and references. All of these entries require a reference number linked to the applicant's data stored in FormId = 6. Therefore, I need the same methodology to pull data from that form to associate to these new records.

I have used SQL statements in many different applications to access data in various databases. I cannot believe that this is so difficult. I have found many examples of populating a drop down box or a list with data from a database. However, I don't know how to convert that for use with RSForm Pro tables. In short I want to:

1. Use an SQL statement to find a single record in FormId = 3;
2. Load all of the data from all fields in that record into variables and;
3. Copy each of those variables into RSForm Pro form fields on the current form.

In another part of the system I want to be able to allow the user to review and update their data. To do this I want to:

1. Populate a form with the previously submitted data (the data from the RSForm Pro tables);
2. Identify whether any of the data has been changed and;
3. Update the database with the new information.

I can't believe that what I'm asking for is too difficult for Joomla or RSForm Pro to handle. I have been doing this in C, LotusScript, HTML and VisualBasic for years. I just need it done in Joomla.

Thanks in anticipation of your help.

Hello,

1. Since you have the user logged in, you can try ("Script called on form display"):

- using their "Account Number" you could perform a query and find that particular submission which took place using the FormId 3. Table in question (#__rsform_submission_values).

- from the same table, finding this Account Number, will also find the "SubmissionId" (this is unique per submission, over all your forms).

- knowing the actual submission ID, you can perform another query on this table (or only one combined single query on the same table) to return `FieldName` and `FieldValue` that have this submission ID.

- you should now have all according submitted data from FormId 3.

As you cannot simply pass from "Script called on form display" data to your fields, you can use $_SESSION for example to store these. Capturing this on each individual field can be done using their Default Value area where you can return $_SESSION values.

2. Have you tried using the built-in Manage Directories feature?

This basically provides a frontend listing of a form's submissions (that can be set to list submissions based on the logged in user), from where the user can edit their submitted data. Note that this doesn't actually provide a full form's functionality, but can be indeed used to edit submitted data.

More on this topic here.

Adrian, thanks again for the input.

You wrote "- using their "Account Number" you could perform a query and find that particular submission which took place using the FormId 3. Table in question (#__rsform_submission_values)". Can you provide an example of the script I would need to use to make this work. The user would have a staff reference number in the format "F123-899". The "F123" is the Company Account Number (field name of "Account" in the company database) and the "899" is the UserId for that User. Please take the output from the database query and load some variables ($company, $town and $phone) with values from the fields "Company", "Town" and "Phone".

Thank you for your time.

Hello,

1. Let me firstly get this straight:

- public users submit FormId 3 and "Account Number" is created (this implies FormId 3 will store the Account Number - assuming you do store this within a hidden field for example).

- users are then redirected via an email URL to FormId 4 from where "Account Number" field is populated through a URL parameter. Submitting the form will register the user (doing so, their user ID is captured by the form - assuming as well that you're using the RSForm!Pro Joomla! User Registration plugin).

Besides the user ID, the Account Number is also stored with this FormId 4 submission that created the user.

To sum up, you have 2 submissions from 2 different forms that have the same unique Account Number (this will be used to find the FormId 3 initial submission), while one of these submissions also has the associated user ID.

2. Once logged in ("Script Called on Form Display"):

- capture user ID:

- find the first FormId 4 submission that created the user and return the Account Number:

- using the $AccountNumber variable, find the initial FormId 3 submission ID:

- knowing the submission ID, you can now return data:

- you can print $initialData contents in order to view object structure using:

Adrian, thanks once again for your time in helping me. I have not made this work yet, but I'm working on it.

Your comments show that you have grasped my intended use of this code. However, I notice that you show how to extract some data based on the Account Number, but at the first opportunity you go back to using the SubmissionId. I believe that I have no use for the SubmissionId as all of my records will be linked via the Account Number. Am I right in saying that if I use the SubmissionId only that user can see only the records that they submitted? I intend that a "Controller" can access all records for a Company, regardless of which "Controller" or which "Applicant" submitted the data. However, an "Applicant" will only be able to view the data that they input (in many different forms). Look at it as a Manager and Staff set up except that all Managers in a company can see what all the other Managers have input and all Staff have inpit, whether created originally by them or by another Manager. In order to give this top-down, multi-manager set up I would either need to develop a look-up table that links all SubmissionId's together in their various permutations or, forget the SubmissionId and work only with Account Number. All staff will have a staff number that is linked to the Account Number of the Company and all of their sub-records will have a reference number linked to the Staff Number, which in turn links to the Company Account Number.

So, to the question. Am I right to ignore the SubmissionId and focus on the Account Number? Or, is there an easier way to do this?

Hello,

Glad to be of help. The provided code should be treated as an example and adjusted to better suit your needs.

Yes, you could work using only the Account Number as it's unique per user. However, you'll have to store it on each of your forms submissions. Doing so, correlating submissions would be feasible.