Hi,

Here's my problem..

The Relationships are as follows:

Person.fp5----manyToMany_join.fp5----Project.fp5----anotherManyToMany_join.fp5----Request.fp5

This is working fine but I would like each Person to see any Requests raised against the Projects they are working on in their View.

How do I do this.

Thank-you FM Daddys.

This technique is variously called "pipelining," "shunting," or "tunneling." There's an excellent description by a fellow forum member here:

http://www.nightwing.com.au/FileMaker/resources/MatrixModelling.pdf

I've tried to implement what they illustrate in this document, but I'm only getting a single record shown in the portal

rather than multiples, I guess I've somehow lost the multiple relationships, any ideas how I retain them??

J

have you tried using Project as your main database and using a technique known as multi-keying to branch to the "People" and "Requests" databases? This woultd eliminate the join files, as they are not necessary in Filemaker (as opposed to MS Access) Simply use a return separated list as multi-value key. . And for the keys use concenated Fields like "Project# & " " & Request# & " " &Person_ID.

CJaeger, not to be picky, but you actually are using a join table, you are just using a single field in the same DB to act as the join table. Access and other SQL-style system make it a seperate table, because why not? In Filemaker a seperate table usually means a seperate file, but I implement multiple tables with a single file all the time and it looks like you do as well.

Just wanted to point out one more area where Filemaker is not that much different from other RDBMSs.

Without having read the PDF file Fitch mentions:

