Portal Filter

[mav]

I've read a number of posts, articles and examples on how to create a portal filter to reduce the records displayed in a portal, based on creating a global field and an extra table occurrence.

Do you think I can work out how to apply this to my solution . . .

I've attached my file, where I want to filter the portal (on the project window) to allow users to filter the results to just show the tasks for the current project, not all the tasks for the related contact.

Note: I want to be able to create a task from within the contact window, which is not related to a Project. Otherwise I would define the relationship directly between the project and the task.

Thanks in advance to anyone who can help me with my problem,

Darrin.

portal.filter.zip

[dkemme]

Your almost there!

Create a new field in Company called gProjectIDViewing, number.

Create a new relationship between Company and another instance of the table Tasks between the new field and ProjectID in Task.

Create a script that:

setfield[company::gContactViewing],get(scriptparameter)

commit records --import to get the portal to refresh, sometimes you also need a refresh window and flush

Then create a button in the portal projects that calls this script passing the script parameter "project::ProjectID".

Finally you need a new portal, based upon the new relationship, that will only show the tasks that are related to the project gProjectIDViewing

Notice that the global is in company and not contact. That is because the layout is based upon company.

HTH

[mav]

Thanks Doug.

I've attached a second updated file with your suggestions, and also added another filter on the company window to filter the tasks portal, based on the assigned to field, this time using the actual global field and a value list to select the filter for the portal records.

I have a further question on this subject, if I may . . .

With regards to the company window, I can filter the records in the tasks portal, based on a selection, but I wanted a selection to show all records.

Any suggestions ?

Thanks again in advance,

Darrin.

portal.filter.3.zip

[dkemme]

For a "Show All" button:

In Company, create a field called gShowAll, text, global and place on the layout as a radio button, Yes/No. Also create a calculation called cProjectIDViewing, text, case (company::gShowAll = "Yes" ; "All" ; gProjectIDViewing ). --this will place all if you want to see all tasks or just the related project.

In Tasks creat a field called cProjectIDAndAll, text, "All<p>" & getastext(ProjectID) --<p> is the paragraph symbol, this creates.

Now update the relationship in Company to use these two new calculated fields. This has not been tested, I wonder if you will have any issues with the text and number fields, may need some tweaking.

HTH

[mav]

I'm having trouble getting this to work . . .

What I'm ultimately looking for is to keep the existing relationship, and to be able to filter the portal, and then have the option to show all records once again.

From what I am seeing with this solution, I need to navigate between many layouts and relationships to get what I want.

Does any one have any ideas ?

Darrin.

[RalphL]

A little more information would be helpful. What is the normal relationship? What is the filtered relationship? Maybe a calculated field as one of the keys would do the trick.

[dkemme]

I modified you last attachment a bit to show you how a key based upon a calculated field. You do not need any additional layouts, but you do need to add a few more fields/relationships.

[RalphL]

After looking at your second attachment, I wonder if your structure is correct.

I am making the following assumptions:

Company has a one to many relationship with Contact. Contacts are employees of a company.

Contact has a one to many relationship with Project. A contact may be on more than one project. Could a project have more than one contacts?

Project has a one to many relationship with Task. A project has many tasks.

Task has a many to many relationship with Staff. A task may have one or more staff person assigned to it and a staff person may be assigned more than one task. This means you need a join file, lets call it Assignment. I am wondering if this should be a star join.

[mav]

Doug:

Thanks, I'll study this to see what you have done / changed . . .

Ralph:

The contact is in fact an employee of a company, where this is the employee who has commissioned OUR company to complete the project, and is, for a better word, the "client".

I see what you are saying regarding a project having more than one contact, although multiple contacts would not give a clear definition of responsibility and accountability for the project - from THEIR end.

In my relationship model, a task does not need to be part of a project (but it can), and can be as simple as returning a call, which is just related to the contact itself.

Also, there can be many staff involved in a project, with many tasks, although each task is for one person to complete.

Not shown in this model is that each project is assigned to a staff member, giving clear responsibility and accountability for the project - from OUR end.

If this relationship still needs a STAR join, could you please extend how to apply this to my database ?

Thanks in advance,

Darrin.

[RalphL]

It doesn't look like a star join. In fact there is no many to many relationship. I think there needs to be a relationship between Project and Task. The portal in Project layout needs this relationship.

[mav]

Right again Ralph.

This would then create a 2nd TO of Projects, which I hadn't got to yet . . .

Thanks again,

Darrin.

[RalphL]

I have redone your file. I tore it apart and rebuilt it. I rewrote 2 scripts and added 1. I changed some field definitions. I add a dummy table which I used to label the graph. The graph is where I made the major changes. I used a Table Occurance Group (TOG) for each layout. I used my nameing convention for the TO's. Layouts look the same but different relationships are used.

Hope you find this useful.