Option Group vs Custom field

Question

New install (civicrm 5.35.1) and I can't find this answer.

We have 22,000 alumni that I've imported. One of the fields is cast year there are about 120 of these so I want to make an option group (it would be taxonomy in other systems I guess). There are multiple uses of this in a contact.

For example, everyone of our alumni has a primary cast. They could also have multiple "secondary" casts where they were staff or several other participants.

I'm trying to map these to a contact through a custom field.

Data sample:

1965A
1965B
1988E

etc.

Question 1: I can import the data into the database directly if needed, but I'm not sure of all the fields that are listed in the option group table.

Question 2: Assuming I get these entered as an option group, how can I create a custom field that will use this for lookup? And secondarily, how to choose which cast type it is (i.e. primary, secondary, etc.)

In answer to the 2nd part of question 2, I know I can also create an option group of cast_type, but again, I'm not finding an easy way to have this show and also allow me to import it. It would be similar to phone or address I imagine.

Thanks and please let me know if you have an questions.

Answer 1

First create a custom field of type select or multi-select with empty options. From civicrm_custom_field table grab the option_group_id for the field.

You can use Option value importer extension to import all the options using csv or have a insert query to insert records in civicrm_option_value table using option_group_id from civicrm_custom_field.option_group_id