AnsweredAssumed Answered

A->B->C Relationship Help, Please

Question asked by Sorbsbuster on Oct 23, 2008
Latest reply on Oct 23, 2008 by Sorbsbuster


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.