Using List calculation as Match field on FM7 Server

[mickeyfinn]

I have a db where I completed the initial build in FM9 Advanced. I have to publish it on FM7 Server Advanced (which is not ideal, but I don't have any other option) and had set up a portal based on a relationship with a match field to a list function calculation (which lists multiple keys so I can have a many to many relationship). This works well in FM9, but though the List function still works on FM7 Server, the relationship fails. Is there an alternate FM7-friendly solution?

To go into more detail, I have three related tables, Pages (parent), Services (child) and Tasks(g-child). Each page can contain many services and a service can be attributed to multiple pages (via a related Service Items table), each service contains multiple tasks, and a task can be attributed to multiple services. There may be a simpler way of doing this with an additional table (e.g. Task Items), and I'd really like to not have to script building more related records if I can avoid it.

Thanks in advance for all replies.

[bcooney]

Why not create the join btw Services and Tasks? And a multi-key would need to be stored to be on the bottom half of a relationship. So, it's not surprising that List() fails.

[mickeyfinn]

Sorry if I'm not picking up your meaning, I'm not as competent with Filemaker as I would like to be. I already have a join between services and tasks by a standard one-to-many key join. Perhaps it is easier to explain what I want to do. I have users assigning services to pages through a portal. Since services are made up of one or more tasks, I'd like to see the associated tasks for all services listed in the portal below.

In the interface, I have the users viewing from a grandparent table layout (Ticket). I use a drop down list of related pages to switch between pages of the ticket. This works well for services - each time I choose a different page in the drop-down, I see the associated services for that page. But when I want to see all the associated tasks for all services for the current page, the only way I could make that work was with the List calculation. And it works in FM9, just not via FM7 server.

Have I overcomplicated things? Is there an existing relationship between Pages and Tasks that can do this? Also, not sure what you mean by "bottom half" of the relationship. Do you mean that the List calc is in the Task table listing related service keys? Apologies, I do not have access to the db till Monday so I cannot experiment with it until then.

[comment]

I'm not able to follow your explanations that well. In any case, in version 7 you can - to some extent - emulate the List() function by defining a value list of related records only, and using ValueListItems() to get the list.

[bcooney]

In the interface, I have the users viewing from a grandparent table layout (Ticket). I use a drop down list of related pages to switch between pages of the ticket.

Each Ticket is assigned many Pages? Do you have a portal of Pages on the Ticket form using a join table btw Tickets and Pages? How is this drop-down working?

This works well for services - each time I choose a different page in the drop-down, I see the associated services for that page. But when I want to see all the associated tasks for all services for the current page, the only way I could make that work was with the List calculation.

I picture on the Ticket form layout, 3 portals.

1. Related Pages for this Ticket, based on join table btw Tickets and Pages.

2. Pages have Services. So, if you select a Page in the first portal, store its ID in a global, you can relate to Services using gPageID to the join btw Services and Pages.

3. Click the ServiceID in the second portal, store its ID in gServiceID, and use a relationship to see Tasks in this third portal.

Sorta like the Finder does cascading lists.

And it works in FM9, just not via FM7 server.

Well, you should upgrade to FMS9, at least. But, it's difficult to believe that it's the Server version defeating this interface.

[mickeyfinn]

Thanks for the fast responses, guys. I realize I'm not describing things so well, though bcooney, you are correct in your assessment of how I have this set up, other than the global field storing the active portal record key. I have never found a way to do this, so if you can tell me what the calculation of the global field needs to be that would help my interface immensely. I have tried to do this in the past and was unsuccessful. Presently, the user switches between pages using a value list of related page IDs.

The only other difference to how you describe things is that I want the Tasks portal to show all related tasks for that page, so the list does not change when the user highlights different services in the Services portal. This is why I used the List function, because each task can be related to more than one page, depending on the services that have been selected for it.

Comment, I tried the ValueListItems function and though it correctly stores the ServiceIDs in a list, as soon as I try a relationship from that list, it will not work on FM7 server. It does work in FM9. I was hoping for a way to do something similar that was FM7 compatible.

[bcooney]

Why do you keep mentioning FM Server 7?

Alrighty, I took a shot at what I think you're trying to do. Have a look at the attached demo.

Cascade.fp7.zip

Edited February 2, 2009 by Guest

[mickeyfinn]

The Cascade example is great. Thanks very much for doing that. I noticed that you use join tables instead of relating each table directly, something I have not done before (unless you count a Line Items table). What are the advantages of this?

As far as the original issue, I am no further to finding a workaround, though I did some more testing. Seems that if I base the relationship between Pages and Tasks on any calculation involving one result (e.g. just the basic Services~Pages::Related Service which shows the first result of this relationship only - no list)the relationship is successful. Likewise, the relationship works if I simply base on a text field and manually type the list of related service keys. However, as soon as I try to base the relationship on any calculation which results a value list, it fails.

I was curious about what you were saying about the List calculation:

And a multi-key would need to be stored to be on the bottom half of a relationship. So, it's not surprising that List() fails.

Is there another way to build this?

To answer your question as to why I keep mentioning FM Server 7, it's really that it was so frustrating to build a working solution in my FM9 client, only to have it fail as soon as I share via the server. That would be a life lesson I suppose! Always test on the platform you mean to deploy on.

[comment]

The thing that I find most confusing is this: the List() function returns a list of related values. For this, a relationship must be in place. A second relationship, based on the result of List(), is going to link to the SAME records that are already related by the first relationship. So what's the point?

That said, ValueListItems() should work as a workaround. Make sure that the calculation is unstored and that the result is set to type Text. And that your application is updated with the latest patches.

[mickeyfinn]

Comment, I realized from what you were saying that my original concern that I was over-complicating things was correct. As you say, the relationship must already exist, if the List function works. As it happens, I had built a TOG with Pages linked to services, and then had a separate Table Occurrence of Tasks which I was trying to link directly to Pages rather than via the Services TO. I should have seen that sooner, but I guess I was getting a little tunnel vision.

So I have made the relationship work, without using the calc as the match field. Still, it should have worked, even if I did build it inefficiently. I do have the latest patches installed to the FM7 server, and I did have the ValueListItems calc field set up as you describe.

[comment]

Well, I don't know. This file works in FMP7 - does it work for you?

ValueListItems.fp7.zip

[mickeyfinn]

Sorry I haven't replied quicker. I've been busy at work. I will post a reply once I get chance to check your file tomorrow.