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

[Fenton]

There is no way to "fool" FileMaker that something is indexed when it can't be. But you could set the values into a 1 record table, then use that as the target of your value list.

For example. You have calculations that can tell you if there are still hours left for the job, probably in the table just above the hours line items. You could use that calculation to produce the Job ID ONLY if there was time left. Yes, it would be unstored. But if you flipped to a layout with only that field and used Copy All Records, you'd have the IDs. You could then go to the 1-record table and paste them into a field. That could make a value list.

You could [x] Show also the names, if the 1-record TO has a relationship to the names TO. If it's the client name, and they have multiple Jobs, you don't want to sort by the 2nd field (name), or it will remove some of the Job IDs.

An alternative is to paste it into a dedicated (and hidden) field of the 1st record of the Jobs table (with select). If you always went to the 1st record, and redid this every time someone clicked on the button to choose, there would not be a problem. It would have to run every time; but you'd want that anyway or you'd never know if it was up to date.

It has been said that related calculations become slower the more you reference other related calculations. So, try and get all the calculations into one, rather than just referencing other calculation fields. It comes to more or less the same thing, but is faster. At least it was in earlier versions of FileMaker and I've not heard different. 7 is faster though, so maybe it's an obsolete obsession.

[Fenton]

Duh. I just realized that you could just paste the gathered IDs into a global field, then Commit, and use that in a relationship back to the original records for a filtered value list (in whichever table you wanted the value list in). No 1-record table or regular field needed.

You really want to run a routine regularly to mark completely finished jobs, so that a quick Find or relationship could knock them out of the found set first. Not at the time of choice, some other time, startup, shutdown, maybe only weekly. You don't want to Find on the Unstored calculation field however. You will be copying a lot of blanks, just carriage returns, but that doesn't matter to the relationship. You could remove them with a Custom Function or Substitute. But they don't really matter in a key.