i have a multivalue custom field that keeps track of the Year and Class of students. This is a multivalue field as we use it to track the student's class every year. I realise that there are extraneous values that are added for year 2020 and i will like to erase all multivalue field that has Year=2020. The other records in the mutlivalue field should not be affected.
So as an example: Student A has in his multivalue field 3 records: Year 2019 Class B, Year 2020 Class C, Year 2021 Class D. I like to erase the Year 2020 Class C record, and leaving 2 other records in the multivalue field.
I checked that there doesn't seem to be an API or the mass update profile to do this so i was looking through the database schema to find a way to batch erase. it seems like there is a separate table just to keep track of the records for this multivalue field and the schema is just the ID, the contactID, and the values in the field. when i delete a record in the multivalue field, the corresponding row in the table will disappear and the reverse happens when I add a record. It looks promising that if I filter for all records that has 2020 in the field and delete them in the database, that is equivalent to batch erasing all the relevant records in the multivalue field.
So i like to seek confirmation that this will work well and it doesn't seem like there are complications from directly deleting the entries in the table dedicated to the multivalue custom field, the table is prefixed with civicrm_value_. Thanks for your advice!
