Relationships & Complications

[ideawire_bb]

I currently have (2) files:

File (1) = Timesheet

File (2) = Estimate

I have established a relationship between File 1 & File 2 via the client name field that correlates records between the two files. It works great.

My problem: The Estimate file has 6 years worth of estimates, so depending on the client, it takes a long time to display all the records. In the Estimates File, we have a layout called Active Jobs that displays only the active jobs for active clients. I want to modify the Timesheet file so that it only pulls records from the Active Jobs layout in the Estimates file and NOT all of the matching records in the file based on client name.

Additional info: Due to other applications using the Estimates File, I cannot remove the old records. I have created an Apple script, that upon launching the Filemaker File, switches to the Active Jobs layout. I figured this would solve my problem, but it doesn't.

I hope this is clear -- if not, please ask me for clarification.

Thanks in advance for any suggestions on how to fix this issue.

Best,

Brenden Bixler

Ideawire, Inc.

[Ugo DI LUCA]

Welcome to this Forum

In the Estimates File, we have a layout called Active Jobs that displays only the active jobs for active clients.

This is highly improbable with FM6 unless you are using a filtered relationship or a special procedure. But then manually switching to another layout wouldn't help display some other foundset.

I want to modify the Timesheet file so that it only pulls records from the Active Jobs layout in the Estimates file and NOT all of the matching records in the file based on client name.

Again, this kind of filter would need to be based on the data itself, not the layout. If you have a field, say ActiveYN, you may :

- create a CompoundKey = CustomerID&" "& AciveYN

- use a similar compound, probably involving globals on the other side of a newly created relationship, that you'd use to filter the active records for a given customer.

ActiveYN needs to be indexed though.

Then it would work.

[ideawire_bb]

This is highly improbable with FM6 unless you are using a filtered relationship or a special procedure.

You're right. I have an Apple-script tied to that layout that is based on a previous "search" given ActiveYN = Y.

So, if I understand correctly, I need to create an indexed, compound key, consisting of client_name & ActiveYN in both the Timesheet and Estimates file.

Thanks for the suggestion.

[Ugo DI LUCA]

Hi,

On the Right side, the compound key must be indexed.

But It can be unstored, involving global fields on the Left side.

You may have a cYes = "Yes" there and use a Compound of ClientID& " "&cYes as your Left key.

cYes can also be a global field gYes filled with "Yes" or a boolean structured result.

Or you can use a gActivity global field with a value list attached to it with either Yes or No, and use it in place of the cYes to dynamically toggle and filter your related records from one status to the other.

But I would think the last one doesn't fit your needs.

[ideawire_bb]

Hey Ugo.

You're talking a bit over my head now. I'm reading the documentation now on how to create a composite key but would appreciate any direction on how this is actually accomplished. Am I creating a new field?

[Ugo DI LUCA]

Yes,

A new Calculated field, on both sides.

One, on the Right Side (when looking at the Relational Box) with CustomerID & " " & ActiveYN

One, on the Left Side with CustomerID & " "& cYes

Of Course, if you want all active "accounts", just use a relationship cYes::ActiveYN.

[ideawire_bb]

Great. It works. Thanks for your help!