A->B->C Relationship Help, Please
The advent of A->B->C relationships with FM7 certainly seemed like a real boon at the time, but I am constantly running into the same limitation, and I have a nagging feeling that it is my fault. Can anyone tell me if I'm just being plain stupid, or is there indeed a way to make these cross-table relationships work?
Example of my problem:
I have a Purchase Order Table that has one record per order line.
I have a Purchase Order Receipts Table that has a record per receipt.
A simple relationship via matching PO Number (and line number) in each table means I can calculate how much is still outstanding to be received against each Purchase Order Line - I just subtract the sum of the quantity received in the POR (via that relationship) from the quantity ordered in the PO Table.
In that Purchase Order Table I can have another simple calculation that shows the order line as 'Complete' if the quantity outstanding is zero. All easy so far.
But now I have another Stock Record Table, where I have a Stock Record for each article I stock. As well as showing the current stock level, I use it to show other info about that article, using a tabbed form. One tab has a portal list shows all of the orders for that item, to show what quantitiy is still outstanding, and when deliveries are next expected.
The problem is that for clarity I only want to show users those order lines that are still outstanding; there is no point in displaying every order I have ever placed for that item. To do that, the relationship between the Stock Record Table and the Purchase Order Table matches 'ArticleNumber -> ArticleNumber', and 'ConstantNo-> OrderLineCompleteYesNo'. Then it will only display Purchase Order Line records which are for that article, and are not complete.
Except that it doesn't, because the field 'OrderLineCompleteYesNo' is a calculation that is dependent upon a relationship with another table; it cannot be indexed, so it cannot be used in any down-the-line relationships. So, instead, I end up having to run a script regularly that calculates, batch-fashion, if the order line is complete or not and sets that value into a text field, which can be indexed and used in subsequent relationships. It has all the potential for confusion that you imagine - and more.
I think my solution is inelegant to say the least. But this is just one (of the easier to explain) example of how I try and maintain data integrity across the whole solution by constantly referencing back to the single time data was entered or calculated, but I find several times in almost every solution I design that I come up against this problem. Using the power of A->B and B->C relationships is so ubiquitous that almost inevitably I will want to use an A->C relationship which, in one of its components, uses a field that somewhere is also dependent upon a relationship, and the whole thing fails.
I am a 20-year fan of Filemaker, and a big fan of FM9, but I constantly find myself niggled that the much-touted A->B->C relationship is not just as all-powerful as suggested.
Or maybe I am indeed just being stupid? All suggestions gratefully received: sorry for the length of my question.