Filter Key and Locator Keys

[FredP]

I am using a field in tableA::LocatorField as the join relationship to tableB:FilterKey.

 

TableA:LocatorField would be something like

 

All

Projectname

ProjectStatus

 

So that you can filter a portal to show only Projects with a particular Status, a Particular name, or ALL to show them all.

 

That is working fine. But what I would like to add, is a way to filter "My Projects" based on if there are any related records in Table C from TableA that have tasks assigned to a username that matches the Get(AccountName) that is logged in.

 

I thought something in the locatorfield such as this could work :

 

If(PatternCount(List(TableC::EmployeeOnTask));Get(AccountName);"My Projects";"")

 

But what seems to happen, is when I try that, the data in the locatorfield works, and actually shows the following result:

 

All

Projectname

ProjectStatus

My Projects

 

in the LocatorField field, but the relationship stops working because filemaker sets thelocator field to be non-indexed field for some reason that is unknown to me. So the actual portal that was working moments before, now shows zero matching records, no matter what the filterkey is set to. The relationship no longer works. Remove the line that checks for a pattern count of a list from tableC and it works again.

 

Can anyone help me find a better way to do this?

 

Hopefully this makes sense and someone will have a moment to help. Im really stuck on this one.

 

Fred

 

 

I thought some

[Ender]

I can't tell which way your relationships are going, but it sounds like you're trying to put that list() on the child side of the relationship. This won't work as list() cannot be kept up-to-date and stored. Instead, the list() filtering should be done on the parent side of the relationship.

 

Unfortunately, with names like TableA & TableB, I can't advise on how to rearrange your relationships to make this work.

[FredP]

Well I have 3 Tables

 

Projects

Tasks

Employees

 

I would like to be able to filter a portal to only show Projects, that have a task assigned to the employee that matches Get(AccountName) in Tasks.

 

Projects Tasks Employees

ProjectID# = Parent_PrjID#

EmployeeID# = EmployeeID#

 

Essentially, I thought I could create a LocatorKey in Table:Projects that got a list of all the Employees assigned to Tasks for that project, do a pattern count, and if pattern count is greater than 0, then that project "IsMine" and then the Keyword "My Projects" would be added to the locator key.

 

Does that information help? If i can solve this one today, it might make me feel better for banging my head all day yesterday

 

Fred

[Ender]

Can you clarify what the primary relationship is between each set of tables?

[FredP]

Sorry, I tried to do that with formatting text, and al the formatting got taken out

 

Table::Projects Related to Table::Tasks By Projects::ProjectID# = Tasks::ParentPrjID#

 

Table::Tasks is related to Table::Employees by Tasks::EmpoyeeID# = Employees::EmployeeId#

 

Does that help?

[Ender]

So your Task table is really an Employee-Project join:

 

Employee -- Project

 

 

If you want to see the current user's Projects, use get(accountname) in an unstored calc (or set by the login script in a global), then link that through an Employee TO to a Task TO (and optionally to a Project TO):

 

Interface -- Employee by Account -- Project by Account

 

 

