Auto-Populating with Data from Another Table

Discuss the database features

Auto-Populating with Data from Another Table

Postby BrotherCRO » Fri Aug 30, 2013 1:56 am

Greetings,

Here's the situation: I'm trying to create an employee sign-in form in OO Base that records the Employee ID (primary key for the table), last name, first name, department/position and an automatic timestamp. The thing is, I only want to have to input the employee ID with entry, and have the rest of the information auto-populate based on the data from a table containing the employees' information (which also uses the employee ID as its primary key). How do I configure the sign-in table to extract information from the employee data table based on the employee ID being entered? I'm still learning the basics of OO Base, so any input (the more idiot-proof, the better) is appreciated.
OpenOffice v. 4.0 on Win7
BrotherCRO
 
Posts: 2
Joined: Fri Aug 30, 2013 1:48 am

Re: Auto-Populating with Data from Another Table

Postby Arineckaig » Fri Aug 30, 2013 2:17 pm

How do I configure the sign-in table to extract information from the employee data table based on the employee ID being entered?

Welcome to the forum.

If I am correct in assuming you wish a table to record each of several times that an employee may sign in, you will in effect need a one-to-many relationship between the employee table and a sign-in table. The latter table (the many side of the relationship) should have its own (possibly token) primary key field but also it should have a foreign key field that holds the relevant "Employee ID" value for referring or linking to the primary key field in the employee table (the one side of the relationship). Of course the sign-in table should also have a field to hold the value of the time-stamp each time the employee signs in: that field should not be in the employee table unless the database is to be designed to record the one and only time that an employee signs in.

AOO Base provides its form/sub-form facility for effective handling of such relationships. In the form document the main data form should be based on the one side or employee table and the sub-form on the many side or sign-in table. In the sub-form's list of properties under the data tab the linking fields for both forms should be their respective "Employee ID" fields. When correctly set-up selection of any particular employee in the main form will display whatever fields are required from the employee table, but it will also filter the records in the sub-form to show only those from the sign-in table that relate to that particular employee. The records in the sub-form can be edited, but more importantly the sub-form permits entry of new records with the relevant Employee ID field value pre-entered by default.

Please come back with questions if this explanation merely confuses or if you would like a simple demo file to see how it works.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Auto-Populating with Data from Another Table

Postby DACM » Fri Aug 30, 2013 5:17 pm

 Edit: Just noticed Arineckaig's reply but I had already compiled this reply so it may or may not reflect that excellent advice... 


Simply use a List Box to copy the Employee_ID to a separate SIGN_IN table. You can always use Queries or Forms to reassemble the table-data for viewing, filtering or analysis purposes.

The SIGN_IN table will also need a TIMESTAMP field with the default set to CURRENT_TIMESTAMP using SQL:

The user selects an employee by name, and then presses a button to save the Employee_ID to the SIGN_IN table with automatic TIMESTAMP. You can optionally add a SubForm to display the Employee information upon saving the SIGN_IN record.

See Students5 - attendance tracking.odb for more detailed information.

Here's a quick demo:
Attachments
Sign_In.odb
Demo List Box record creation with TIMESTAMP
(39.58 KiB) Downloaded 380 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Auto-Populating with Data from Another Table

Postby fuzzyluzzi » Thu Oct 30, 2014 9:54 am

Is it possible to make this have cascading list boxes. First to check for Dept, then Employees in that dept before clicking Sign In?
OpenOffice 4.0.1 on Windows 7
fuzzyluzzi
 
Posts: 22
Joined: Wed Feb 26, 2014 12:37 pm

Re: Auto-Populating with Data from Another Table

Postby Arineckaig » Thu Oct 30, 2014 11:27 am

First to check for Dept, then Employees in that dept before clicking Sign In?

There is probably a one-to-many relationship between Dept and Employees. Thus it can again be handled in Base by using a data form for the one side (Dept) and a linked sub-form for the many side (Employees). Base will permit the inclusion of several generations of sub-forms in a single form document. Thus the sub-sub-form (sourced from the sign-in table) can still reflect whichever Dept and Employees records have been selected in the grandparent and parent data forms.

Cascading list boxes can equally be used if the perceived advantage justifies their more complex set-up without resort to macro programming. See this Example for a full and good description:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=42845
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Auto-Populating with Data from Another Table

Postby Villeroy » Thu Oct 30, 2014 1:17 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Postby tveinot » Thu Mar 09, 2017 8:30 pm

Is there a way to make the related table info come up in the form before saving the record? I have 2 tables valve_exercise and valve_master_list. The form is for recording when a valve is exercised. So each exercise is related to the valve via the valve_no field. But when we go to a valve and type in it's number we would like to see the fields from the valve_master_list because that table stores the valve operation information.
OpenOffice 3.1 on Windows Vista
tveinot
 
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm

Re: Auto-Populating with Data from Another Table

Postby Villeroy » Thu Mar 09, 2017 10:43 pm

You can add as many sub, subsub, subsubsub...forms as needed in order to display and/or edit all interrelated information from all forms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Postby tveinot » Thu Mar 09, 2017 11:03 pm

I understand that but until I "save" the entry it doesn't show me the related data. I would like to be able to enter the valve number then tab to the next field, on the "tab" have the information from the master_valve_list populate into the subform so that I know which way to turn the valve, if it is open or closed, how many turns, and when it was last operated, etc... Do I have to save the entry with just the valve number first or is there away to show the related data on insert?
OpenOffice 3.1 on Windows Vista
tveinot
 
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm

Re: Auto-Populating with Data from Another Table

Postby Villeroy » Sat Mar 11, 2017 2:35 am

Add a small main form (in the forms navigator directly under "Forms") with property "add new record only" where you can enter a new valve without moving away from the the other form's record. Then go back to that form, refresh the list box of valves and pick the new one.

https://forum.openoffice.org/en/forum/d ... hp?id=2879 has a form with movies and genres where you can add a missing genre before assigning it to movies.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Postby tveinot » Sat Mar 11, 2017 6:31 pm

I will give it a try.
Thanks
OpenOffice 3.1 on Windows Vista
tveinot
 
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests