Use self-join to get group of record of same file

[Anh_Tran]

Dear All,

I got a database name A

I got field : Manager and employee

So I use self-join on Manager to get employees with the same Manager

I put : Relation: manager and in the portal I put Relation: employee

And i have a script attached to it I write

Setfield (Realtion:employee," Bill ")

And find it ...it does not work to get many employees with same Manager ...Is it the good way to get it ? or you have another approach?

Thanks

[CobaltSky]

I suggest you create a global field called gManager and set up your self-join so that it matches 'gManager' with 'Manager', then modify your script so that it runs as follows:

Set Field ["gManager", "Bill"]

Then a list of all employees who have Bill as their manager will appear in the portal you've described.

[HazMatt]

Cobalt, wouldn't the global field solution only be a temporary help, as global fields are only good for the login session? (except for DBs that are not shared).

I have the same question, I think:

We have "Parts.fp5" that contains many records, all with a unique Part Number. Many of these parts are built up to make assemblies, and many of those assemblies are again built up to be final products. These assemblies and final products all need to have what we call a Parts List, or basically a list of all the parts that make up the given part.

I had tried using a self-join relationship, but I could only get it to work for one sub-part. Any ideas?

I suspect I will have to create a 2nd database called "Parts_List.fp5", and whenever a part deserves a parts list, I will script a new record in this new database, and somehow go from there. But, I'm not sure either way! :

[LiveOak]

The self join for a parts list file will work, with a few caveats of summarization up through the levels.

"I had tried using a self-join relationship, but I could only get it to work for one sub-part. Any ideas?"

This is a little vague, kind of like "I tried a car once, but it didn't work". Could you be a little more specific?

FileMaker is far from ideal for linked list type structures, but it should work for what you are trying to do. I would use a multi-key field called "Parent". In this field put all the assembly/subassembly/subsubassembly/etc. part numbers that a part belongs to, separated by carriage returns:

123

123-1

123-11

123-111

I'd then create relationships from Part Number to this Parent field and from this Parent field to Part Number. This will allow the display of all the part numbers that make up a part (Part Number to Parent) or all the Part Numbers that contain a part (Parent to Part Number).

-bd

[CobaltSky]

Yes, the global field approach is designed to provide a short term 'look this up' facility a little akin to a constrained 'find' that can occure off to one side (displaying its results in a portal) while you are still browsing records.

If you want a more permanent cross-referencing feature, then a self-join that matches the key field to itself is more appropriate (eg in the original instance matching the manager field with itself).

If you want the more broadly based location of related entities, including sub-categories, as Brent says, you'll need a calculating key field on the right side of the match which will pick up partial matches.

If this is the case, you may find the FMI article on partial match techniques at:

http://www.filemaker.com/ti/103956.html

to be of interest.

If you want to display enclosing categories as well as sub categories, then you are likely to require a relationship which matches calculation to calculation along these lines (though depending on the content and hierarchal structure you're working with, the formulae for the calcualtions for left and right keys of the relationship might need to be rather different.

[Anh_Tran]

Dear All,

I am really stuck in self-join relation ...I could get the many employees of the same manager ...but when I find specific record in portal ...if finds everything ..let's me give u an example

Parent Field :MAnager : John

In the portal :

Employee : Bill

Peter

Paul

ok I click find and I enter "Peter" in the portal ...it gives me all three names of that portal ...I really have no clue ...

Dear LiveOak and Cobaltsky ...If you have an example of self-join relation file I could see ... I love to learn it since I tried for 3 days already ....

Dear Cobaltsky : I went to your site but did not find out the example like above

My email : [email protected]

ps: even 2 separate files, could not find in portal

[HazMatt]

Well, I decided to go with a seperate database. It simplified the implementation for me, as well as scalable printing. Sorry if I was vague before!

-Matt

[CobaltSky]

Well, that is exactly what one would expect it to do. The portal, after all, is a list of staff who have the same manager.

'Peter' therefore appears in the portal for all the records on which John is the manager. If you search on 'Peter' in the portal, you are searching for all the records that have peter's name in the portal - so naturally that produces the records for Bill, Peter and Paul.

I'm not quite sure why, but it seems you were expecting something else to happen? - but you don't say what, so I'm trying to guess...

If what you were trying to do is jump to the record for Peter, then you will need to include a button in the portal which is attached to the Go to Related Record [show only related] command. When you click on such a button in the portal, it will be the same as performing a find in the main database field on the name of the person in the portal row you clicked on.

If that's not what you're trying to achieve, it might help if you say what it is you DO want to do, so one of us can suggest a way to go about it

[Anh_Tran]

Thanks All

My head is not working now ...

I give up .....try to find another time ...but if you have an example with self-join I would love to see it or any site that could help ... I love it too.