We have a database that contains records of every book that we record in audio (for reasons that are too complicated to go into we can't query the database directly and so have to simply copy and paste data from fields from it's front end) and some of this data we need to paste into the metadata of our recordings.
One such piece of data is the name of the actor that narrated the book but there are a couple of problems here. The name is stored in the database as surname, forename and I need to enter it as forename surname. Thanks to someone on here I've solved this problem with a piece of vbscript but some books are narrated by more than one actor and I need to copy both names inserting a comma and space between them.
This is my code so far:
This works perfectly for a single narrator however if there are more than one narrators in that field it only copies the first one.// Sets Narrator
//Meta Data is the window I'm pasting the data into
SetFocus>Meta Data
WaitWindowOpen>Meta Data
Press Down * 3
Press Enter
WaitWindowOpen>Edit meta "ncc:narrator"
Wait>1
//Recording Management System is our database of books
SetFocus>Recording Management System - [View/Amend Title]
WaitWindowOpen>Recording Management System - [View/Amend Title]
Wait>1
//Navigates to the field in the database containing the narrators names
Press Tab * 29
Wait>1
WaitClipBoard
Press Ctrl
Send>c
Wait>1
Release Ctrl
//Goto the Restructure subroutine
GoSub>Narrator_Name_Restructure
SetFocus>Edit meta "ncc:narrator"
WaitWindowOpen>Edit meta "ncc:narrator"
Wait>1
Press Ctrl
Send>v
Release Ctrl
Wait>1
Press Enter
WaitWindowClosed>Edit meta "ncc:narrator"
Wait>2
Let>MSG_STAYONTOP=1
Let>MSG_CENTERED=1
MessageModal>Complete! %CRLF% %CRLF%Please verify that the Meta Data is correct before you build the book.
SRT>Narrator_Name_Restructure
VBSTART
VBEND
//example here:
//Let>name=COOPER, Mandy
//Get the copied name from the clipboard
GetClipBoard>name
Let>comma=,
Separate>name,comma,parts
Let>firstname={lower(%parts_2%)}
Let>surname={lower(%parts_1%)}
VBEval>Trim("%firstname%"),firstname
VBEval>Trim("%surname%"),surname
Let>firstname={upper(copy(%firstname%,1,1)) + copy(%firstname%,2,length(%firstname%))}
Let>surname={upper(copy(%surname%,1,1)) + copy(%surname%,2,length(%surname%))}
Let>fullname=%firstname% %surname%
//MessageModal>fullname
//Put the restructured name back onto the clipboard for pasting
PutClipBoard>fullname
END>Narrator_Name_Restructure
In the narrators field in Recording Management System (RMS) if more than one narrator read a book their names are listed under each other and can be selected by pressing the down arrow. As the number of narrators will vary from book to book the only way I could think of doing it would be to copy the first narrator as per the script above, then perform a down arrow to move to the next narrator on the list if there is one and copy again. Then it should compare the new name with the old one and if they are the same it should stop, if they are different it should switch back to Meta Data, enter a comma and a space and then paste in the new name before switching back to RMS and checking if there is a 3rd name etc etc. Trouble is, I can't figure out how to write it. I know I need to store the name in GetClipBoard>name but don't know to stop that from being overwritten when it gets the 2nd name nor if there is a second name to compare the 3rd name to that and not back to the first name again. I'm also not too sure on the best way of getting it to run this function x times as it's not really a loop because the number will be different for every book so it seems that it needs to know how many names there are before it does anything else
Basically I've confused the hell out of myself, anyone know how to achieve this?
