See spreadsheets “DataDefsMstr” and “DataDefsFieldSort” on Database Design Spreadsheets.
Discussions & comments from Wikispaces site
Adding To/Updating File
08 January 2010 21:35:48
A few of us so far have been trying to add the Notes, etc details to the various table fields in the file referenced in this page.
Much of being able to accurately describe the fields depends upon breadth of data in one’s RM4 database, as well as of features used within the software. The more and varied the data, the more likely that an accurate and complete portrayal of the database fields and values can be made. Sometimes just an extra pair of eyes can make a difference in seeing a particular aspect, as well.
Ultimately, the more people that are able to help try to validate the Notes added and to find any discrepancies or shortcomings in them, the better the contents of the file will become.
By the way, we chose the ODS format since it’s used by the spreadsheet software package contained within the OpenOffice suite and because a number of people are likely not to own Excel.
OO is similar in scope to Microsoft Office, but is freeware. It can be downloaded at http://www.openoffice.org.
I own Excel, but we found that opening an OO ODS file in Excel and saving in ODS format within Excel resulted in some issues/discrepancies between the products. As a result, I went ahead and downloaded OO in order to also be able to work in the Calc environment so that we’d all be able to contribute to saving a file without any worry of repurcussions.
Thanks very much for any help that anyone can provide, whether taking on defining of the Notes for fields within a table, making sure that all values for a field are represented and portrayed accurately, etc. Anyone using LDS support and/or nFS (particularly) within RM4 may be of real help.
25 January 2010 14:02:39
Well, our first pass through the fields in the RM4 database tables has been completed, so the hardest part and bulk of the work are essentially done. We managed to do so all during RM 22.214.171.124, which was fortunate since the database layout or behavior of fields, etc. may’ve potentially changed between releases.
I’ve just sent comments about observations made on some fields in tables that I didn’t submit within the ODS file, so corrections to some fields in those tables could be forthcoming. After that time, we’d all certainly appreciate others looking at the data in their various RM4 database tables/fields for variations. Any feedback relating to corrections to the field descriptions in the DataDefsMstr sheet of the ODS file would be most welcome.
Thanks very much for any contributions that you’re able to make!
ResearchTable: Correction of Status def
18 January 2010 16:15:04
I found the workbook really helpful in doing the To_Do query. Found and corrected errors in the Status def.
232 ResearchTable Status I 0-3 Status (Status, from Edit ToDo item screen (TaskType = 0): 0 = Open, 1 = Completed, 2 = Pending, 3 = Problem; Sent or Received, from Edit Correspondence screen (TaskType = 1): 0 = Sent, 1 = Received)
18 January 2010 16:24:59
Oops. I posted this without realising that Romer had uploaded a revised workbook so my corrections have yet to be incorporated. Great work, Romer!
CitationTable.Quality misrepresented on web page
18 January 2010 17:23:08
Three tildes in a row is used as an example of Quality (Don’t Know x3). Wikitext detects two tildes as a code for the user who last edited the page, e.g. – ve3meo , thus misrepresenting the value. Weird!
18 January 2010 19:36:22
Surrounding with backtick characters should do the trick:
– ve3meo or `– ve3meo`
– ve3meo Jan 18, 2010 oe `– ve3meo Jan 18, 2010`
– ve3meo or `~`~~ or `~~~`
But then it will look funny in the spreadsheet.
CitationTable.OwnerType = 7 ???
29 January 2010 14:21:47
I’ve encountered a few records in the CitationTable where the OwnerType=7. Some of them correspond to odd results in RM4’s Reports > Lists > Source List report:
1. The first (and sometimes only) listed citation for the source is the source itself.
2. In one case, a citation with a unique detail that shows in the Edit Person screen of a woman’s Alternate Name fact is reported against her husband’s Birth fact. The same source is cited with different detail for his Birth fact and is correctly reported.
3. There remain some others with the value 7 for which no anomalous behaviour is readliy detected in the Source List report.
The CitationTable.OwnerID value for these does not make any sense. They do not point to any relevant records in the same or any other table.
30 January 2010 06:13:39
Very strange! I couldn’t locate any in my database, but the only thing that I might suggest would be to go ahead and try the following query and see if any patterns emerge (or at least any thoughts as to how the sources themselves might’ve been used):
LEFT OUTER JOIN SourceTable ON
CitationTable.SourceID = SourceTable.SourceID
WHERE CitationTable.OwnerType = 7
DataDefs for DUP database added.
21 February 2010 04:16:28
Spreadsheet revised with a sheet added for DUP database, the .DUP file created by the Duplicate Search Merge tool. maybe it should be integrated with the Master DataDefs sheet because it does function as a temporary table in relation with the permanent tables.
01 July 2011 15:35:28
PersonTable.UniqueID appears as hex in both SQLite and MS Access. The usual tricks to coerce it into appearing as text don’t seem to work – CAST for SQLite and StrConv – because the functions leave the UniqueID field in hex. Does anybody know how to coerce UniqueID into text?
02 July 2011 03:52:16
UniqueID is a hex coded string, viz.the table def:
CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, …
I’m not sure what the problem is. Concatenating it with text results in text:
SELECT ‘UID=’||UniqueID FROM PersonTable;