Multiple Self-Join, Related Fields, one display

[PTKen]

I've been searching this archive for a few hours now and cannot find anything that directly relates to my question:

I have a file with a self-join related twice by two calculated fields using concatenated fields as the key (thanks to this list for the idea!). The idea is to search for a client name and view all projects that are "active" or "complete" for that client only. One relation/calculation handles "active" and the other handles "complete".

Now I have two portals side-by-side on one layout, one to display active projects and the other to display completed projects. They both work correctly. Except...

Let's say I have a client "ABC" with two active projects and one completed project. If I am on a record that has "ABC" as the client and is active, then the active portal shows the two records, but the completed portal is empty. If I am on a record for "ABC" where the project is completed, then the project shows up in the portal, but the active portal is empty.

Is there some way to have both portals show their related records on the same display regardless of what the current record's status is?

BTW-the calculations are done as such:

if(client_name & job_status = client_name & "active", client_name & "active", "")

if(client_name & job_status = client_name & "complete", client_name & "complete", "")

Thanks!

Ken Fleisher

[Kurt Knippel]

Your calcs are the problem, only ONE of them is ever going to be valid.

Just eliminate the IF () entrely. This will ensure that both calcs will always evaluate and both portals will always be filled.

[PTKen]

Just eliminate the IF () entrely. This will ensure that both calcs will always evaluate and both portals will always be filled.

That's actually how I had it at first, but that doesn't work either. If I have just one calc field calculated as "client_name & job_status", then what would I use as the relation? Right now it's a self-join relating each of the calculated fields to themselves. That's why it works when on a record when it contains the same job status (it either matches or is empty and therefore doesn't match), but if I just set the field to the client name/status, all records will always match so I don't get the right results. Both portals show every record.

I can't just hardcode client_name & "active" because I need to test the record to see if it's actually active or not.

If I use two calc fields as I have now, I don't see how to do it differently than with one calculated field.

Do I need to break this out into another file? I was hoping a self-join would make it simpler. Thanks again for any advice you can offer.

Ken

[PTKen]

Hi again! I've tried every combination I can think of to make this work and I guess I'm just not seeing something. Does anyone have an example of a single file with a filtered self-join and two portals as described in my other post that you could upload? Or describe how to make it work for me here? I would very much appreciate any help that can be provided.

Happy New Year!

Ken

[Kurt Knippel]

Fields:

cClientActive_match = Client_Name & " - Active"

cClientCompleted_match = Client_Name & " - Completed"

cClientStatus_match = Client_Name & " - " & Job_Status

Relationships:

cClientActive_match to cClientStatus_match

cClientCompleted_match to cClientStatus_match

[PTKen]

Success!

Thanks so much. This was driving me crazy! I was using the current records composite of "Client Name & Job Status" in my relation rather than creating a third calculated field of this composite for each record. It's so obvious now I don't know how I didn't see it. (duh...)

Thanks again. The help of others is always appreciated and never taken for granted. (Hopefully soon I'll be able to answer someone elses questions and give something back!)

Ken

PS Happy New Year!

[IdealData]

Well done, a good use of an advanced FMP facility.

Watch out for the 20 char limit

Only the first 20 (before a "return") characters of an FMP field are indexed, and consequently only those characters form the foundation for the relationship.

I'm presently designing a multi-user, multi-calendar diary system which relies on the related "key" have multiple "lines" for various purposes. When I exceeded the 20 char limit, FMP saw fit to "match" on the first 20 chars of the 1st line of the "key" - even though the 2nd line of the "key" was valid.

I also use a lot of GLOBALS for "dynamic" relationships, and you can script a lot easier.

[PTKen]

Excellent point! I suspect I will run into this limit once I have real data. I'm a little confused though, is it the first 20 characters of the whole line (i.e. composite key) or first 20 characters of each word? If I don't have any "words" longer than 20 characters will it work?

Suppose I can't avoid client names and/or keys that would exceed this limit. Any suggestions on how I would deal with that situation?

Thanks.

Ken