Load custom field (dropdown) with output from SQL query

I’m working with v24.
I have a custom field called “fee” for the users. The type of field is dropdown.
The list of values of this field should be populated from a list resulting from a SQL query from another table, and on the screen I should be able pick one of the values displayed, as the fee for that user.

Currently I believe the only possiblity for providing the list of values is writing the list as a fixed list in the “options” field below the type field.
This is not a good solution for this case, because if a fee is added, or changed in the fee table, it is not going to reflect in the list of values for the custom field.

Is there a way to fill that list with a SQL query result?

Thank you!!

Hi @rfernandez2007 ,

Here is a possible solution:

You can identify the Custom Field to modify with the following query:

SELECT * FROM gibbonCustomField

Once you know the gibbonCustomFieldID to modify, you can make a query that returns the comma-separated list of values ​​and assign it to the options field.

UPDATE gibbon.gibbonCustomField
SET options = (SELECT group_concat(name separator ‘,’) FROM gibbon.gibbonFormGroup)
WHERE gibbonCustomFieldID = ‘0005’

You can add this query in the module Query Builder > Manage Commands to run it (optional module).

You could also modify one of the Gibbon sources to run this command each time you connect to Gibbon (to refresh the list).

PLEASE NOTE, if you modify a Gibbon source, you must keep this modification in order to redo it when updating to the next version of Gibbon.

Gibbon : v25.0.01, PHP v8.1.2, MySQL v8.0.35, Ubuntu 22.04.3

1 Like

Thank you very much for your answer!
I’ll explore your suggestions to see if I can make them work in my context.
Warm regards!!

Hi @rfernandez2007 this sounds slightly beyond the intended use case for custom fields, but @smarcil’s workaround certainly sounds like a possible solution. One option for running this command frequently could be to put it in a CLI script and setup a cron task to run it daily.

It looks like you may be working with fees and payments, and there is a section of the Finance module that manages fees, fee categories, and invoices in a more robust feature set than custom fields can offer. I wonder if these features could help in this case :thinking:

Hi Sandra,
I hope you are fine!!
I’m aware of the Finance module, thank you.
As far as I understood what I wanted to do is not part of its functionallity.

What I want to do is simply to attach a fee to a person or group of people, so when the billing time comes I just generate the invoices automatically and the process should take the fee from what the person has assigned, instead of having to select each person on screen, then select the fee and finally generate the invoice.
This is because I have many different fees for people, depending of many variables and so I cannot select them all and then simply choose a fee and generate the invoice. Instead every time I need to generate invoices I have to go through this process of manually selecting person+fee many times, and it is a very error prone process.

I hope the reason for which I’m looking for a workaround is clear.
Thank you!!