Adding Virtual Columns to an RM4 Table #msaccess #todolist #colorcoding

(This is for the MS Access section of the Wiki. I thought I was putting it there, but it didn’t seem to go there. I guess I’m still figuring out how to work the Wiki. Jerry)

I’m about to describe what I think is a useful approach to some reporting problems that are of interest to me. But I’ll say up front that I’m probably going to abandon this approach because I think I have a better idea moving forward. But in the meantime, I’ll report an what I’ve done so far. Well, I’m going to abandon this useful approach at least in part, but there is at least one case where I will probably continue with this approach.

There are some cases where I want to report on some additional information that is not presently in any of the RM4 tables. So the basic idea is to create a “real” table in MS Access and to think of the “real” MS Access table as if it were a “virtual” SQLite table in the RM4 database. No changes will be made on the SQLite side of the house. I place the additional data on which I want to report in the MS Access table, JOIN the MS Access table with an appropriate SQLite table in the RM4 database, and I can use MS access to report on the JOIN’ed data just fine.

Of course, from the point of view of MS Access, the roles of “real” and “virtual” are just the reverse – the MS Access table is “real” and RM4 SQLite table is “virtual”. And from the point of view of MS Access, you can JOIN a “real” MS Access table with a “virtual” SQLite table in the RM4 database just fine. The idea is to create an MS Access table to contain the additional data, and to be sure that the MS Access table contains a column that can be JOIN’ed with an appropriate RM4 SQLite table.

The first example is an MS Access table that includes a column that has the names of the RM4 colors. I want to be able to display the string “Green” rather than the number 9, for example. And I want to be able sort and filter on the name of the color rather than the number code for the color. I’m sure there is a simple and straightforward SQL way to achieve the required mapping between numbers and colors without having to make a new table, but making a new table is what I know how to do and it is very much in the spirit of how relational databases are usually designed.

To that end, I created the following MyColorTable and linked it to the RM4 PersonTable, with the required JOIN being between PersonTable.Color and MyColorTable.ColorID.

MyColorTable.jpg MyColorTableJOIN.jpg

The MyColorTable above is the example I plan to continue using moving forward. The following idea is the one for which I think I have a better approach moving forward. For some number of individuals – 17 individuals or 32 individuals or 103 individuals or whatever – I may have five “to do” tasks that I wish to accomplish for each individual. I wish to create a grid with 17 or 32 or 103 or whatever rows, and with five columns. I want to treat the grid as a checklist where I simply check off the appropriate cell for each individual and for each “to do” task when that task is completed for that individual. In my experience, traditional “to do” management software doesn’t work in this very simple and user friendly fashion, including “to do” management that’s available in RM4 itself and “to do” management available in GenSmarts.

So the obvious solution is to create a ToDoTable in MS Access with one column containing the RM4 Record Number for the individuals of interest and the other columns corresponding to the “to do” tasks to be accomplished. The only data that each cell in the “to do” columns has to contain is a 0 or a 1, or a blank or a 1. The 1 is my check mark denoting that the task is complete

This being a relational database application, I went at the problem slightly differently than having a row in my ToDo table for each individual of interest. I don’t really want to constantly be mucking around with entering the RM4 Record Numbers of the 17 or 32 or 103 individuals that are currently of interest. Rather, I want to automate that process. So I really created my ToDo table in MS Access with a row for every individual in my RM4 database. And to allow for future growth of my RM4 database without having to muck around with my ToDo table, I really, really created my ToDo table in MS Access with many more rows rows than there are currently people in my RM4 database. My RM4 database currently has about 58,000 individuals. So I created my ToDo table in MS Access with 100,000 rows. Row 1 of the RecNo column in the ToDo table contains 1, row 2 of the RecNo column contains 2, and so forth through row 100,000 of the RecNo column contains 100,000. The required JOIN is between ToDo.RecNo and PersonTable.PersonID. Some of the rows will never be used, but that’s fine. At the present time, I only have a column for one of my five “to do” tasks. I’ll add the other four “to do” tasks later.

MyColorTableJOIN2.jpg

Finally, I linked PersonTable to NameTable so I could display useful information about the individuals, and I filtered by color. So the standard RM4 color coding could be used to select individuals rather than having to muck around with lists of RM4 Record Numbers in my ToDo table, and the final result is as follows.

