4 Replies Latest reply on Oct 23, 2008 3:20 PM by Sorbsbuster

    A->B->C Relationship Help, Please



      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.

        • 1. Re: A->B->C Relationship Help, Please



          First of all, don't feel stupid!  Everyone is here for the same reason.... That is, to learn more about FileMaker Pro through questions and discussions.


          The relationship between Stock Record and Purchase Order is through ArticleNumber, and that is straight forward.


          In the Purchase Order Table, is there a field that shows the difference between Quantity Ordered and Quantity Shipped?  If so, then use it to find those Stock Records with an outstanding order.  That is, in your Stock Record Table, perform a find on the Purchase Order Table (through a portal) for that field having a value greater than zero.


          I'm sure there is more to it than that, since this doesn't need a third table.  If you can provide a real-life example, it may help with my understanding of the problem.


          Sorry if I'm stupid.  :-) 



          FileMaker, Inc. 

          • 2. Re: A->B->C Relationship Help, Please

            Thanks for the swift feedback, TSGal!


            Yes, the Purchase Order Table has a calculated field showing the difference between Quantity Ordered and Quantity Shipped - I use it to display a subsequent calculated field 'OrderCompleteYesNo'.  It displays 'Yes', say, if the quantity outstanding is zero.


            Then I can have a two-criteria relationship from the Stock Record Table to the Purchase Order Table:

            - ArticleNumber = ArticleNumber


            - ConstantNo = OrderCompleteYesNo


            The problem is that that relationship won't work, as the  'OrderCompleteYesNo' field is dependent, ultimately, upon a calculation by relationship:


            QuantityOrdered - 'Sum (PurchaseOrderReceipts::QuantityReceived ).


            Because something in its lineage is relationship-dependent, nothing downstream of that calculation can ever be used in a relationship.


            To re-cap on the tables:


            Purchase Order Receipts has many records of receipts against one Purchase Order Table record.

            The two are joined by a relationship PurchaseOrderNumber = PurchaseOrderNumber  (I've ignored the issue of PO LIne numbers for clarity.)


            Stock Record Table is joined to the Purchase Order Table by ArticleNumber = ArticleNumber simplistically, but that would show all order for that article, ever.  I only want to display order that are live, with an outstanding balance still to be received.  Adding that criterion into the relationship makes it fall over, as it cannot be indexed.


            Like I said, in order to avoid using scripts to set fields, or lookups at the point of data entry to copy data from one table to another, I keep trying to follow guidelines on data integrity and constantly refer back to data from another Table if that is the true data source.  But I keep coming across this problem, and have to regularly resort to lookups and user-triggered 'Set Field' scripts, to copy 'live' data into a trapped Number field, say, that can be indexed and therefore used in relationships - with all the encumbent potential for error.  (Although plug-ins like EventScript help.  A bit.)


            Thanks again for your help; I really appreciate it.



            • 3. Re: A->B->C Relationship Help, Please



              You could use a script triggering plug-in in your calculation field to set your completed order field to "Yes". Like zippscript for instance. BTW, most of these script triggering plug-in's are free and quite usefull.


              Let ( [

              qtyremains = OrderedQty - receivings:ReceivedQty;

              complete = Case ( qtyremains = 0; 1; 0)


              If (complete; zippScript_PerformScript( Get ( FileName ); ScriptName ); qtyremains )



              That way the value in the completed order field is a simple text value and indexible for a relationship.




              • 4. Re: A->B->C Relationship Help, Please

                Thanks Tim,


                You've (both) confirmed what I thought: that the only way to make it work is to 'copy' the field-via-relationship data into an indexable field.  I've got round it sometimes using the LookUp facility, and sometimes using a batch-processing script (usually tagged on to the end of some other routines that I can depend on being run frequently), and sometimes using EventScript as a trigger.  I came across zippscript a while back and circulated it to friends that I'd introduced to EventScript, but I haven't got round to using it myself yet.


                Thanks again for the help.