how do i display records only if criteria match ..

[J__]

Hi,

I posted something similar in another forum, but it wasn't enough for me to figure it out.

I have a portal of Projects

I have a drop down list above, which displays a list of authors of that project. When i pick a name from the drop down list (which is a value list (not in a table)) i filter the portal to only show those matching names. I use a global for the drop down with self join and this works fine.

This part i can't figure out though:

the projects table has a 1-M relation with a tasks table. and it turns out that a task has an author too. Someone else could have created the Task - who is not the Project Author... The task tables author field is a value list too (like above) and has the same names in it.

So, when I pick the name from the drop down list, i want the portal to show a project in that portal IF the projects::author = dropDownListAuthor OR Tasks::author = dropDownListAuthor

So, if my name, say Fred, is picked in the drop down, and I authored a Project called "Bedrock", then that project appears in the portal. If there is another project called "Core", which has a project author of Barney, but one of the tasks related to the "Core" project has my name, Fred, in it, then that project should also appear in the portal.

What is the relation for that? I have noodled with this quite a bit and I still haven't been able to figure it out.

thanks for any help in advance,

sincerely,

J__

[Lee Smith]

[color:"red"] Do Not Double Post in the Forum

If this post is the more accurate one in explaining what you need, then go back and delete the other posts.

Double Posting in the Forum is a violation of List Etiquette, and is [color:"red"]not necessary, because the Forum is really only one list with several areas of interest. BTW, double posting is the quickest way to upset the Members and can cause them to ignore your posts.

Lee

[RalphL]

This could be a star join. 3 main tables are People, Project & Task, each with an ID field as the primary key. The join table has 3 foriegn keys, one for each of the other 3 tables. It may contain data that applies to the combination of the 3 keys.

A portal in people to the join would show the projects & tasks for a person. A portal in project would show people and tasks. Project Author would be a task.

[J__]

Lee,

in no way am I trying to upset anyone. I really like this forum.

The question I am asking here, is not a duplicate of any other question - that is, you wil not see this question anywhere else but here. However, I did ask a similar flavored question and it was stated in a different way in the file & searching section. I asked for follow up and while I got some responses, i really found that I needed a little more explanation - or hoped to get some more, but didn't...This was probably on Dec 8 or earlier.

After thinking the problem over a little I realized that the question was probably better suited for Relations, since this is really what I need to get it to work, so i wrote a completely new question and asked it here.. Sincerest apologies.

----

RalphL, thanks for the response. Ok, let's say i have an Authors table, a Project table and a Tasks Table. with an project_PK field for projects, tasks_PK for Tasks table and a authorsPK for Authors table.

.. what one needs to be the join table? could you please explain in a little more detail?

thanks,

sincerely,

J__

[Barbecue]

J__, Double-posting is definitely frowned on

But I don't think you're really guilty in this case. Here you are asking about creating a relationship for a portal, while the previous question was about doing a wildcard search.

That's the problem with having such a big forum with so many topics; sometimes it's hard to decide where you should post.

[Lee Smith]

Hi J__,

Sorry if they are not Double Posts, but I read both of your posts, one after the other, and they seemed similar.

I was getting ready to reply to "[color:"blue"] how do I Match fields with first char?", but when I read "[color:"blue"]how do i display records only if criteria match .. " the problem to me seemed the same, but asked a different way, especially when I read your first line "[color:"blue"]I posted something similar in another forum, but it wasn't enough for me to figure it out", it sounded like a confirmation of a duplicate post to me.

Anyway, you have received some responses, let us know if you need more help.

Lee

[RalphL]

Not knowing you abilities, I am going to make this as simple as I can. The join table is sort of a line items table. When you have a one to many relationship the many taable is often called aa line items table where each record represents a line say in an order. Say the order table has an order number as its primary key then the line items has this field as its foreign key.

When you have a many to many relationship you use a join table. This becomes a one to many relationship from one of the tables to the join table and a one to many relationship from the other table to the join table. In this case the join table has 2 foreign keys one for each the primary keys from the other 2 table. In some databases the concatenation of the foreign keys becomes the primary key for the join table. This implies uniqueness to this field.

A star join is like a many to many between 3 or more tables. Thus the join table must have a foreign key for each of the tables joined by it. In your case this would be 3. The concatenation of the foreign keys should be unique.

[J__]

Lee, sorry, one was matching the wildcard and the other was a relational join type of thing i posted them to solve 2 different problems. I think I can see why you might have thought they were double posts and I'll try to make the subjects more less similar - thanks really!

RalphL - thanks for the response.

Simple is good. I know a bit of SQL, but haven't mastered the abillity to translate anything i see in my mind in SQL to filemaker, it seems to me to be a paradigm shift or just a different way you have to look at it.. dunno..

I think I follow you.

but if you have a Projects table, with 1-M to Tasks and each of those tables has a field called Authors, which relates to the Authors table, which table is the join table?

I could see the sql being something like (slightly pseudo code here)

Select Projects.Name, Projects.Author

From Projects, Tasks

Where ( Projects.Author = zUserSelectedAuthor ) OR ( Tasks.Author = zUserSelectedAuthor );

but with the relation view in Field definitions of FM, there doesn't seem to be a way to do this.. or at least I don't understand what needs to be done to mimic the above.

thanks for tips and/or suggestions or help,

sincererly,

J__

[RalphL]

3 main tables are People, Project & Task, each with an ID field as the primary key. The join table has 3 foriegn keys, one for each of the other 3 tables. It may contain data that applies to the combination of the 3 keys.

The join table is the 4th table.

Why do you think Projects is a 1-M to Tasks? Wouldn't other projects have the some of the same tasks?

In the templates that come with FMP is one called Task Management. It has a star join. This may help.

[J__]

Thanks for the response RalphL.

Other projects would not have some of the same tasks. I create a project and then create tasks unique for that project.

For example, a Project: Build Guitar

- make neck of guitar

- create body of guitar

- put in mother of pearl inlays on neck

- floyd rose guitar tremelo system put in

they are tasks that could be another project, but in my case they may look the same but the Project Author creates the project and he/she or other 'authors' may add tasks.

When they open the app, they want to see the projects that they have been working , so I let them pick from a drop down list their name. They may have also created a task in a particular project, so that project should appear too, since they have worked on it.

thanks for the suggestion on the Task Management Template examples are great.

thanks for the responses, I appreciate it.

sincerely,

J__