You can use the Run SQL function to upgrade the library data you imported from the spreadsheet file. You can import your library data into the LIBRARY_NEW table.
For example, after importing, the author's names are listed in the form "firstname lastname". You can modify the format so the last name is listed first, followed by a comma and the first name. It will involve:
- trim data entries; remove possible leading and trailing spaces
- test and review a result
- execute the final SQL command that updates author entries
Library data was imported from the spreadsheet. The author's name is listed in the form "FirstName LastName".
We want to change the author from "FirstName LastName" to "LastName, FirstName".
One simple SQL command could modify the value of the author data field. The command uses the position of the last space (space character before the last name). If the entry has space characters at the end or front of the entry, it could generate errors. Instead, we can execute the TRIM command to remove the leading and trailing spaces.
UPDATE LIBRARY_NEW SET AUTHOR = TRIM(' ' FROM AUTHOR)
We are ready to change the format of the AUTHOR data field. However, before we do it, we can run a test and check the result. In the test, we take the value of the author data fields, process it, and save it in CUSTOM2.
UPDATE LIBRARY_NEW SET CUSTOM2 = SUBSTRING(AUTHOR from CHAR_LENGTH(AUTHOR)-(POSITION(' ' in REVERSE(AUTHOR))-2) for POSITION(' ' in REVERSE(AUTHOR))) || ', ' || SUBSTRING(AUTHOR from 1 for CHAR_LENGTH(AUTHOR)-POSITION(' ' in REVERSE(AUTHOR)))
Review the test results.
review result, AUTHOR:
Now, we can safely change the format of the entries in the AUTHOR data field.
UPDATE LIBRARY_NEW SET AUTHOR = SUBSTRING(AUTHOR from CHAR_LENGTH(AUTHOR)-(POSITION(' ' in REVERSE(AUTHOR))-2) for POSITION(' ' in REVERSE(AUTHOR))) || ', ' || SUBSTRING(AUTHOR from 1 for CHAR_LENGTH(AUTHOR)-POSITION(' ' in REVERSE(AUTHOR)))
You can change the value of the AUTHOR using CUSTOM2 from the test.
UPDATE LIBRARY_NEW SET AUTHOR = CUSTOM2
Use the CUSTOM VIEW feature: you can define a SELECT statement to review results before you execute the SQL command that modifies data values. The SELECT definitions create and display a temporary table.
Handy Library Manager 4.4, review new features
Labels Printing Software for Windows
Library Web Search, review new web search
Library Search 4.0, review new desktop search
Getting started library video lessons
How to include library name on labels
Library, Data Entry Form Designer
Edit item or borrower data in the check-in/check-out window?
Link item lost fine with the PRICE data field?
Handy v3.2 supports letter tabs?
How to predefine library email messages?
How to update library data from the web with ISBN?
How to predefine library email messages?
Compilation of questions and answers from library requests will help you to make purchase decision.
How to verify if the item is checked out in the library main catalog window?
How to create a new record using Copy Selected Item?
Special Library, how to manage digital and paper document library?
What is the maximum number of library items?
Library Software for Small Libraries, Windows application
Handy Library Manager
small library software
library tour
library features
try now, download
cloud
library software reviews
Copyright © 2024 · All Rights Reserved · PrimaSoft PC