9 Replies Latest reply on Feb 4, 2015 6:36 AM by Mike_Mitchell

    Relationships

    andyk3005

      Filemaker is such a frustrating tool.  When you think you have found the logical way to work it confounds you.   I have a straightforward task to create Work instructions for the shop floor (File WO)  which is generated from a Sales Order, a Sales Order Line Item and a Product Quality Specification.  I have created the relationship shown (fig 1).   Using a form based on the file WO with Merge fields or claculated fields based on the three source files in the relationship I expected these to be populated when a primary key field was entered.   This works partially as the other two link fields are immediately displayed with one other linked field (fig 2).  This state remains on movig from the record and returning or applying a refresh script.   However if i go to manage database do nothing but click OK and return to the created record it displays the information I expected (fig 3).

       

      I would appreciate any help in understanding and resolving this.  Thanks in anticipation.   In the meantime i will return to trying other ways.

       

      Relationship.png

      Fig1 - Relationship Graph

       

      Inital Creation.png

      Figure 2 - Initial record with three link fields populated and one other field sales price from the Sales Items file.

       

      After OK in Database Management.png

      Fig 3 - Fields correctly populated after visit to data base.  Date required should be populated but only on creation which is why it is balnk.

        • 1. Re: Relationships
          andyk3005

          OK guys.  I think I have solved the issue.  Entering a link field does not automatically execute through the other links although the fields are correctly populated.  You have to enter the three link values separately and each link executes on entry.  Thanks for viewing my predicament.

          • 2. Re: Relationships
            Mike_Mitchell

            We really don't have enough information to diagnose the issue. I'm going to assume, based on your description and the Graph, that WO::Item Number and WO::Order Number are unstored calculations based on another TO. If that's the case, you can probably resolve the issue by using either a lookup or an auto-enter calculation instead.

             

            The reason this isn't working is that FileMaker does not evaluate relational joins based on unstored calculations right away (for performance reasons).

             

            HTH

             

            Mike

             

            P.S. The fact you're using a merge field doesn't really matter. A merge field is simply a way of displaying the field on the layout. Doesn't have anything to do with how it's evaluated.

            • 3. Re: Relationships
              andyk3005

              Thanks for the reply Mike.  I now understand that unstored calculations do not automatically trigger a relationship which uses that field although I would question the performance aspect as they must be triggered on the completed record since it does not revert after time.

                

              • 4. Re: Relationships
                Mike_Mitchell

                They show up on your machine. They won't show up on other users' machines until after a cache flush to disk. That's where the performance issue comes in.

                • 5. Re: Relationships
                  wimdecorte

                  andyk3005 wrote:

                  I would question the performance aspect as they must be triggered on the completed record since it does not revert after time.


                  That is a weird sentence that I'm not sure I understand. The performance angle is very real though. It's too easy in FM to create an unstored calculation that depends on other unstored calculations that depend on other unstored calculations. If FM were to update those automatically all the time (and note that you have very little control over when unstored calculations are updated) then the whole system would crawl to a halt.


                  So think very carefully whether there is not a better approach to get and set the data statically before you use an unstored calculation.  

                  • 6. Re: Relationships
                    andyk3005

                    Hi Wim,

                    I can fully understand cascading links based on unstored calculations would be detrimental, but surely that is for the developer to avoid.

                     

                    The issue i was dealing with did relate to unstored calculations.  The sales item from which Work Instructions have to be generated has three key fields, the Order Number relating to the Sales Order Header, the Item Code of the product to be manufactured and the Line Number, a serial relating to itself.  I expected on entering the Line Number the in the WO layout that the Order Number and the Item number returned by calculation would automatically link to the Sales Order Header and the Product Specification.  This did not happen on creation, however when the links were somehow forced by accessing the database manager the full data appeared.  As these were unstored calculation fields every time i scrolled through existing records all three relationships must be refreshed.  My point therefore was, what is the performance difference between creating the record which did not perform the automatic links to re-selecting the record which did perform the links.  

                     

                    Anyway the creation process which is script activated now works perfectly well.  Thanks for the help

                    • 7. Re: Relationships
                      andyk3005

                      Hi Mike,

                       

                      I can understand that.  Is there a script step to force a flush?

                      • 9. Re: Relationships
                        Mike_Mitchell

                        Not exactly. That will refresh the screen and the join results, so it will solve the immediate problem of seeing the related records. However, in order to save the data to disk, you'll need to use Flush Cache to Disk. Performs a slightly different function (as we discovered recently here: Commit Records/Requests vs. Refresh Window ... ).