by gpe » Fri Jan 15, 2016 10:40 am
I will try to explain better. I have a lot of paper files for which I want to create a metadatabase. The general information of the file (file_nr, start_date, closed_date, shelf_location) are stored in table tbl_file. Each file has a number of documents of different types: letters, receipts, contracts etc for which I created the tables tbl_letter, tbl_receipt, tbl_contract. The documents have some general attributes in common, which go in table tbl_document: doc_nr, doc_date, doc_type, file_nr (which is a FK to the tbl_file) and doc_path, which stores the path to the scanned image of the document.
Now, I would like to create data entry forms, one form for each type of document. To combine the attributes from tbl_document and (let’s start with the receipts) tbl_receipt, I created a select query that includes all of the attributes of the two tables:
SELECT "tbl_document"."doc_nr", "tbl_document"."doc_date", "tbl_document"."doc_type", "tbl_document"."doc_path", "tbl_document"."file_nr", "tbl_receipt"."doc_nr", "tbl_receipt"."receipt_type", "tbl_receipt"."receipt_amount" FROM "filedb"."public"."tbl_receipt" AS "tbl_receipt", "filedb"."public"."tbl_document" AS "tbl_document" WHERE "tbl_receipt"."doc_nr" = "tbl_document"."doc_nr" AND "tbl_document"."doc_type" = 1
When I run the query, it is not updateable, I cannot add records. Changing the relationship to a left or right join will make it updateable:
SELECT "tbl_document"."doc_nr", "tbl_document"."doc_date", "tbl_document"."doc_type", "tbl_document"."doc_path", "tbl_document"."file_nr", "tbl_receipt"."doc_nr", "tbl_receipt"."receipt_type", "tbl_receipt"."receipt_amount" FROM { OJ "filedb"."public"."tbl_receipt" AS "tbl_receipt" RIGHT OUTER JOIN "filedb"."public"."tbl_document" AS "tbl_document" ON "tbl_receipt"."doc_nr" = "tbl_document"."doc_nr" } WHERE "tbl_document"."doc_type" = 1
Now, when I add record to the query resultset, filling all the attributes and making sure that tbl_document.doc_nr = tbl_receipt.doc_nr, and then save the record, Base crashes. After recovering the odb file and opening the query, I can see that the records were added correctly in both tables.
Any ideas what I can do to avoid Base from crashing with every added record?
Open Office 4.1.2, Windows 8.1, using PostgreSQL 9.4 as backend