Relationships and "Table Hopping"

[bfbechard]

I am a new FileMaker user - Version 12. I am creating a database to handle employee HR for a transportation company. The constant, or "key file" is k_EmpID. When an employee’s file is complete, it will have records in multiple tables (Personal (1 record); Payroll (multiple records); Drivers File (1 record); Verification File (multiple records) etc.).

When working with the files, it is very common to jump from one table to another. When I move from table to table, I would like the system to show records for the “same employee”, unless I ask for a different individual. However, as the tables and their relationships are set up now, there is no guarantee of landing on the same employees record when you move to a different table. I must have something messed up in my relationship setup.

Questions:

Is there a way to auto-create a record for all employees in ALL tables when a new employee ID is created?

Is there a way to ensure that when jumping from table to table you remain with the same employee's records?

Is there a way to avoid the system opening to an existing record?

[bcooney]

First off, congrats on being the first person (that I know of) that is running FM12! Lol, it's not released yet.

Anyway, I'll assuming that you are running FM11. The way to hop tables are stay within the "family"(parent to children and back) is to use FM's Go to Related Records script step. This step relies on a parent child relationship, such as Client > Invoice where Client::primaryKey = Invoice::ClientForeignKey.

So, if you provide more detail of your relationship structure, then we can help you hop about your relationship graph. Also, I wouldn't go the route of auto-creating records. It is better practice, in the majority of cases, to create records as you need them. That being said, are you familiar with portals and allowing the creation of child records in a portal on the parent's form view?

[Fenton]

First, if every employee gets a "Driver" record, then I see no real need for a Drivers table. But perhaps you have your reasons.

I don't see any reason to create any records in the other tables either. Things like Payroll happen at a certain time. You can create it when it's needed. Resist the temptation to create records ahead of time. Make it easy to create them at the time they're needed.

The system navigation can stay on one employee, if you navigate using the employee ID to his other entries, generally with a button, using the Go To Related Record step. It cannot however go anywhere if their other record does not yet exist, which makes general navigation to the other table difficult. Personally I say have 2 buttons, one to go to his records, another to just to the other table; clearly labeled and/or placed on the layout.

To avoid opening to an existing record, you'd need to open to NO record. Which you could do using script steps: Show All Records, Show Omitted Only. But you might rather have some kind of central navigation layout, which is not an employee-related table, and just go there. It could be a 1-record only table, a "menu". This is fairly common.