[Solved] extract next record from query to populate spre

Discuss the spreadsheet application

[Solved] extract next record from query to populate spre

Postby PurplePix » Sun Dec 15, 2019 6:00 am

[SOLVED] I have a working Family tree in Calc, with two data entry sheets (Families and People). I have a database that can extract the data I need to populate through a query, but need help to extract the PersonID column into individual form fields on the spread sheet.

The screen shot shows in column F of the spreadsheet two ID's and Child name. In this example I need to put 11 (PersonID) and Sarah (Child Name) in the field at F10 any suggestions?

q_Families
SELECT "FamilyID" AS "FamilyID", "FathersID" AS "FathersID", "MothersID" AS "MothersID" FROM "tbl_Families"

q_tbl_Person_Photo
SELECT "tbl_Families"."FamilyID" AS "FamilyID", "tbl_Families"."FathersID" AS "FathersID", "tbl_Families"."MothersID" AS "MothersID", "tbl_Person_Photo"."PersonID" AS "PersonID", "C"."Given" AS "Child Name", "tbl_Person_Photo"."Family1" AS "Family1", "tbl_Person_Photo"."Family2" AS "Family2", "tbl_Person_Photo"."Family3" AS "Family3", "tbl_Person_Photo"."Family4" AS "Family4" FROM "FILTER_Family", "tbl_Families", "tbl_Person_Photo" AS "X", "tbl_Person_Photo" AS "Y", "tbl_Person_Photo", "tbl_Person_Photo" AS "C" WHERE "FILTER_Family"."F_FamilyID" = "tbl_Families"."FamilyID" AND "tbl_Families"."FathersID" = "X"."PersonID" AND "tbl_Families"."MothersID" = "Y"."PersonID" AND "tbl_Person_Photo"."Child_of_Family" = "FILTER_Family"."F_FamilyID" AND "C"."PersonID" = "tbl_Person_Photo"."PersonID"
Screen shot-r.jpg
Screen shot showing situation
Last edited by robleyd on Sat Dec 21, 2019 3:19 am, edited 2 times in total.
Reason: Tag as Solved
Open Office 4.1.2 Windows 7
PurplePix
 
Posts: 19
Joined: Sun Jan 17, 2016 1:14 am

Re: How to extract next child record from query to populate

Postby RusselB » Sun Dec 15, 2019 7:53 am

I'm thinking that you're going about this wrong using a spreadsheet to interface with your database.
Your screenshot is useless to me, as it opens up too small for me to read, and even if I could, there's so much information that is missing from a screenshot, it's ridiculous.
Whether you stick with using a spreadsheet or convert to strictly the database (which would be my recommendation), in order to help you, I'm thinking we would need to see your actual database and/or spreadsheets in order to analyze the structure.
Without the .odb and/or the .ods files, we're making a lot of guesses...which is not good form.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to extract next child record from query to populate

Postby PurplePix » Sun Dec 15, 2019 8:51 pm

Thank you for your reply (I'm thinking that you're going about this wrong using a spreadsheet to interface with your database.) You might be right but the project started on a spreadsheet, because the learning curve was steeper trying to learn base than applying the knowledge I had with spread sheets. Still I struggle to understand base even after months of searching to go one step forward - may be it is a case of just not knowing the right questions to ask.

The spreadsheet works by entering a family number on the sheet in the box provided, and pressing return (Enter). The sheet like a printable report is populated with data from the families and people sheets and it works as expected filling all the family data and up to 16 children. With that all working I thought it would be nice to add photos of family members. and the Father and Mother were no problem as I cobbled together a Database that fill the fields on the spreadsheet after selecting a family number from the drop down box when the sheet was refreshed.

The next thought was to also put pictures of the children on the page, so I struggled through SQL and the unclear direction to build a query that would search the database for children of the family. With the database registered, and F3 to bring it up above the spreadsheet, the Father and Mother show up in the 9 records of Family 4, The PersonID is the childs ID number in the People sheet, along with their name ( Child Name) in the People Sheet. The Family1 to Family5 is a reference to their family number(s) which their children will be linked to.

The help I need is to be able to assign their PersonID and Child Name to the Form Fields shown in Column F. If I was some how able to assign a unique identifier (or call them by their record - 1-9 in this case) or even a sub form in Form Navigator. Like I said maybe I don't know the right question to ask, or have found the right piece of information. I am stuck and if I were in base I don't think I would be close to where I am. I look forward to any help you can provide.

Forum_Family_SS4.ods
(98.89 KiB) Downloaded 8 times

Family test Database - Forum.odb
(14.49 KiB) Downloaded 7 times
Open Office 4.1.2 Windows 7
PurplePix
 
Posts: 19
Joined: Sun Jan 17, 2016 1:14 am

Re: How to extract next child record from query to populate

Postby UnklDonald418 » Mon Dec 16, 2019 10:33 pm

A genealogy database probably isn't the best place to begin learning database design. I downloaded your database file and it looks like you have attempted a relational database that appears to be mostly correct..
Your tables had all text fields outside the Primary Key values so I corrected some formatting issues.
You had fields for Family1-Family5 which indicates a many to many relationship so I moved those into an intersection table. It appears you were trying to attach photos to personal records so I added another table for that.
I added some forms with list boxes that allow you to edit and enter data in a more user friendly manner.
I noticed you had a Filter form so I added a form that uses that.
See if this helps you make some progress. If there is anything you don't understand be sure to ask.
Attachments
FamilyTestDatabase-Forum01.odb
(69.4 KiB) Downloaded 8 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How to extract next child record from query to populate

