How does the short_str_val column of the propval table get populated?
Actually, it looks like this is the only trigger that's used in the database schema. Backup the database first and then run the following query while the Laserfiche Server service is stopped:
create trigger short_str_val_insert_update on propval after insert, update as set nocount on if update(str_val) update propval set short_str_val = case when len(i.str_val) <= 100 then i.str_val else null end from inserted i where propval.tocid = i.tocid and propval.prop_id = i.prop_id and propval.pos = i.pos
As for addressing the existing values that need a short_str_val, you can run
update propval set short_str_val = str_val where str_val is not null and len(str_val) <= 100
Then restart the Laserfiche Server service.
The propval table uses two columns for text type metadata fields. If the value saved in the field is 100 characters or less, then it will get written to both the str_val and short_str_val columns. Otherwise, if the field value is over 100 characters then it just gets written to the str_val column. This is for search performance reasons.
At some point, values have stopped getting written to the short_str_val column completely, regardless of character length. This has definitely impacted search results. Only rows with a short_str_val column value are returned. Is there some obscure administrative setting that could have been changed to cause this?
This isn't controlled by a setting.
The propval table should have a trigger named [short_str_val_insert_update] that checks after insert or update, if the length of the str_val value is <= 100, then set the value into short_str_val.
Can you confirm that trigger exists in the propval table in your repository database?
The trigger does not exist in the propval table.
None of the tables in the repository database have a trigger.
Did something happen to your original repository database where you needed to recover it or restore the data to a new database?
Otherwise, I can't explain why else you might not have any triggers on any tables at all. The integrity of the database may be suspect at this point.
Was there a specific event that occurred which could have affected your database as such? Can you check any database backups you may have to see if those contained the proper triggers?
In July 2014, we migrated from an offsite to onsite hosting solution. This is the same time frame in which values stopped getting written to the short_str_val column.
Actually, it looks like this is the only trigger that's used in the database schema. Backup the database first and then run the following query while the Laserfiche Server service is stopped:
create trigger short_str_val_insert_update on propval after insert, update as set nocount on if update(str_val) update propval set short_str_val = case when len(i.str_val) <= 100 then i.str_val else null end from inserted i where propval.tocid = i.tocid and propval.prop_id = i.prop_id and propval.pos = i.pos
As for addressing the existing values that need a short_str_val, you can run
update propval set short_str_val = str_val where str_val is not null and len(str_val) <= 100
Then restart the Laserfiche Server service.
Thank you, Alexander.