Relationship Based on Unindexed Match Field

[atrautman]

Hello Experts,

I'm stumped. Working in FMP7 on Mac OS X. Bear with me...

SHORT VERSION:

I would like to create a value list showing only related records, but the underlying relationship needs to be based on a calculation field which references related fields, and so is not indexed. An unindexed field (correct me if I'm wrong) cannot be used as a match field for a relationship. Is there a workaround to create this value list so that it shows only field values from a subset of records in a table?

DETAILS:

In my business, I sell hours of work to my clients. I have contractors who perform the work and bill me by the hour. Each pairing of client and contractor is a Job. I keep track of the hours remaining on each Job by summing up the hours sold on all sales of that job, and then subtracting the number of hours used on that job and invoiced by the contractor. The database is available to my contractors via IWP for them to submit their invoices directly.

Occasionally, a contractor will invoice me for a job with no remaining hours. (They sometimes "get ahead" of the number of hours purchased by the client.) I would like to prevent this. So what I've done is to design a more or less standard invoice layout containing a portal to line items. Each line item has a popup field containing Job numbers. I would like to define the value list upon which this popup list is based so that only Jobs that have more than zero hours remaining show up. This is where I run into a problem.

I can define a multi-criteria relationship between the Invoice table and the Jobs table this way: first I match the contractor IDs in both tables so that only Jobs assigned to the current contractor display. Fine. But now I want to screen out Jobs with zero hours, so I add a global calculation field in the Invoice table, set to equal zero, and relate that to the Jobs table such that the Hours Remaining are greater than zero. The problem is that the Hours Remaining field is a calculation that is based on related fields (in the Line Items and Sales tables), and so it is not stored and, therefore, unindexed. As far as I can tell, a relationship match field needs to be indexed.

Sooo... Is there a way to define a relationship (or otherwise define the value list directly) so that the value list will show only Jobs where the Hours Remaining is greater than zero?

(Does this make any sense?)

Thanks all,

Allan

[BobWeaver]

If the unindexed field is on the left hand side of the relationship, it will work. If not, you could change the field from a calculation to a regular field with an auto-enter calculation option. This can be indexed.