Field type sql calculation

Hi,

Sticking to the example of the dog competition, a record includes the score a dog has earned in a category in a competition. Most often, a dog wins in several categories in a competition. I would like there to be a new column where the calculation of competition points would occur. That is, in this cell (now we are in a record), the total points the given dog has accumulated in that particular competition would be visible. Then, in another table, I would like to see the total sum of points for the dog's top five competitions achieved throughout the year. Such a column, as far as I can tell, is not among the options because it requires a more complex SQL command, but I would like such a feature unless you or anybody have a better idea for solving the problem. 

Br.:
Marton

Addendum: The starting question via email:

My client is the head of a dog breeding foundation who would like to have a table where the most successful dogs can be seen according to competitions.
The question is, is there a possibility that once a dog owner registers a dog in the system, from then on the user does not have to register the dog anymore, but can select it from a list, but this dog cannot be selected by another user.

Addendum: The first answer via email:

Thank you very much for the good, concise and precise description.
With Visforms it is possible to implement the following dog selection scenario.

Dog selection scenario:
- dog owner registers a dog in the system
- dog owner can select a dog from a list containing only his registered dogs
- dog selection is limited to dogs registered by the user

The solution uses 2 forms:
- 1st form: registration of the dog
- 2nd form: Selection of a registered dog

Apart from the implementation details, a solution is essentially based on 1 Visforms Subscription Feature.

It is described in the documentation.
Field type SQL fields:
docs.joomla-5.visforms.vi-solutions.de/e...ion/field-types/sql/

There is a very similar example of this in the documentation.
Users and Vehicles:
docs.joomla-5.visforms.vi-solutions.de/e...orms_users-and-cars/

In your case the selection would simply be further limited, which is only noticeable in the SQL statement.

Hello Marton,

Re: not among the options because it requires a more complex SQL command:

The solution with an SQL field and its SQL statement is already the better and more elegant solution.
Visforms has exactly the right and corresponding SQL fields ready.
We have developed these SQL fields and SQL-based default values precisely for such situations.

Re: complex SQL command:

If you can't figure it out, we'll be happy to help you practically.
I hope you understand that we offer this individual service based on our current hourly rate and at least 1 hour.

Kind regards, Ingmar

Hello Marton,

In order to develop the 'Competition Points' field for you effectively and quickly, I also need access to the database.
The question is whether you could provide us with an Akeeba backup of the website for this.
Administrative access to the backend is not sufficient in this case.

We use the following platform for this: wetransfer.com
Use the forum email address as the recipient: forum (--at--) vi-solutions.de .

The website must already contain the solution described using the two forms mentioned above:
- 1st form: registration of the dog
- 2nd form: Selection of a registered dog

Kind regards, Ingmar

Hello Ingmar,

I understand SQL, I just need a column on which I can perform such complex calculations. If I understand correctly, you recommend the existing columns that accept sql, but I'm afraid that they won't be able to satisfy what I want to do. How can I create such a column where I can enter sql statements with few constraints?

br.:
Marton

Hello Marton,

Re: I understand SQL
That is great!

Re: you recommend the existing columns
By 'existing' field I don't mean: fields in the form that belong to the form and are important.
I mean: An additional form field of the special SQL type, especially for your complicated calculation.
This field can also be invisible in the form.

Re: won't be able to satisfy what I want to do
- Why shouldn't a SQL type field be sufficient?
You can make the SQL statement as complicated as you like.
- Have you tried it yet?
- If so, where are the limitations you are talking about?

Re: How can I create such a column where I can enter sql statements with few constraints?

One option for developers is to create a custom plugin.

The documentation describes how to create a 'Visforms Custom Plugin'.
Custom plugin creation:
docs.joomla-4.visforms.vi-solutions.de/d...-plugin-development/
Custom plugin event system:
docs.joomla-4.visforms.vi-solutions.de/d...-plugin-development/
There is a complete custom plugin with all events and help functions as a template that comes with the installation of Visforms:
docs.joomla-4.visforms.vi-solutions.de/d...-plugin-development/

The developer can then decide for himself what else should happen.
In your case, when saving the form data (form submit or data edit), your SQL statement would run in your PHP code in the plugin.
You have access to all form data and the entire database.
You can manually insert any additional field into the form's data table (or an entire table of your own) without hindering the operation of Visforms.
Creating a custom plugin requires some experience in PHP development under Joomla.

If you need help developing the plugin, we will be happy to support you - including with the complete implementation.
Please understand that we have to charge this effort at our current hourly rate and for at least 1 hour.

Kind regards, Ingmar