Creating a Value List from Complex Relationships

[Going Boldly]

I am working on a time tracking and billing system for my company in FM7. Can anyone help with this:

Projects are contained in a Projects table. Projects can have Status=Open or Status=Closed. Each project is broken down into components contained in a Components table. Each component can have one or more staff members working on it. I have a Staff table for staff. Components, Projects, and Staff are linked in a linking table which I call BillingUnits (eg: Project ABC, Component XYZ, Staff LMN).

Here's the problem: When someone enters their time into an Hours table they need to choose a "BillingUnit" ID to allocate their time to. I want to create a Value List that contains only BillingUnits related to open projects AND related only to one staff member. I have only been able to create a value list that contains either billing units related to open projects OR billing units related to one staff member, but not the intersection of both of these - in other words the billing units related to one staff member AND related to open projects.

Staff enter their time in a layout based on the Staff table which has on it a portal based on the Hours table so that staff can create records in the Hours table from their staff page.

Going crazy - have spent hours on this and have consulted with our senior programmer (not familiar with Filemaker - but a programming genius) and I still can't figure it out. It should be possible shouldn't it?

[Wim Decorte]

BillingUnits is related to Components through a componentID, Components is related to Projects through a Project ID.

What you need is second occurance of Projects and a second occurance of Components and a multi-predicament relationshop between those 2 table occurences.

Components2 to Projects2

ProjID = ProjID

AND globalStatus = status

where globalstatus is a global text field with the value "open"

You now need a new relationship from BillingUnits to Components2 based on componentID. Now make a value list with related values based on those occurances and you should get only open projects.

This may need some tweaking since I'm doing this off the top of my head but it should get you going...

(to use meaningful names you could name Components2 'openComponents' and Projects2 'openProjects')

[transpower]

Wlm: You mean "multi-predicate" or "multi-criteria," not "multi-predicament."

[Going Boldly]

OK -- I've gotten that far, but now how do I see ONLY the BillingUnits belonging to one particular staff member in a Value List. When I link StaffID from the Staff table to StaffID in the BillingUnits_Open table I don't get the expected results.

[Wim Decorte]

Time to get you out of this predicament

(File is attached)

We're looking at from the Hours table, right? There we have a staffID (the chosen staff guy). that StaffID in hours relates to staffID in a TO of BU (named 'openBUs'). BU has a calc field that concatenates ProjectID / CompID / StaffID.

OpenBU is related to a TO of Components named 'openComponents' through compID. openComponents is related to a TO of Projects through a rel with 2 predicates: the global field "open" to the status of the project, and projectID.

Now for the tricky part. We define a value list showing the values of the concat calculation in openBU (choose the correct TO here!). But at the bottom of that dialog we ask it to show only related values starting from 'openProjects'. If you look at the relational diagram you'll see it makes sense. Now back in the hours table, format a field named "chosenBU" with the value list, make sure that there is a value in the staffID field (because that's the trigger for the chain of related TO's) and you'll get the list of open BU for that staff member.

testFMforums.zip