Auto Increment Field

Dear Ingmar,

Trusting you had a good break.

I just wanted to make you aware of a problem I have encountered in our workflow.

A competitor enters and we assign them a Tally Band number and they are informed of their entry details via "receipt email".

Other competitors enter and the Tally number rises for each entry.

We then Edit (via the front end) the first competitor's information (adding payment received, GPS unit allocated etc,) But the Tally auto increment field SQL is active and has assigned a new Tally number.

Which is a real nuisance as we use the Editing of a record to trigger an Email with a nicely formatted PDF with all of the information to be used in the office during the event, our information is different to that we gave the competitor earlier. And we don't want Tally numbers changing.

In addition it actually "breaks" the Auto Increment field in the form for the next entry as when editing we have effectively assigned a higher Tally number based on MaxID but to an existing ID (so MaxID doesn't change)  so the next real entry also tries to use the same Tally Number but fails due to the forced unique value. 

The joys of  testing -  I hadn't foreseen this!

For my workflow I need to "turn off" the SQL in Edit mode or prevent that field from being updated.

In the Joomla backend when editing the record the Auto Increment SQL field (Tally Number) is not displayed  and isn't updated - so whatever causes that behaviour is what I need in the front end.

I'm was hoping there would be an option for this but I haven't found it yet.....

All the best

Nick

Hello Nick,

yes that is correct.

The SQL statements in the SQL fields are executed each time the form is called - this includes submitting the form and editing the data in the front end.
Therefore, if the SQL has an auto-increment, the 'Edit form data in frontend' cannot be used.

There is no quick remedy for this, at least none that ensures the uniqueness of the values.
You may be able to use other means to create uniqueness, such as the user ID of the logged-in Joomla user.
The user ID of the logged in Joomla user is available as a placeholder.
But then no strictly ascending and completely freely definable number assignment is possible.

We will introduce a new option for SQL fields to disable recalculation for frontend data editing.
I have created a development issue for the feature wish list:
Issue [VF-795] Forum: SQL Field: Data Edit in Frontend: Add Option: Use saved value instead of SQL re-execution

When we release the new feature, you will be explicitly informed about the release here in the topic.
Thank you again for your contribution!

Kind regards, Ingmar

Hi Nick,

Alternatively, if the data is only sent with the edit mail anyway, you could use an edit only field.
Field Type: text.
With a default value which is set using an SQL statement.
Put the autoincrement SQL statement in there.
The "Edit Only Field" parameter is on the Advanced tab in the field configuration.
The Edit Only field is not included in the form view.
Default values ​​for Edit Only fields are set in php after clicking on the send button and saved with the data record.
The auto increment mechanism is only called once (because a default value is used).
The saved value is then always displayed and re-used.
The time between setting the default value for Edit Only and saving the data record is short.

The only disadvantage of using a default value is that duplicate values ​​cannot be 100% ruled out.

This may be a problem when a large number of transmissions come in at the same time.
But, if there are duplicate values, the values ​​can be corrected when editing the data.
And I think, that only view record sets will be effected.

With this mechanism, the easiest way would be to just increment the number in the Edit Only field.
In the formatted edit mail to the user, the field placeholder can be used in conjunction with text.

Regards,
Aicha

Thank you both. I'll try implementing Aicha's suggestion. With about 100 entries over a few weeks the risks of duplicates is very small.

Nick

Yes, using the Edit only Text field and the SQL to set a default value works fine. Thank you.

However I have seen a similar problem exists with my asking for date of birth. I have chosen to set a date field with a default value (todays date) outside of the maximum and minimum age range set. So the competitor is forced to input their age. But again the data they submit gets over written in the form on front end edit.

So if it were possible to code a generic solution that enables a field to be selected as "Not available for front end edit" that would be a wonderful fix for my usage.

I am keen to use the front end edit as I can get other volunteers to do some of the admin work without having to give them backend access .

Apologies for being so demanding and thank you so much for your support.

CORRECTION

Actually the date in the front end edit form doesn't get saved - I guess because it fails to meet the max/min age? If so it doesn't seem to cause an error or give a warning.

So on data export to CSV the original date is still there unchanged and the PDF produced on Edit is also with the correct date..

Nick

Hi Nick,It may sound a bit too bureaucratic at first, but could you please create a separate topic for each question?
I really only see advantages for everyone involved:- The headline indicates and points out what it is about.
- Answers can be formulated more easily, since the often cumbersome reference to the specific question addressed is no longer necessary.
- Only posts that actually belong together are in the topic.
- The topic remains navigable in terms of content and practicality.
- The topic of an answered question can be considered closed.In principle, you can simply copy and paste any question that has already been formulated into a new topic.
It is important that it becomes clear what the problem actually is.

In the meantime I will take a look into this, so that I hopefully can give you an answer in the new post, soon.
Regards,
Aicha