Auto-Populating with Data from Another Table

[BrotherCRO]

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.

[Arineckaig]

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.

[DACM]

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:

Code: Select all   Expand viewCollapse view

ALTER TABLE "SIGN_IN" ALTER COLUMN "DATE_TIME" SET DEFAULT CURRENT_TIMESTAMP



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:

[fuzzyluzzi]

Is it possible to make this have cascading list boxes. First to check for Dept, then Employees in that dept before clicking Sign In?

[Arineckaig]

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

[Villeroy]

[Solved] Create a Form with the List Box and Subcategories

[tveinot]

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.

[Villeroy]

You can add as many sub, subsub, subsubsub...forms as needed in order to display and/or edit all interrelated information from all forms.

[tveinot]

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?

[Villeroy]

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.

[tveinot]

I will give it a try.
Thanks