1 of 1 people found this helpful
Yes, what you want to do is theoretically possible. You could set a Script Trigger (probably OnRecordCommit, since you're using two fields) to create a new record in your library table if the item isn't there already. However, doing so on an automatic basis is fraught with issues.
The first issue is simply the fact that people make mistakes on data entry. They mean to enter, "Bush's Baked Beans" and enter "Bushs Baked Beans" instead. The machine doesn't know they're supposed to be the same thing, and poof! You wind up with a functional duplicate in your library table. Bad.
The second issue is that the whole purpose of a library table is to control data entry, to restrict it to approved items. If users are permitted to enter whatever items they want, willy-nilly, then you might as well do away with it and just do a self-join to the items table.
Allow me to suggest a better approach. Rather than automatically adding the data to your library table, you could have the system query the user as to whether or not the missing item should be added. Then, it could be marked for addition by a data admin later, who would have the privileges to make the decision as to its propriety.
Also, rather than allowing free text entry, consider implementing a value list or a list-view-based selection method. This will tend to cut down on spelling / typo errors.
Thanks Mike! You are absolutely on target with your analysis. I will look at both suggestions of solutions. I am a little concerned with the list view-based selection as there will be hundreds, if not thousands of entries. I like that solution better if the entry of 'beans' brings up all the 'beans' entries for selection. I'll check it out. It seems to be the more elegant and simpler way to solve the problem.Also, would cut down on Admin time. thanks again for your time!
When dealing with the long lists, you can implement a filtering feature that performs a search based on the contents of a global field to thin the list. This will accommodate longer lists without so much scrolling.