Autofill field & delete original record.

[linmtheu]

Hi,
i want to create a form similar to the screenshot,

One (databasejoin) dropdown,
& a button that autofills a field according to the dropdown & at the same time deletes the original record from the table that the autofilled data originated from.
What i'm interested in, is every autofilled data entry to be used only once and by one user. (If there are two or more users online at the same time they never get the same autofilled data.)
(Maybe it would be more practical to flag the original entry with a hidden 'used' field instead of deleting it?)
Any ideas?

Thank you

 

[linmtheu]

Any suggestions from the support staff would be welcome.

 

[cheesegrits]

Well, the autofill part can be done with the 'autofill' form plugin.

To remove the selection from the source table, you'd need a PHP form submission script, running onAfterProcess to change the flag ... something like .

$fooId = $formModel->formDataWithTableName['yourtable___foo_raw'];
$fooId = is_array($fooId) ? $fooId[0] : $fooId;
if (!empty($fooId)) {
   $myDb = JFactory::getDbo();
   $myQuery = $myDb->getQuery(true);
   $myQuery->update('foo')->set('used = 1')->where('id = ' . $myDb->quote($fooId));
   $myDb->setQuery($myQuery);
   $myDb->execute();
}

And add a "WHERE filter" to the 'foo' join of ...

{thistable}.used != 1

You would probably also want to add a PHP validation to the 'foo' join, to check for the case where two people have loaded the form at the same time, and hence possibly submit with the same selection (user 2 loads after user 1, but before user 1 submits).

Something like ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery();
$myQuery->select('used')->from('foo')->where('id = ' . $myDb->quote($data));
$myDb->setQuery($myQuery);
$used = $myDb->loadResult();
return !empty($used);

Obviously change the table and field names in the queries to suit.

-- hugh

 

[linmtheu]

Thank you Hugh,

but if the submission php is on onAfterProcess then if two users load the form at the same time (user 2 loads after user 1, but before user 1 submits) won't they still the get the same autofilled data presented to them?

Could i do the autofill & set('used = 1') on a single query?

 

[cheesegrits]

Nope, there's no hooks in the auto fill plugin on the server side. I think we fire a JS event when the autofill AJAX call completes, so you could then fire your own custom AJAX call to do it (see user_ajax_example.php at the top level of the front end component folder).

If course there are several problems with doing it before form submission. If the user changes their mind, and re-selects a different option, then you've marked one used that isn't (unless you handle that as well, to unselect). Or if they never actually submit the form.

And even if you do this in the form, it still doesn't stop two users seeing (and selecting) the same options in the dropdown, as user 1 making a selection won't trigger a reload of user 2's dropdown options. Also, as there's no custom WHERE clause in the autofill plugin, unless you are deleting rows rather than marking them used, it'll still allow user 1's selection to be filled by user 2.



Sent from my HTC6545LVW using Tapatalk

 

[cheesegrits]

What you might be able to do is enable AJAX validation, and use the validation I suggested. That would mean that as soon as user 1 submits, if user 2 makes the same selection (after user 1 has submitted), the AJAX validation should fail. Or, if you do the "roll your own AJAX call" thing, then it would fail after user 1 has made the selection (but you still have the issue of possibly marking used when they aren't, which may or may not be a problem for you).

-- hugh

 

[linmtheu]

If course there are several problems with doing it before form submission. If the user changes their mind, and re-selects a different option, then you've marked one used that isn't (unless you handle that as well, to unselect). Or if they never actually submit the form.​

This is not a problem (i can maybe put a second flag at formSubmission 'used_final' and at the end of the day cron-filter out the entries that only have the first used flag.)

And even if you do this in the form, it still doesn't stop two users seeing (and selecting) the same options in the dropdown​

I was intending the dropdown to point to a different(immutable) table with just the categories available to filter the autofill not the table with the data, so no problem here.

What i want is to avoid validation errors at the autofill for the frontend users.
Maybe i can ditch the autofill plugin completely, split the form to multipage and do the autofill & the set('used = 1') at the same time only with php at onSavePage ?

What do you think?

 

[cheesegrits]

Try it and see. Although same issue, you'd have to roll your own AJAX code to do it on the page change. We fire a Fabrik JS event at start and end of page change, iirc. So you could call your own custom code, update the other table, return your value, and set the element on the page.

-- hugh