MyColorTableRPT1.jpg

I’m using “green people” as my filter, individuals color coded as green in RM4. The PrgraphOk task being complete means that for these particular individuals, I have painstakingly edited all fact notes so that paragraphing and white space looks exactly the way I wish for it to look in a narrative report. So in theory, I never have to perform this task again for those individuals. When I first created this MS Access query, the PrgraphOK column was completely blank. I manually replaced each blank with a 1 as I completed my editing and review of the way those individuals appeared in a narrative report. If I continue in this vein, I will gradually color code more people as green in RM4 to create my expanded “to do” list, and if I wish I can filter my MS Access query not to display the rows where PrgraphOk has already been set to 1.

The only thing I don’t like about this approach is that the information in the PrgraphOk column really isn’t stored in RM4, and I might like it to be stored there. There are number of creative places in RM4 where I could store the fact that I have completed my paragraphing review. And having done so, my query could create the PrgraphOk column by testing the data in RM4 rather than by having the data stored in my own ToDo table separate from RM4. I would still have to store the “I completed my paragraphing review” data in RM4 manually when I completed my paragraphing review, but it would keep the data in RM4.

Jerry

Discussions & comments from Wikispaces site


ve3meo

MS Access Section

ve3meo
26 June 2011 00:48:40

There is no section other than what may be implied by navigation guidance. You could edit other MS Access pages to include a link to your page. You could also create a MS Access box in the side menu with links to MS Access pages.

Tom


ve3meo

ve3meo
26 June 2011 12:59:07

Also, tagging the page can assist others searching for relevant stuff.


ve3meo

Comment: the basic idea is to create a “real” table in MS Access‍

ve3meo
03 September 2018 19:06:08

ve3meo Jun 26, 2011

One can add user defined tables in the RootsMagic 4 SQLite3 database using a SQLite manager. So far, I have seen no action from RM4 updates that causes such tables to be modified or deleted. That’s not to say that it could not happen. The nice thing is that these user tables are backed up and restored through RM4’s own procedures and always stay with the database in question. The downside is that they are not directly usable with multiple RM4 databases. But one could create a SQLIte database with MyColorTable and other common tables and an SQLite query can use it with any RM4 database by ATTACH.

Tom
thejerrybryan Jun 26, 2011

Thanks for all the very useful input. I’ve ultimately decided not to add “virtual” tables to RM4 by creating real tables in Access and joining them to RM4. I’ve also ultimately decided not to add any “real” tables to the RM4 database to use in my queries.

It’s been a very useful exercise to learn how to do both, and if it was just me I would probably do one or the other or both. But I would like to think I’m going to be a part of a larger community of users writing SQL against the RM4 database. As such, I would like to be able to write queries that I can post on this Wiki for others to use, and I would like for such queries to “just work”. But they won’t “just work” if they depend on additional tables that nobody else but me has created.

Again, much thanks for all the help.

Jerry

Inline comments


ve3meo

Comment: One can add user defined tables in th…

ve3meo
26 June 2011 13:05:33

One can add user defined tables in the RootsMagic 4 SQLite3 database using a SQLite manager. So far, I have seen no action from RM4 updates that causes such tables to be modified or deleted. That’s not to say that it could not happen. The nice thing is that these user tables are backed up and restored through RM4’s own procedures and always stay with the database in question. The downside is that they are not directly usable with multiple RM4 databases. But one could create a SQLIte database with MyColorTable and other common tables and an SQLite query can use it with any RM4 database by ATTACH.

Tom


thejerrybryan

thejerrybryan
26 June 2011 16:46:42

Thanks for all the very useful input. I’ve ultimately decided not to add “virtual” tables to RM4 by creating real tables in Access and joining them to RM4. I’ve also ultimately decided not to add any “real” tables to the RM4 database to use in my queries.

It’s been a very useful exercise to learn how to do both, and if it was just me I would probably do one or the other or both. But I would like to think I’m going to be a part of a larger community of users writing SQL against the RM4 database. As such, I would like to be able to write queries that I can post on this Wiki for others to use, and I would like for such queries to “just work”. But they won’t “just work” if they depend on additional tables that nobody else but me has created.

Again, much thanks for all the help.

Jerry

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.