Tally Band - Need data to seed a valid SQL return, so need to ignore some entries

[nickjpovey]

In the back end we have the option to clear out all old data. However if you do that then the first entry doesn't allocate a Tally band number. It appears to need at least one record in order that the SQL has something to interrogate and return a result.

Work around is to keep at least one record and introduce another field to identify valid entries.

I have made a hidden field "LIVE or TEST" when live I propose that the value is set to the year "2023" and when in TEST it is "1" so when we go live we switch to the year. Mike, your process just needs to use entries with the correct value in this field..

[nickjpovey]

Interesting and on-going problem. I'm hoping the developer will offer a solution.... Other wise all is looking very good.

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 - 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

[nickjpovey]

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

[nickjpovey]

I'll try Aicha's suggestion,

And possibly we'll get a new release before October so might have an alternative as well.

[nickjpovey]

Aicha's suggestion works well. I'm happy to use the process as it is. I am no longer sending Tally Band numbers to the competitor before the event - we may want to make changes. Everything is on the Office Form which gets produced when we edit the entry to allocate Motion IDs. wsw-entry.pdf

[Logiqx]

I think the initial tally band issues have now been overcome.

If there is an issue with duplication it can be a new issue.

Closing this one down