Complicated Relationship 
Author Message
 Complicated Relationship
I have a DB of Jobs and Time Entries

These are related by Job#

Time Entries contains a Date field as well as In & Out times
Each Job will have entries from a number of different people (employees)

I want a portal in another DB to display all the Jobs that a particular
person (employee) has time logged in.

Any ideas?

Thanks,

Trygve



Sat, 22 Nov 2003 00:48:53 GMT
 Complicated Relationship

Here's one way: you need an employee DB with one record per employee and an
employee id#. When an employee creates a record in the time DB that record
should have a field for the employee id, so that it has the employee id of
the employee who logs the time and the job id of the job for which the time
was logged.
Now you can use a multi line key (when a field used in a relationship has
multiple values separated by a charge return, it will relate any records
who's mach field's values mach to any of those values). In the employee DB
create a relationship that relates the employee id to the employee id in the
time log DB, then define a value list based on the job id field of the time
log DB as related by this relation ship. The result will be a value list
consisting of each unique job id on any record in the time log DB that is
related to the current record in the employ DB. Now create an unstored
calculation field in the employ DB = the design function ValueListItoms
(employee DB, job id value list); use this field as a multi line key for a
relationship to the job id field in the job DB. And use this relationship
for your portal.

--

C. Alex Lorda


Quote:
> I have a DB of Jobs and Time Entries

> These are related by Job#

> Time Entries contains a Date field as well as In & Out times
> Each Job will have entries from a number of different people (employees)

> I want a portal in another DB to display all the Jobs that a particular
> person (employee) has time logged in.

> Any ideas?

> Thanks,

> Trygve



Sat, 22 Nov 2003 03:42:48 GMT
 Complicated Relationship
Quote:
> then define a value list based on the job id field of the time
> log DB as related by this relation ship. The result will be a value list
> consisting of each unique job id on any record in the time log DB that is
> related to the current record in the employ DB. Now create an unstored
> calculation field in the employ DB = the design function ValueListItoms
> (employee DB, job id value list); use this field as a multi line key for a
> relationship to the job id field in the job DB. And use this relationship
> for your portal.

 I am not sure how to define a Value list based on a relationship - I can
see how to use a field from another DB, but not how to tie it to a
relationship.

Thanks!

Trygve



Sat, 22 Nov 2003 03:59:21 GMT
 Complicated Relationship
Hi Trygve

You need an additional file "Employees" containing an Employee ID field
(plus any other particulars that are needed).  In the Time Entries file,
there should also be a field for Employee ID.  This will allow you to set
up a relationship between the Employees file and the Time Entries file
based on the Employee ID field, which will in turn allow you to create a
portal in the Employees file that will show all Time Entries for each
Employee.  

The Time Entries file will effectively become the join file in a
many-to-many relationship between jobs and employees and will open up all
kinds of possibilities for accessing related data.  For example, you
should be able to access Employee records directly from the Time Entries
portal in the Jobs file by creating a script that goes to the related
record in Time Entries then runs an external sub-script in Time Entries
that goes to the related Employee, then formatting a button to the script
and placing it on the first portal row of the Time Entries portal in the
Jobs file.  Data from Employees can also be shown in Jobs by creating calc
fields in Time Entries that = those fields and placing the calc field in
the Time Entries portal of Jobs. The same thing can be done to access
information about jobs from the Employees file.

--
Bridget Eley


Quote:

> I have a DB of Jobs and Time Entries

> These are related by Job#

> Time Entries contains a Date field as well as In & Out times
> Each Job will have entries from a number of different people (employees)

> I want a portal in another DB to display all the Jobs that a particular
> person (employee) has time logged in.

> Any ideas?

> Thanks,

> Trygve



Sat, 22 Nov 2003 04:11:32 GMT
 Complicated Relationship
I think I've got it working so far. One complication is that on the Employee
DB, we only want to view Time Entries from Open Jobs

So I have created a Calc field (Job Status & Job#) which is used for the
related portal.

If The portal was based only on Job#, new entries could be created easily.

If a portal's relationship is based on a calculated field that is the
concatenation of 2 other fields, I see no way to add new records into the
portal directly. (Since when I enter the first data and tab to the next
field, the relationship is not yet valid.

Trygve

Quote:

> Organization: -
> Newsgroups: comp.databases.filemaker
> Date: Tue, 05 Jun 2001 06:11:32 +1000
> Subject: Re: Complicated Relationship

> Hi Trygve

> You need an additional file "Employees" containing an Employee ID field
> (plus any other particulars that are needed).  In the Time Entries file,
> there should also be a field for Employee ID.  This will allow you to set
> up a relationship between the Employees file and the Time Entries file
> based on the Employee ID field, which will in turn allow you to create a
> portal in the Employees file that will show all Time Entries for each
> Employee.  

> The Time Entries file will effectively become the join file in a
> many-to-many relationship between jobs and employees and will open up all
> kinds of possibilities for accessing related data.  For example, you
> should be able to access Employee records directly from the Time Entries
> portal in the Jobs file by creating a script that goes to the related
> record in Time Entries then runs an external sub-script in Time Entries
> that goes to the related Employee, then formatting a button to the script
> and placing it on the first portal row of the Time Entries portal in the
> Jobs file.  Data from Employees can also be shown in Jobs by creating calc
> fields in Time Entries that = those fields and placing the calc field in
> the Time Entries portal of Jobs. The same thing can be done to access
> information about jobs from the Employees file.

> --
> Bridget Eley



>> I have a DB of Jobs and Time Entries

>> These are related by Job#

>> Time Entries contains a Date field as well as In & Out times
>> Each Job will have entries from a number of different people (employees)

>> I want a portal in another DB to display all the Jobs that a particular
>> person (employee) has time logged in.

>> Any ideas?

>> Thanks,

>> Trygve



Sat, 22 Nov 2003 05:15:43 GMT
 Complicated Relationship
It's in the same dialog. You select the field and select use only related
values- but I forgot to mention only in FM v5.x. Sorry- helps, if when you
post you tell what v. num you're using.

--

C. Alex Lorda


Quote:
> > then define a value list based on the job id field of the time
> > log DB as related by this relation ship. The result will be a value list
> > consisting of each unique job id on any record in the time log DB that
is
> > related to the current record in the employ DB. Now create an unstored
> > calculation field in the employ DB = the design function ValueListItoms
> > (employee DB, job id value list); use this field as a multi line key for
a
> > relationship to the job id field in the job DB. And use this
relationship
> > for your portal.

>  I am not sure how to define a Value list based on a relationship - I can
> see how to use a field from another DB, but not how to tie it to a
> relationship.

> Thanks!

> Trygve



Sun, 23 Nov 2003 15:01:50 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Newbie - Help with complicated relationships

2. Doubt with relationship to the relationship?

3. Relationships using relationships?

4. Indeterminate relationships in Relationships Window

5. Complicated SQL Statement

6. Simple question - complicated solution ?

7. Help! Complicated SQL Statement

8. complicated SQL - need ideas

9. Really Complicated Easy Query

10. More complicated aggregation using unary operatos, custom rollup formula and custom member formula

11. Complicated Searches

12. Complicated Query


 
Powered by phpBB® Forum Software