Postby PurplePix » Tue Dec 17, 2019 1:03 am

Thank you for your suggestion and file.

You had fields for Family1-Family5 which indicates a many to many relationship so I moved those into an intersection table. It appears you were trying to attach photos to personal records - these were for multi marriages associated to that PersonID and indicated the FamilyID.

The Child_of_Family is the colum where the (Name/Photo) is taken from. When the number is 2 that PersonID is a Child_of_Family 2 or the number 4 that PersonID is a Child_of_Family 4, a family could have up to 16 children or none at all.

Let's look at this differently, If I had started with a data base I would have made two tables - tbl_Families (to identify the parents) and tbl_Person_Photo (to have all the information for everybody) in that table is a Child_of_Family field. Using the q_tbl_Person_Photo provides all the information I need to populate the fields in my Calc Sheet. The problem is if there are 4 children the query produces the information in 4 rows with both parents and one child in each row. My solution would be to have the query output in one row (but have not been able to get there) the other possibility would be able to extract/identify each row from the query so I could assign it to the field on the cal sheet. I haven't been able to find a way to do that either. So SQL in the query or something like that is all I need. It is difficult to find good documentation to help with the learning process.

It might be as simple as a temporary or another table in the database. I am going to spend some time looking at your files, but at the moments they seem to be taking me away from a solution.
Open Office 4.1.2 Windows 7
PurplePix
 
Posts: 19
Joined: Sun Jan 17, 2016 1:14 am

Re: How to extract next child record from query to populate

Postby UnklDonald418 » Wed Dec 18, 2019 12:23 am

If your problem is turning spreadsheet rows into columns have you tried the Calc menu selection Data->Pivot Table?
Chapter 8 of the Calc User Guide explains how to use it.
https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/OOo3.3_User_Guide_Chapters
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How to extract next child record from query to populate

Postby PurplePix » Wed Dec 18, 2019 6:58 pm

UnklDonald418 thank you very much for taking a positive interest in my request, although I haven't got to a solution yet. You have made me reconsider everything and think in new creative ways. I looked at your files, and did more extensive searching and reading. Some of what you posted will be valuable moving forward.

Lets go backward for a moment, this project was started in 2016, the use of a spreadsheet was because it started as data collection, and that was easy to enter into the cells of the spread sheet. The first upgrade was to split the people and families from one sheet to two. With the growing amount of data, the efficient extraction of data to print a report seemed important. The spreadsheet allows for data on one sheet to be displayed on another sheet through the use of formulas sometimes extensive. This resulted in a Display sheet and has been followed by others much like you make reports from a Database. Spreadsheets do this well but the next step deciding to add pictures is not done well by a spreadsheet, so I struggled with a database (You pointed out I had formatting issues) with the biggest challenge being good documentation for a beginner. Even on the forum snippets of code and cryptic responses, assume a level of understanding, that comes over time as the pieces fall into place like a large jigsaw puzzle with no picture for reference.

Having persevered I have made it this far. Looking at the spreadsheet display page and entering a family number in the box and pressing enter populates the cells of that page. With over 600 people and 200 families everything works as expected and is easy to maintain. Now the database connection upgrade to place pictures on the display spreadsheet.

Using the drop down list and also putting the same number as the family number box, and pressing the Cyan bar will refresh the page. That sends the number to the database filter table, the query takes information from the queried table, and populates the 6 3D boxes below the Cyan bar in column F of the spreadsheet. Right now I am just working with text as I all ready have the Father and Mother pictures working and the process should be the same. The 6 boxes are looked at by clicking the 'Design Mode on/off'. Clicking the form navigator and looking at the FamilyID form shows the boxes and a refresh button.

Lets add another set of boxes (once I get this working I will need enough for 2 parents and 16 children). Click the Form Navigator FamilyID at the top and open the add fields. Double click the field you want (I expected them to go into the FamilyID form) It gave me q_tbl_Person_Photo 2 and I drug it up into FamilyID as a sub folder and positioned it below the other boxes for reference. I noticed the form Properties Data has two link fields ( will need to find if I can use that to solve the problem). Turning Design Mode off, the 6 person numbers boxes have the same number 8 and Ann, the new box follows that same update when the Cyan button is pressed. The query extracts 4 lines for four records 9 lines for nine records.

The problem is to be able to display data from each of these query lines into the appropriate field boxes on spreadsheet, this is where I am stuck.
Attachments
Form Properties-r.jpg
Form Properties
Form Navigator-r.jpg
Form Navigator
Open Office 4.1.2 Windows 7
PurplePix
 
Posts: 19
Joined: Sun Jan 17, 2016 1:14 am

Re: How to extract next child record from query to populate

Postby PurplePix » Sat Dec 21, 2019 2:38 am

After weeks of hitting an SQL programming wall, I now have a working calc spreadsheet that will both display all the family information with the single entry of a family number. Using a drop down box, selecting the family number then refreshing the sheet will then display up to 19 images (that meets my needs) of the family members to match them. There is also an option to show no images as well, in the coming days I will put the finishing touches on my family tree project.

I came to the forum looking for constructive help, I would like to thank F3K Total (Rik) and UnklDonald418 for their positive approach and providing me either a small idea or pieces to challenge my thinking process. A couple of days ago I thought, like I had been told it was impossible, sometimes all we need is a little direction and lots of personal hard work to find the impossible solution.

I am going to mark this solved as I have arrived at a solution for my request.
Open Office 4.1.2 Windows 7
PurplePix
 
Posts: 19
Joined: Sun Jan 17, 2016 1:14 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 4 guests