12 Replies Latest reply on Nov 12, 2015 2:36 PM by Razor

    Adding an AND clause in the Relationship Manager

    Razor

      Hello, a quick introduction, I previously worked with MS Access for years and new to FileMaker.  In the past I set most of my properties through VBA.  I'm, having a problem grasping the concept of adding an “and” clause with the relationship viewer.

       

      Lets say I have two tables, PartMaster and PackageInstruction.  I want to return a list of “current” packaging instructions to a portal.  I say “current” because rather than literally deleting or changing a record, I place a timestamp in a DELETE column to hide a record that no longer applies.  We sometimes revert a process and rather than recreating records I can go in and just simply remove the timestamp to make the record “active” again.

       

      My SQL statements in Access generally looked like this;

       

      SELECT PackageStep, SortOrder FROM tblPackageInstruction WHERE PartMasterID = fPartMasterID AND LEN(DeleteTimestamp) = 0 ORDERBY SortOrder

       

      I have the equivalent of this working;

       

      SELECT PackageStep FROM tblPackageInstruction WHERE PartMasterID = fPartMasterID

       

      but can’t figure out the;

       

      AND LEN(DeleteTimestamp) = 0 ORDERBY SortOrder

       

      How do I do this with a relationship?  Or is there a better way of attacking this problem through scripting (which I would prefer).

       

      Thanks!

        • 1. Re: Adding an AND clause in the Relationship Manager
          beverly

          The relationship graph does not have CONSTANTS.

           

          You can't "match" on Length(). You must have relationship and then do a FIND in the view of the data on a layout.

          the symbol "=" when entered in find mode (no quotes) will find "empty" in that field.

           

          This can be manual and this can be scripted. Perhaps if you need it "automated", you can have a layout script trigger to perform the find. I tend to have a button (and maybe global fields and/or drop-downs) to use to script the find.

           

          The SORTS (ORDER BY) are also a part of the process that may or may not be a part of the relationship. I prefer to not use sorts in the RG (relationship graph), but rather script them (and allow users to change the sorts).

          • 2. Re: Adding an AND clause in the Relationship Manager
            Razor

            Ok, I think I understand.  So then you would set your table relationship; (graphically speaking tblPartMaster.PartMasterID = tblPackageInstruction.fPartMasterID) then run a find script with an "on load" event for the portal using the relationship?  I don't know how exactly to do that but I'll look in to that if that is the right idea.

            • 3. Re: Adding an AND clause in the Relationship Manager
              Mike_Mitchell

              You can do something like this on the Graph, but it requires a bit of a paradigm shift. As Beverly noted, the Graph doesn't have constants per se. Nor can you match on functions, like Length. However, you can mimic such functionality. It usually requires adding a field or two to the table in question.

               

              Depending on whether you just want something for display or if you need to be able to address it at the schema level, you can use global fields (fields with global storage) and set them with scripts, as suggested by Beverly (a very good solution, since it doesn't add to bandwidth overhead), or put actual calculation fields in the table. The trick is in setting the fields and defining the relationships.

               

              In your particular case, you're looking for an empty (length = 0) DeleteTimestamp. That's basically the same as DeleteTimestamp < CreateTimestamp (assuming you have such an animal in your table). The < operator is available in the Relationships Graph, so you could just add an additional predicate to your relationship, like this:

               

              relationship.png

               

              As far as the sorting is concerned, you can do that either in the portal or in the relationship. As Beverly points out, the relationship is usually the poorer choice, since it will affect every call to the related table instead of just when you display the portal. This will be a performance hit, and, if you don't need the relationship to sort, it's unnecessary.

               

              HTH

               

              Mike

              • 4. Re: Adding an AND clause in the Relationship Manager
                beverly

                I shied away from this age-old hack (we've been doing this for a long time, and I still use it!). Mostly I didn't add this method to my reply because we have ExecuteSQL() function which does allow constants! But you can't use that in the RG, either. I guess that's why the Idea has been posted:

                https://community.filemaker.com/ideas/1055#comment-8369

                beverly

                • 5. Re: Adding an AND clause in the Relationship Manager
                  Mike_Mitchell

                  I agree that some improvements to the Graph would be nice. Especially considering the performance advantage it has over ExecuteSQL and scripting. But hey, whatever works.   

                  • 6. Re: Adding an AND clause in the Relationship Manager
                    Razor

                    For whatever reason Mike whenever I tried the "AND CreateTimestamp > DeletedTimestamp" I received 0 records.  I experimented several different ways, even manipulating my data and trying  "AND CreateTimestamp = DeletedTimestamp" just to see if that would work, and it didn't.  Not sure why but it did't seem to like the AND clause.

                     

                    Although I didn't do it how I would like to have, I solved my problem.  I ended up using the Filter Portal Records option and applied a IsEmpty ( DeltedTimestamp).

                     

                    I'm use to a programatic solution but I guess this was easy enough.  Maybe I'm thinking harder than I need to while working in FileMaker?

                    • 7. Re: Adding an AND clause in the Relationship Manager
                      Mike_Mitchell

                      Razor wrote:

                       

                      For whatever reason Mike whenever I tried the "AND CreateTimestamp > DeletedTimestamp" I received 0 records.  I experimented several different ways, even manipulating my data and trying  "AND CreateTimestamp = DeletedTimestamp" just to see if that would work, and it didn't.  Not sure why but it did't seem to like the AND clause.

                       

                       

                      You did this in the Relationships Graph, in the dialog where it shows the relationship?

                       

                      Screen Shot 2015-11-10 at 5.52.21 PM.png

                       

                      And the CreateTimestamp did have a value in it, right?

                       

                      I'm use to a programatic solution but I guess this was easy enough.  Maybe I'm thinking harder than I need to while working in FileMaker?

                       

                      Well, that's certainly possible.      More likely, it's just a different paradigm. The Graph is similar to both a querying tool and an ERD in one place. So you wind up with a composite tool that performs multiple functions. It's useful to think of it as "stepping stones" in the data model, such that when you're standing on one Table Occurrence (TO), you're taking a view into the related TOs. Along the way, you have what I like to call an "implied found set" - essentially, a filter of the records that match the relationship criteria. This applies across as many "hops" through the Graph as you take.

                       

                      So if you can wrap your brain around that concept, it'll begin to make more sense.

                      • 8. Re: Adding an AND clause in the Relationship Manager
                        Razor

                        Mike, I recreated this from scratch to "protect the innocent" and to make sure I wasn't doing something wrong.  To test that I have the basics correct here are the results without the AND clause (the ABC123 is my "Part Number");

                         

                        Screen Shot 2015-11-11 at 1.50.43 PM.png

                         

                        I then experimented the following ways;

                        Screen Shot 2015-11-11 at 1.32.01 PM.png

                        Screen Shot 2015-11-11 at 1.32.42 PM.png

                        Screen Shot 2015-11-11 at 1.33.18 PM.png

                        And in each case these were my results on the form;

                        Screen Shot 2015-11-11 at 1.52.21 PM.png

                        Am I doing something wrong?  Is this a bug?  If I filter the portal with IsEmpty ( PackageInstructions::DeleteTimestamp ) I get what I expect to see;

                        Screen Shot 2015-11-11 at 1.50.22 PM.png

                        • 9. Re: Adding an AND clause in the Relationship Manager
                          Mike_Mitchell

                          It's working for me. (See attached.) Something else is going on with your setup.

                           

                          Are you sure all timestamp fields are of type "timestamp"?

                           

                          fieldType.png

                          • 10. Re: Adding an AND clause in the Relationship Manager
                            Razor

                            Mike, I verified they are both TimeStamp fields.  I must have had something funky happening though because I then deleted them and recreated them.  Now everything is working as I expected it to.*  Thanks for the help!

                            Screen Shot 2015-11-11 at 3.45.40 PM.png

                            * I added a deleted date earlier than my parent creation date just to test everything was working.

                            • 11. Re: Adding an AND clause in the Relationship Manager
                              Mike_Mitchell

                              Great!

                               

                              Just as an aside: This method works fairly well until the data load gets substantially high (hundreds of thousands of records in the child table). The reason is that FileMaker's indexes are sparse bitmaps, so comparison operators have to walk the entire index to do the comparison (as opposed to an equality, which is a simple yes / no). If you find this causing problems, there's an alternative.

                               

                              Create a calculation field in the child table like this:

                               

                              Case ( IsEmpty ( DeleteTimestamp ) ; parentRecordID ; "" )

                               

                              Add that as an AND predicate to the relationship instead of the timestamp comparison. This will essentially cause it to match on the recordID in cases where the timestamp is null.

                               

                              Why not do this the first time? Well, you could. If you're using serial numbers as your keys, it might even work better. But the other side of the coin is that numeric keys index substantially faster than text keys. (We're getting a bit down in the weeds here, but for larger installations, it might matter.) So it gives you another alternative.

                               

                              For most systems, the difference will be trivial. But it's something to consider.

                              • 12. Re: Adding an AND clause in the Relationship Manager
                                Razor

                                Awesome Mike, thanks for the tip, that makes perfect sense.  I love getting little nuggets of information like that.  I don't think the possibility of a slowdown would occur here but we have some other projects in mind that I could see the potential of that happening.

                                 

                                I'm going to use the calculation field/method just for consistency sake.  It's not terribly difficult now that I have it figured out and it gives me the experience of working with calculated fields, win-win!