1 2 3 Previous Next 38 Replies Latest reply on Sep 22, 2016 11:04 AM by BruceRobertson

    related record issue works intermittently

    burghfan

      We two (2) tables; "Rep Orders" and "Distributor Orders" that are linked to a "Estimate" Table.

       

      From the "Estimate" table we can generate either a new "Rep" or "Distributor" order.

       

      We several scripts that allow us to move between the related records in the "Estimate Table" and the "Rep Order" or "Distributor Order" The issue we are having is that the "Go To Related Record" Scripts works correctly as long as we stay in the same two tables Example go from the "Estimates" Table" to the related "Rep Order" and back to "Estimates" is fine. If we switch to the Distributor Order" table the script for some reason takes us to the last "Estimate" record associated form a "Rep Order". The same holds true if starting out between "Distributor Order" and Estimate" then the issue is that the "Rep Order" goes to the last "Estate" associated with the "Distributor Order"

       

      Our Relationships look as follows

       

      Estimate Table

      Primary Key: ID_Estimate

      Child Key: id_rep_order

      Child Key: id_distr_order

       

      Rep Order Table

      Primary Key: ID_RepOrder

      Child Key: id_rep_estimate

       

      Distributor Order Table

      Primary Key: ID_DistrOrder

      Child Key: id_distr_estimate

       

      Relationship Table Occurrences

       

      Estimate to Rep Order by Estimate_ID

      ID_Estimate = id_rep_estimate

       

      Estimate to Distributor Order by Estimate_ID

      ID_Estimate = id_distr_estimate

       

      Estimate to Rep Order by Order_ID

      id_rep_order = ID_RepOrder

       

      Estimate to Distributor Order by Order_ID

      id_distr_order = ID_Distr_Order

       

      Scripts are as follows:

       

      From Distributor Order back to Estimate

      Screen Shot 2016-09-19 at 3.37.56 PM.png

      From Estimate to Distributor Order

      Screen Shot 2016-09-19 at 3.39.40 PM.png

       

      The 2 Scripts for the Rep Order and Estimate look the same with the corresponding fields in the script steps

        • 1. Re: related record issue works intermittently
          Johan Hedman

          It most probably have to do with your Table Occurrence for your Layout not being in the same Table Occurrence Group (TOG). You should have one TOG for each function, but in your case if you dont have more relationships then between your three tables, you make sure they all are linked together

           

          Unknown.jpeg

          1 of 1 people found this helpful
          • 2. Re: related record issue works intermittently
            dtcgnet

            Couple of things...

             

            First, in general, it is considered bad practice to use Copy and Paste as the scripts do. If a user has copied something important to his clipboard, and without warning, YOU copy something, then the user has lost something he may not have wanted to use. Avoid copy and paste. Instead:

             

            Set Variable [ $DistributorOrder ; Value: DOD_DISTRIBUTOR-ORDER::ID_Distributor_order ]

            Then you navigate to the record you want to use and you use:

            Set Field [ T08_ESTIMATES::id_distr_order ; $DistributorOrder ]

             

            Use Set Variable and Set Field instead.

             

            That being said...you're not using Go to Related Record. You're using Go to Layout. Switch to Go to Related Record, and use the option to match This Record Only.

             

            Also, whenever you go to a different layout, FileMaker will automatically refresh the window, so you don't need Refresh Window statements.

             

            One last thing...your line 7 commands, "Perform Find/Replace" are not actually doing anything, I'm guessing. They should be removed.

             

            Hope that helps.

            1 of 1 people found this helpful
            • 3. Re: related record issue works intermittently
              burghfan

              I have a four Table Occurrences;

               

              1. Estimate Table to Rep Order Table: (ID_Estimate = id_rep_estimate)

              2. Estimate Table to Distributor Order Table: (ID_Estimate = id_distr_estimate)

              3. Estimate Table to Rep Order Table: (id_rep_order = ID_RepOrder)

              4. Estimate Table  to Rep Order Table:  (id_distr_order = ID_DistrOrder)

               

              See Below

               

              Screen Shot 2016-09-19 at 4.46.12 PM.png

               

              Are saying I need Table Occurrences from the "Rep Order" and "Distributor Order" tables to the ""Estimates" tables?

              1 of 1 people found this helpful
              • 4. Re: related record issue works intermittently
                Johan Hedman

                Have you based your layouts on those four table occurrences on the right side of you graph?

                1 of 1 people found this helpful
                • 5. Re: related record issue works intermittently
                  burghfan

                  Thank you i'll re-work based on your suggestion, and see what happens.

                  1 of 1 people found this helpful
                  • 6. Re: related record issue works intermittently
                    burghfan

                    no from the table on the left

                    1 of 1 people found this helpful
                    • 7. Re: related record issue works intermittently
                      burghfan

                      Now I am truly confused. I can't seem to get this at all.

                       

                      Do I need more table occurrences or a join table?

                       

                      Based on my earlier scripts it would seem I am almost there. I have gone back to those for now after removing the refresh window and find/repalce

                      1 of 1 people found this helpful
                      • 8. Re: related record issue works intermittently
                        dtcgnet

                        You do not need any more TO's.

                         

                        What is the name of the layout that you are on? Also, is there a portal on that layout? Two portals?

                         

                        Let's start there.

                        1 of 1 people found this helpful
                        • 9. Re: related record issue works intermittently
                          dtcgnet

                          A screen shot of your layout in layout mode would be very helpful.

                           

                          Here's why Go to Layout isn't working for you:

                          I'm on Layout A, based on Table A. I'm looking at record 3. If I switch to Layout B, ALSO based on Table A, then the found set will remain the same, and I'll be on record 3 when I get to the layout.

                           

                          HOWEVER, if I'm on Layout A, based on Table A, looking at record 3, and I Go to Layout B, based on Table B, then the found set I'm looking at will have absolutely nothing to do with Layout A or the records that I was viewing while I was on Layout A. Go to Layout simply switches me to another layout. I could land on record 4 or 18 or 1000 or 1. Whatever was in the found set before I got there is STILL in the found set. If it's my first time on the new layout, then I'll land on Record 1. If I navigate to record 10 on Layout B, then I go to Layout A and back...I'll STILL be on record 10 when I get back to Layout B.

                          1 of 1 people found this helpful
                          • 10. Re: related record issue works intermittently
                            burghfan

                            Below are screen shots from the three layouts/tables I am referring to.

                             

                            Estimate has a portal

                             

                            Rep Order

                             

                            Rep Order.png

                             

                            Distributor Order

                             

                            Distributor Order.png

                             

                            Estimate

                             

                            Estimate.png

                            1 of 1 people found this helpful
                            • 11. Re: related record issue works intermittently
                              dtcgnet

                              Thank you for the shots.

                               

                              The way I would do it is something like this script. I'd have a button that I've added to the portal row. In layout mode, you add one button and it'll show up on every row. The button will run the following script:

                               

                              Set Variable [ $ThingYouWantToSet ; Value: DOD_DISTRIBUTOR_ORDER::SomeImportantField ]

                              Set Variable [ $DistributorOrderID ; Value: DOD_DISTRIBUTOR_ORDER::ID_DistrOrder ]

                              Enter Find Mode

                              Go to Layout [ "Estimates" ]

                              Set Field [ T08_ESTIMATES::id_distr_order ; $DistributorOrderID ]

                              Perform Find

                               

                              Those five statements do this: When you click the button in a portal row, FileMaker knows which record you're clicking on, so it sets a variable so that you can find that particular distributor order. ID_DistrOrder should be a value that applies to one and only one distributor order, and now you have its ID in the variable called $DistributorOrderID (you can call the variable whatever you want). After it knows that unique ID, it goes to the Estimates layout, and it finds that particular order.

                               

                              Now then...what do you want to do after that? You don't want to change the ID. You want to change something or set something. Notice how I've set a variable called "$ThingYouWantToSet", which was a value from DOD_Distributor_Order? After you've found the specific record, then:

                              Set Field [ T08_ESTIMATES::SomeImportantField ; Value: $ThingYouWantToSet ]

                              Go to Layout [ "WhateverLayoutYouStartedOn" ]

                               

                              In English...Grab the ID of the row you clicked on. Go to a layout based on the table you're interested in, and use the ID to find the particular record you're interested in. Set whatever fields you want to whatever variables you've set. Go back to the original layout.

                               

                              Keep this in mind: when you go to a layout, you've done one thing: you've gone to a layout. You have no idea if you're on the right record, or if the right record exists, or anything else. Go to Layout means go to a layout, and that is ALL it means. If you need to find a specific record to act on, then find it and do what you need.

                               

                              Hope that helps. You'll get there.

                              1 of 1 people found this helpful
                              • 12. Re: related record issue works intermittently
                                Johan Hedman

                                Yes, but you have several layouts and to be able to get from one layout to another through relationships they need to be related

                                 

                                Layout Estimation should be on Table Occurrence T08_Estimates

                                 

                                Layout Distribution Order should be on Table Occurrence DISTRIBUTOR_ORDER to estimate by Order ID

                                 

                                and so on. There need to be a relationship between Estimates and Distributor that you can use

                                1 of 1 people found this helpful
                                • 13. Re: related record issue works intermittently
                                  burghfan

                                  Table Occurrences as follows;

                                   

                                  All three of these layouts have unique table occurrences with a Primary Key Field.

                                   

                                  Distributor Order TO with with DistributorOrder_ID

                                  Rep Order TO with RepOrder_ID

                                  Estimate TO with Estimates_ID

                                   

                                  I have a script that runs from the "Estimates" table and can generate a new Rep Order and another that generates a new Distributor Order.

                                   

                                  What I am looking to price is to have the ability to go from the generated "Rep Order" or "Distributor Order" back to the "Estimate" that generated it. Each "Rep Order", "Distributor Order" and "Estimate" have a unique "Key Field"

                                   

                                  Obviously I have found that using "Go to Layout" does not work. I have tried to use "Go to Related Record" but i think I may need either a "join" table occurrence" of another relationship.

                                   

                                  I also tried "Set Variable" and "Set Field" versus copy and paste. At this point I can hardly remember which method gave me which results!

                                   

                                  Screen Shot 2016-09-20 at 6.13.42 AM.png

                                   

                                  The top two TO's are  from "Distributor Order"

                                  The bottom two TO's are from  "Rep Order"

                                   

                                  My description below is probably not technically correct in the wording.

                                   

                                  These allow me to have;

                                   

                                  A field on the "Distributor Order" with the primary key field from "Estimates"

                                  A field on the "Rep Order" with the primary key field from "Estimates"

                                  "Estimates" has a field for each of primary key's from "Rep Order" & "Distributor Order"

                                   

                                  Those fields can be seen in the Screen Shots posted a few comments above. They have buttons to the left of them that are labeled in red; Rep Order, Dis Order and Estimate ID

                                  1 of 1 people found this helpful
                                  • 14. Re: related record issue works intermittently
                                    burghfan

                                    The Portal brings in data from a "estimate line items" table

                                    1 of 1 people found this helpful
                                    1 2 3 Previous Next