how to display appropriate data on layout

[agtjazz]

Greetings, this may be long, but I am hoping that I am clear about the situation and my issue.

I have the following:

people table

job table

contract table

Relationships:

People to job (1 to many) and then job to contract (1 to many)

1 person can have 1 or more jobs…. Each job record could create 1 or more contracts. or 1 person (ie Staff) can have a job record but will never have contract records.

I created a report (based on the job table) and scripted a find (that follows rules) to display the appropriate data (there are actually 4 reports – 1 for each quarter. Ie Fall, Summer, Spring, Winter).

Problem: It’s not displaying the appropriate data on some people (people with more than 1 active contract)

So John Jones has a job record and 2 contracts

1 contract is for summer with summer info (salary, fraction decimals etc0

The other contract is for fall/winter (with fall & winter info –salary etc)

The summer report shows John Jones no problem with data

The fall report shows John Jones, but doesn’t show fall salary- it is showing summer info (which is blank)

How do I tell it which contract data to use?

If I pull the report off the contract table, the people that do not have contracts, do not show up (and there are some folks that need to show up) For example, Staff should show up on the report, but do NOT have contracts in the contract table.

Below is my script (called Fall Journal)

-Go to layout {“Journal-Fall” (Job)]

- Perform Find [specified Find Records; Criteria: Job::jb_emptype=”Staff” and Job::c_jobactive=”Active”

Find Records; Criteria: Job::jb_code=”2481”

Find Records; Criteria: Job::jb_code=”2482”

Find Records; Criteria: Job::jb_code=”2360”

Omit Records; Criteria: Job::jb_empsubtype = “Temp Hourly”

[Restore]

-Sort Records [specified Sort Order:Dept::dp_reportunit; ascending

Job:;jb_code ascending

People::empid ascending]

Thanks in advance.

[Jeff Bills]

What's the relationship betweent the job and the job contract? An ID field? Or?

Attach an example

[agtjazz]

Hi there.

I will attach a sample real soon- but in response to how they are related ---

emp_pk (primary key that is serialized)

jb_pk (primary key that is serialized)

cn_pk (primary key that is serialized)

in people table

emp_pk is the primary key

in job table

jb_pk is the primary key

emp_fk is the foreign key that relates to people table (people::emp_pk - job::emp_fk)

in the contract table

cn_pk is the primary key

jb_fk is the foreign key that relates to the jobs

(job::jb_pk - contract::jb_fk)

I will post up a sample once I return to my office in about an hour

Thanks in advance for your time and advice.

[agtjazz]

Here is a sample file. I removed all personal data that could be considered confidential... but the relationships and other data are intact

There are approximately 10 records... examples of all varieties that have to show on the report. Some are staff with no contracts, some are tenured with no contracts, some are lecturers with one contract and some are lecturers will multiple contracts. (the ones that have multiple contracts are the ones that I have the problem displaying the correct fields)

If you open the database, it will take you to the appointment tab on the People Layout. On this tab, I put a button just to help you navigate around (button goes to Fall Payroll reports). This button runs a script called FALL 672 that does the appropriate find queries and takes you to the FALL 672 report layout.

On the layout- I wrote notes about what my issue is. Basically there are 3 people in this sample database that have more than 1 contract. On the Fall report, the fields cn_timbase_fall should be populated- but it is NOT.

Your advice is greatly appreciated. I am not sure if the script is incorrect (or I am missing steps to tell it which contract to pull) or something.

Thanks bunches

help-journals.zip

[Jeff Bills]

Well here's my take on part of the problem. Filemaker will always and only show the FIRST match (unless you're viewing results in a portal) .

You have a relationship between your JOB table and your CONTRACT table joined by "=" between jb_pk and jb_fk.

And, yes, Elias, Englert, and Eastermann have more than one record.

Let's use Englert as an example. Filemaker searches for the match . . . again your match criteria is between jb_pk and jb_fk as stated above and BOTH have the value of 5818.

Filemaker finds the first match which happens to be the 5818 for (Summer, I think) and since that record's cb_tb_fraction_fall fields is empty . . . you have what appears to be no result.

What you're doing is pretty complex (to me : )

I would suggest you rearrange your tables and or create a calculation field for use in a new relationship that will eliminate any unwanted record from being a part of that match.

[agtjazz]

Thanks Jeff for your response and time.

I wasn't aware that Filemaker finds the first match and stops searching... there has to be a way (or work-around) to this.... since you can't print a portal (and the report I need to print should really be a list anyways).... mmmm what to do?

A calculated field for a new relationship--- let me work with that a bit... but if anyone else can chime in, I would really appreciate the help.

[Jeff Bills]

How about creating jb_pk_fqtr, jb_pk_spqtr, jb_pk_sqtr calc fields in the JOB table represtenting jb_pk and Fall, Spring, Summer quarters, respectively

Example

jp_pk & "Fall" resulting in "5818Fall" for the jb_pk_fqtr field

jp_pk & "Spring" resulting in "5818Spring" for the jb_pk_spqtr field

etc

and then set a calc field, say jp_fkqtr in CONTRACT to combine the jp_fk field and produce "Fall," Spring," or "Summer" depending on the which cb_tb_fraction_xxxxxx field is empty/full, thus, also resulting in EITHER "5818Fall", "5818Spring", etc.

Create new relationships between each of newly created JOBS jp_pk_(quarter-type) fields and the ONE jp_fkqtr field in CONTRACT . . .and put somewhere in their titles, the corresponding "Fall", "Spring", etc (jobs_CONTRACTS_Fall).

Then, when you access the Fall, Spring, etc layout (I'll just stick to Fall from now on) set the cb_tb_fraction_fall field to the corresponding relationship (xxx_xxx_Fall)

Make sense?

I hope this helps

[comment]

I think you are missing a table. I am saying this with caution, because the file is a bit too overwhelming to take in at a glance. But it seems quite clear that a single contract record should not hold information for multiple seasons - if you intend to report or find by season. So a better structure might be:

People > Jobs > Contracts > Seasons

These are all one-to-many (from left to right), and it should be quite easy to isolate the desired records in the Seasons table and print them as a list - with or without complementary data from the parent tables.

[Fenton]

Duh. I see now that he said "People to job (1 to many)". I thought it was many-to-many. I'm deleting my earlier post. This is much easier than I thought.

[comment]

Good excercise, though. :laugh:

[agtjazz]

Thanks to all the advice.... and food for thought

I will do some testing and post back if I run into issues.

Thanks again to all of you for the insight.

[agtjazz]

Thanks for the advice and I added another table (called Seasons) and it seems to work - reports and all. I am still in testing mode---but have run into a issue in the portal

So I have the contract details layout and there is a portal for the season (with quarter name ie Fall, Summer, wtu, fraction, salary etc). How do I create totals in the portal? I need a sum for WTU, Salary, Fraction, Decimal etc.

I have attached a screen shot for a better idea.

Thanks bunches

[agtjazz]

Never mind--- aye :

As a calculated field- Sum (sn_wtu) works

Thanks again... it seems that I need to bounce things off of this great forum, and then I get it and am embarrassed that I posted some simple questions

Thanks again

[agtjazz]

Ok I think I am back at square one.... and going backwards

On a test database, I implemented a new table Seasons.... that relates to Contracts (contracts::cn_pk -> season::cn_fk)

But on another form (different report than payroll journal-but both are needed) I have to list if one contract holds Summer, Fall, Winter, Spring Info (and now that we are getting into Fall, alot of contracts hold Fall, Winter, Spring on 1 contract). I made calculated fields- Case(sn_name=Fall;sn_wtu) for fall, spring, summer, winter... but it's only looking at the first record- not all the records.... please tell me I am NOT back where I started from

Any advice?

[comment]

I am not sure why this would be required - after all you can list the seasons of a contract by reporting from Seasons, subsummarized by Contract, so you'll get:

Contract ABC:

• Summer

• Fall

Contract XYZ:

• Summer

• Fall

• Winter

etc.

In any case, you can define a value list of season names, including only related records, starting from Contracts. Then you can check (by Position() or PatternCount() if a particular season is included in the list.

[agtjazz]

I am having a hard time making a report with subsummaries..... can you point me to a link or sample?

Mine comes out like this:

John Johnson

Contract ABC

WInter

Winter

Winter

thanks again

[comment]

Your layout must be based on the Seasons table. You should have (at least) these layout parts:

Sub-summary when sorted by

- the field with the person's name, from the related People table goes here

Sub-summary when sorted by

- the field with the contract's name, from the related Contracts table goes here

Body

- the field with the season's name, from the Seasons table goes here

Don't forget to sort by the fields defined in the sub-summary parts.

[agtjazz]

Thanks I am giving that a whirl today.

Thanks again for all the help and patience!