It doesn't really matter what TO the interface layout is based on for this. Note that these are separate TOs from the primary relational structure, though "Employee by Account" is also related to "Task by Account" via the EmployeeID, and "Task by Account" is related to "Project by Account" via the ProjectID. What's different is that first step: relating to "Employee by Account" via the AccountName (yes, you'd need to store the account name in a text field in Employee).

 

An alternate method could relate the unstored get(accountname) calc directly to the Task TO, where the records are all marked with the account name instead of the EmployeeID, but this could be problematic if a user's account name changes.

 

Anyway, once you have that relational chain established, you can use that to pull a list() of the ProjectIDs for the current user:

 

List(Task by Account::ProjectID)

 

And use that in the filter calc on the parent (interface) side, something like:

 

case(FilterChoice = "My Projects"; List(Task by Account::ProjectID);

FilterChoice = "Status"; FilterValue;

FilterChoice = "Name"; FilterValue)

 

To avoid false-positives, something about the FilterChoice should be included in the result:

 

filterKey (calculation, text result) =

case(FilterChoice = "My Projects"; substitute(¶ & List(Task by Account::ProjectID); ¶; "¶MyProjects|");

FilterChoice = "Status"; "Status|" & FilterValue;

FilterChoice = "Name"; "Name|" & FilterValue)

 

Then on the child side (in Task or Project, depending on what you want to see), put an equivalent calc to build a match key with all conditions:

 

cMatchKey (calculation, text result) =

"MyProjects|" & ProjectID & ¶ &

"Status|" & Status & ¶ &

"Name|" & Name

 

 

Now the filtered portal's relationship can be established, something like:

 

Interface::filterKey = Task::cMatchKey

 

or

 

Interface::filterKey = Project::cMatchKey

 

whichever you're relating to.

[FredP]

Now, I know this is not built very secure or smart, but this is something i whipped out just to make sure i understand it better.

 

On the main layout, change the user name from Fred to Sean and you can see the tasks from the projects in the portal. I think this will work when i sit down and set it up properly.

 

Would love comments or suggestions on where to go from here.

 

 

Thanks!

Fred

[Ender]

What you have is essentially the "alternate method" that I mentioned above, though there's no need to tunnel through the Employee table for that.

 

I still recommend the structure that I outlined.

[FredP]

I thought I was doing what you recommended, hehe.. Ok well I will take another look at this later today. if by any chance you have a free moment to put together a very VERY rough example, that would help alot, as I am not having as much luck keeping it all organized in my head :confused:

[FredP]

Ya, Im trying to work through this right now, and I just cant make sense out of 2 things,

 

in the filterkey i dont know what FilterChoice is.

 

 

and the section about avoiding false positives. can that be explained?

[Ender]

Ya, Im trying to work through this right now, and I just cant make sense out of 2 things,

 

in the filterkey i dont know what FilterChoice is.

I guess that would be your "LocatorField".

 

and the section about avoiding false positives. can that be explained?

 

A false-positive is getting a match based on a selection for a different category.

 

An example would be a user selecting "Name" as the FilterChoice, and entering "Active" as the project name (which doesn't exist as a project name), but getting a match for records whose Status is "Active". Or a user entering a number as the name and getting matches of Tasks that have that EmployeeID.

 

By including the type of value with each line, the false-positives are eliminated.

[FredP]

I guess that is what I am not understanding. it seems that you are creating a new locatorkey for the interface table, so why would the CASE reference itself?

 

case(FilterChoice = "My Projects"; substitute(¶ & List(Task by Account::ProjectID); ¶; "¶MyProjects|");

FilterChoice = "Status"; "Status|" & FilterValue;

FilterChoice = "Name"; "Name|" & FilterValue)

 

In Mine, MyProject Would only show up in the list IF it had already been determined that the project was related. that is what has me confused.

[Ender]

Well, I was only working from what I understood from your description:

 

TableA:LocatorField would be something like

 

All

Projectname

ProjectStatus

 

So that you can filter a portal to show only Projects with a particular Status, a Particular name, or ALL to show them all.

This implies a user-selectable choice between the values you gave "All, "Projectname", and "ProjectStatus". So I gave you a calc to take that user selectable field (I called it FilterChoice) and from that, build the parent key.

 

If you meant something different, hopefully you can figure out a solution based on my example.

[FredP]

Ok I think I have made progress. I understand what you mean now, and think I have the Interface side working. Please see attached and let me know if that works in your opinion.

 

With user name set to Fred or Sean, and based on the filter key you select, it updates the matchfield to be what we want.

 

My specific question for you is, why if I select My Projects do i get an extra Linefeed/carriage return in the matchfield?

 

Fred

[Ender]

Looks right. Now add the filtered relationship and portal.

 

My specific question for you is, why if I select My Projects do i get an extra Linefeed/carriage return in the matchfield?

That's just an artifact from the substitute(). The extra line does not affect the relationship the field will be used in.

[FredP]

Hmm, that might need some help too.

 

Please see attachment. I am not sure what a filtered relationship could be other than what I have here.

 

But this doesnt work, because the way its set up, the MatchField is unstored, and thus cant be used in a portal relationship

 

??

[Ender]

Reset the storage option on the MatchKey, then reselect the parent key on that relationship. It works for me.

[FredP]

Wow, I think that worked. But, why and how? Hehhee. Why did the matchkey need to be reset? Why didnt it default to be indexed?

[FredP]

Thank you so much. It is starting to make sense to me. I am going to try to build this again from scratch... to try and really help this to sink in.

 

Fred

[FredP]

Ok to make matters worse, I need a way to match records that cross 4 different tables.

 

Main Interface to Projects to Tasks via UserID we have covered above, but in the same project, I need to add another which would be Main Interface To Projects to ProjectManagement via UserID.

 

IE: I need to match this record if current user matches either a user on a task or a user who is set as management on the project.

 

Whenever I try to use List(ProjManagament::UserId#) it makes that calculation field unstored, and if i use the unstored information in my matchfield, the match stops working.

 

So troublesome.

 

Fred

[Ender]

You may have missed an important point from my example above: the list() function is used on the parent side of the relationship. It doesn't matter if the key on the parent side is unstored. Only child-side keys must be stored and indexed.

[FredP]

Right. Dang.. Let me wrap my head around that.....

 

thats probably it.

[FredP]

Wow. How interesting such a simple difference can make!! That changes everything!!!!!

 

Thank you!

Fred

[aislinn]

Anyway, once you have that relational chain established, you can use that to pull a list() of the ProjectIDs for the current user:

 

List(Task by Account::ProjectID)

 

And use that in the filter calc on the parent (interface) side, something like:

 

case(FilterChoice = "My Projects"; List(Task by Account::ProjectID);

FilterChoice = "Status"; FilterValue;

FilterChoice = "Name"; FilterValue)

 

To avoid false-positives, something about the FilterChoice should be included in the result:

 

filterKey (calculation, text result) =

case(FilterChoice = "My Projects"; substitute(¶ & List(Task by Account::ProjectID); ¶; "¶MyProjects|");

FilterChoice = "Status"; "Status|" & FilterValue;

FilterChoice = "Name"; "Name|" & FilterValue)

 

Then on the child side (in Task or Project, depending on what you want to see), put an equivalent calc to build a match key with all conditions:

 

cMatchKey (calculation, text result) =

"MyProjects|" & ProjectID & ¶ &

"Status|" & Status & ¶ &

"Name|" & Name

 

 

Now the filtered portal's relationship can be established, something like:

 

Interface::filterKey = Task::cMatchKey

 

or

 

Interface::filterKey = Project::cMatchKey

 

whichever you're relating to.

Hi Ender!

 

I was wondering if you have some kind of example database of the above. I've been trying to make sense of while putting it into practice, but you've lost me somewhere down the line...

 

Guess I'm still thinking too much based on the fields I want to see in my table, not taking into account calculation fields to obtain the results I want.

 

My database has several portals that need to be filtered and I would like to make sense of the reasoning behind it before I start.

E.g. the filter calcs, where are they implemented? Table level? If so, what happens with layouts or where no filtering is required?

Hope you can help.

 

Thanks in advance!

Isabelle

[Ender]

I don't have an exampe for you, but you might be able to fix Fred's last sample.

 

Usually filtering is done by selecting a value in the interface's table. That field is linked to a data field or stored calculation in the portal's table. When the selected value matches something in the related table, it shows that related record.

 

For Fred's situation above, I was going about it from another angle: building the list of keys of related records to show based on another relationship. This makes it possible to use the same portal for filtering based on "My Projects" or on the other criteria choices.

 

The filterKey on the parent side gets built based on what the user selects. If the user selects "My Projects", the key will be a big list of the current employee's project IDs:

 

MyProjects|231

MyProjects|456

MyProjects|458

MyProjects|344

 

 

If the user selects "Status", the key will be the Status value:

 

Status|Active

 

If the user selects "Name", the key will be the Name value:

 

Name|John Smith

 

 

Since the key on the child side has the projectID, along with the Status and the Name, any of those can be used to make a match.