"I would like to bring up a list of my check-outs by Borrower Type. In essence, I am trying to see what my patrons (who are labelled as Child under Type in their records) have checked out, their overdue books. I do not want to look at every type of patron's loans (the borrowers with a Type of Adult)."
Solution 1: In the loan.map file define mapping for EXTRA1 and/or EXTRA2 data fields. If you define EXTRA1=TYPE it means that the borrower type value will be stored in the loan's EXTRA1 data field. With this data saved you can sort, group, search by borrower type in the loan transaction table. You can find loan.map file tin eh c:/handy_lib/data/db folder.
Solution 2: see our help topic on how to create a query in the custom views window.
SELECT B.NAME, B.TYPE, C.TITLE, A.RETURNDATE, A.DUEDATE, A.LOANDATE FROM LOAN A JOIN BORROWERS B ON A.BOR_IDNO=B.BOR_IDNO JOIN LIBRARY C ON A.ITEM_IDNO=C.IDNO WHERE B.TYPE='Child' AND A.RETURNDATE IS NULL ORDER BY B.NAME
Tables and fields used in the query:
A - Loans table (LOAN), data fields: RETURNDATE, DUEDATE, LOANDATE, ITEM_IDNO
B - Borrowers table (BORROWERS), data fields: NAME, TYPE, BOR_IDNO
C - Libary Items table (LIBRARY), data fields: TITLE, IDNO
This part defines data displayed:
SELECT B.NAME, B.TYPE, C.TITLE, A.RETURNDATE, A.DUEDATE, , A.LOANDATE
This part defines what tables are joined and by which data field:
FROM LOAN A JOIN BORROWERS B ON A.BOR_IDNO=B.BOR_IDNO JOIN LIBRARY C ON A.ITEM_IDNO=C.IDNO
This part defines conditions for results to be displayed:
WHERE B.TYPE='Child' AND A.RETURNDATE IS NULL
Copyright © 2019 · All Rights Reserved · PrimaSoft PC