Hi. I want to create a badge based on the trust level and the chosen option from a dropdown custom field and I keep getting this message ( also I’m not sure if the rest of the query is correct, so any help is appreciated ):
ERROR: column "Woman" does not exist
LINE 10: ) AND LENGTH(cf.value) = "Woman"
How it should work:
So I’ve created a dropdown user custom field named “Gender” and two options, “Man” and “Woman”.
If user X is a man and registered ( level trust 0 ), the “Warrior Male” badge should be granted, otherwise the “Warrior Female” badge.
Also can someone please tell me where can I see the user field number for all my existing custom fields? ( in my example I’m using the field name, but just in case ).
The query so far (not working):
SELECT u.id, current_timestamp, cf.user_id user_id, cf.updated_at granted_at
FROM users u, user_custom_fields cf
INNER JOIN user_fields uf
ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer
AND uf.name = 'Gender'
WHERE trust_level >= 0 AND (
:backfill OR u.id IN (:user_ids)
) AND LENGTH(cf.value) = "Woman"
Thanks a lot!