Dynamically update available user field values during a profile fill in

Dev
6

I have found a solution to automatically update this specific case. No API update possibility for customfield options , I have only found a server-side solution here through a cron job. I didn’t use the json file, i used the database entries for automation.

The use case

The use case for this automation is to allow new users to select existing options when registering, which can help the system to evolve as more users make their preferences known. If a new user cannot find an appropriate option, they can enter their own text in the custom text field, which can then be added to the dropdown list for the next user.

This should be used with caution, as changes to the databases for Discourse can crash, recommended only for experienced developers!

Requirements:

Steps 1:

Create two custom fields in /admin/customize/user_fields, one with a dropdown and the other with just text (in my example: dropdown with user_field_5 and text with user_field_8).

Note: The ID can be retrieved from /admin/customize/user_fields.json.

Step 2:

It is best to use Discourse Data Explorer to check your inputs: Discourse Data Explorer

Create a new query under /admin/plugins/explorer:

E.g.: User Field Options

SELECT * FROM user_field_options WHERE user_field_id='5'

Change user_field_id=‘5’ to your dropdown user_field_id=‘x’ Then the values that are displayed for your dropdown options will be output.

Create a second query:

E.g.: User Custom Fields

SELECT * FROM user_custom_fields WHERE name = 'user_field_8'

Change ‘user_field_8’ to your text custom field ‘user_field_x’ Then the values that your users have entered for text will be output.

Step 3: This is the basic foundation for your cron job to update the user_field_options database, where all options for custom fields are listed, which have the associated value 5 for me because of user_field_5. I want to add options there.

First, we create a bash script in the directory for Discourse

cd /var/discourse
nano update_dropdown.sh

Add the following content if Discourse is run as Docker:

#!/bin/bash
cd /var/discourse
docker exec -i app su postgres -c "psql discourse -c \"UPDATE user_custom_fields SET value = (SELECT value FROM user_field_options WHERE user_field_id = 5 AND user_field_options.value = user_custom_fields.value) WHERE name = 'user_field_8' AND value IS NOT NULL AND value NOT IN (SELECT value FROM user_field_options);\""

Make sure you save the script and make it executable

chmod +x update_dropdown.sh

Then you can start and test the script

cd /var/discourse
./update_dropdown.sh

Step 4: To automate this, we need to add the bash script as a cron job:

crontab -e

This opens the cron job editor, where you add the code in the next line:

* * * * * /var/discourse/update_dropdown.sh

Then the command will be executed every minute. This can of course be adjusted, and the path must be adjusted depending on where you have stored update_dropdown.sh on the server.

3 Likes