If the keyfield used in Person.fp5 is auto-entered into records in Request.fp5 (that is, when records are created through a portal in Project.fp5, which is how I presume you're doing it), then you should be able to create a relationship between the two files.

Or did I miss something here?

Persons are related to Projects and Requests are related to Projects, both relationships are done with many to many joins.

I can Display the Projects a Person is related to quite easily via a Portal into the PersonProjects_join but am having difficulty listing the Requests for a Project in the Person View - i.e. a list of Requests applicable to a Person's Projects.

Do you sugges another many-to-many between Person and Request somehow?

thanks,

J

Hi,

As I unserstand it :

1.a person may have many projects.

2.a project may have many requests

3.a person may have many requests....

That seems very close to my solution where a customer may have 2 proposals splitted in 4 invoices ....

Then I'll use what I call a Multi-line Item using the same Join File for Projects and Requests, but using a "Unique Line ID" in the Join File (Line Item) that would be created for any new Request.

That suppose that projects are created through a portal, based on "Previous Requests" using a filtered portal related on that Unique ID.

1.a person may have many projects.

2.a project may have many requests.

3. I want a Person to see the Requests for the Projects they are associated with.

Requests are assigned to Projects, not People.

i.e. When a Person logs-in they see a list of Requests relevant to them (a list of Requests for the Projects they are working on).

Is the above a solution to this?

I'm trying to keep things pure just incase I need to move to a relational DB solution in the future, but I've come up against this wall where 'shunting' dows not seem to work.

Any advice greatly appreciated.

rgds,

J.

Hi,

Last question.

Is one request related to only one projetct ?

If so, IMHO, it would be easy to locate each requests filtering :

- the Projects db on the Person_Id

- the Request.db on the Project Foreign key.

Are you with me ?

Nope, one request can relate to many projects (this is to ensure that good request ideas appear in all applicable projects).

J.

OK, then I'll use the same line item for both Projects and Requests.

When it comes to filtering, you will be able to find any records where in the same line will appear Person_Id, Project_Id, Request_Id.

Therefore, use a concanate calc in the line item :

Concanate = Person_Id & " " & Project_Id

In the Person.db, create 1 global field g_Project_Id and a value list of all projects related to that Person_Id. Set the global field to use this value list.

Create another concanate = t_Person_Id & " " & g_Project_Id

You have then 2 means.

1.Draw a portal using relationship Person.db:Concanate::Line Item:Concanate and place in it the Request_Id.

Any time you will select a Projetc Id, you will have a list of all Requests.

2. Use another global field g_Request in the Person.db and a new value list of all related requests in the line item. Apply this value list to the global field.

If there is no way a new line could be populated with the 3 Ids, I would personaly use the Unique Id and Multi Line Item method.

Do I need a new file named LineItem??

Sorry, I'm rather new to all this FM stuff.

rgds,

J

Hi,

Line Item is another word used for Join File.

You wil nned a unique Join File.

I've dropped a sample of a Multi-Line Item in the Sample section.

Multi-Line Item

In advance for all those fellow here, if this seems rather complex, I can assure that I find this file very useful in Multi many to many complex relationships.

I currently linked 11 files in my own system with that unique Line Item.

Sorry, I'm being a but stupid here, do I do as follows:

1. Create a new join file e.g. personProjRequest_join.fp5

2. Create PersonID, ProjectID, RequestID fields in above file.

3. Create Relationships to the Person.fp5, Project.fp5, Request.fp5 using foelds in 2. :?

4. Create a calc field in the personProjRequest_join.fp5

using Concanate = Person_Id & " " & Project_Id (these fields being in this file anyway).

Is this ok so far??

thanks for you help too.

J

Hi,

Yes. Start with this with "calm". Don't know you're business and file structure so far, so make a try to see if all the 3 Id's can fit on the same record in the Join file.

Sorry dilucaugo68, I'm still lost.

I would like a list of Requests (not the ability to select Requests as this is done during Request creation) to be shown in the Person.fp5 table. This list shows all Requests associated with a Project that a Person is working on.

I've tried all the g_ stuff + value lists and I'm still not there.

Any more ideas/help much appreciated.

Is there a simple way of doing this, or is the way you advised simple?

J.

Joseppic,

Let start it from the beginning....

That's what you posted.

Hi,

Here's my problem..

The Relationships are as follows:

Person.fp5----manyToMany_join.fp5----Project.fp5----anotherManyToMany_join.fp5----Request.fp5

This is working fine but I would like each Person to see any Requests raised against the Projects they are working on in their View.

How do I do this.

Thank-you FM Daddys.

If your solution is working fine, then it should be more simple to have a list than to have a view...

Let's call your Join files PersonToProject.fp5 and ProjectToRequest.fp5 (maybe RequestToProject would be the good way, but as you said Project--->Request....)

Person file :

If I understand, you should have already one relationship :

Rel_PersonToJoinfile : Person.fp5:Person_Id::PersonToProject.fp5:Person_Id

and maybe another :

Rel_PersonToProject : Person.fp5:Person_Id::Project.fp5::Person_Id (a Multikey ?)

Create 2 additional global fields :

- g_Project_Id

- g_Request_Id

Drop them on a layout.

Go in the menu define value list.

Create a new value list called "ProjectsVL"

Choose "from another file" and select PersonToProject.fp5

Choose related, and select "Rel_PersonToJoinfile"

Then pick-up the field Project_Id.

-----> This will surely give you a list of all Projects attached to the Person.

Create an additional global field, called g_Request and a new relationship :

Rel_PersonToRequestByGlobal : Person.fp5:g_Project_Id::ProjectToRequest.fp5::Project_Id.

Go in the menu define value list.

Create a new value list called "RequestVL"

Choose "from another file" and select ProjectToRequest.fp5

Choose related, and select "Rel_PersonToRequestByGlobal"

Then pick-up the field Request_Id.

-----> This will surely give you a list of all Requests attached to the previously selected Project_Id (in fact g_Project_Id), that was attached to the Person_Id.

BTW, call me Ugo or DI LUCA, or Ugo DI LUCA...dilucaugo68 makes me feel as a robot

Let us know if you got it to work....

Hi Ugo,

Thanks for your continued help.

After performing all you requested I have 3 fields in the Person.fp5 layout:

g_projectID

g_requestID

g_request

All these fields seem empty, what next??

cheers,

J.

Hi,

Ooops...

You need to format the g_project _Id and g_request_Id with the Value lists "Projects VL" and "RequestVL". Go in format and choose use list...

Don't know why you have 3 fields though. I'll check back my previous post.

g_Request (where did you found it ?)

You will see it better with this attachment

JOSEPPICREL.zip

OK, wow, when I select a Project in Person.fp5 I get its associated Requests Thanks.

If I would like just a list/portal displayed of this info, how would the code be altered??

Thanks again, you've helped greatly.

Hi, I've got it so that when you select a Project (this Person's Projects) from a Pop-up, it displays the Requirements for that Project in a Portal.

However, ideally (not sure if it is possible), I'd like it to display all Projects (a Person's Projects) and their Requirements at once without further navigation. Is this possible??

thanks, thanks Ugo,

J.

New attachment....

A script set a global field in the PersonToProject db,

Then sets g_ProjectId from a portal row selection.

Then the Portal fro requests use same relationship than first.

JOSEPPICREL2.zip

Thanks a lot, you have saved me a painful time trying to get this working.

take care,

J.

Take care

That's why I always keep my helmet on

Glad it worked....

HI, sorry to resurrect this thread but I would like some more help.

Now, I have a drop down global (project list) which on selection, updates a global projectID field which in-turn affects the related portal of related requests.

This works, however I would still like a person to see a list/portal of all his/her project's requests, rather than having to switch between projects with a global dropdown.

Is this possible?

Does this make sense?

J.

Hi,

Of course that makes sense.

As the requests are related to the Projects using the RequestToProject File and Projects are related to the Person using the PersonToProject...,

Use the value list item calculation for the left side (list all Project_ID related to the Person_ID in the PersonToProject file.

This will be your left side key.

and create a relationship from that calculation to the Project_ID from the Line Item RequestToProject.

Now you can drop your portal or a go to related record script...

Et voila.