Joomla 5 Notice

We are pleased to announce that as of January 29, 2024, all of our Joomla extensions are compatible with Joomla 5.

For all who are still updateing from Joomla 3 to Joomla 4: Joomla 4 Migration instructions are available here:

There is now a separate Documentation for Visforms for Joomla 4 and for Visforms for Joomla 5!

Forum

Visforms Subscription user can ask questions in our forum. Please log in with the relevant user first.
Everybody can access the forum for reading.

Please only ask 1 question per topic.

Field type sql calculation

More
2 weeks 3 days ago #10243 by gollmarci1
Field type sql calculation was created by gollmarci1
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

More
1 week 3 days ago #10273 by Administrator IV
Replied by Administrator IV on topic Field type sql calculation
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.

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).

More
1 week 3 days ago #10274 by Administrator IV
Replied by Administrator IV on topic Field type sql calculation
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

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).

More
1 week 3 days ago - 1 week 3 days ago #10275 by Administrator IV
Replied by Administrator IV on topic Field type sql calculation
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

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).
Last edit: 1 week 3 days ago by Administrator IV.

More
4 days 23 hours ago #10325 by gollmarci1
Replied by gollmarci1 on topic Field type sql calculation
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

More
3 days 29 minutes ago #10332 by Administrator IV
Replied by Administrator IV on topic Field type sql calculation
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

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).

Moderators: Administrator AVAdministrator IV
Powered by Kunena Forum