2 Replies Latest reply on Jun 29, 2014 7:04 PM by nihmbrisby

    Layouts based on subsets of tables



      Layouts based on subsets of tables



           I’m looking for a way to base layouts on subsets of tables.  I’m wondering if it’s good enough for my purposes to forget ‘sub-sets’ and simply restrict navigation so that one can’t move from records of one subset to records of another without first switching layouts.



           In my database I have thus far considered “contacts” and “authors” as separate entities with separate tables.  Authors create works that are bought/sold/traded/owned/advised on/etc… by contacts. 

           However, I’ve always had a sneaking suspicion that this was problematic, and that in fact contacts and authors are the same entities (each being a subset of a larger group, say ‘people’).  This suspicion has been borne out by many issues, principal being the fact that contacts and authors are both capable of fulfilling the same roles in transactions.  So, when I select a buyer on a transaction- I’d like to be able to select/find from a list/portal of both contacts and (some) authors.  Also, it’s clear that a single person may have an author record AND a contact record in such a way that may be confusing for the end user.

           On the other hand, I must have one layout based on contacts and another layout based on authors.  Furthermore it would be ridiculous for some authors (Dickens, Shakespeare) to show up in the contacts layout.  Neither the trigger-found-set nor the record level access solutions I’ve read about appeal to me for various reasons.  I don’t know another solution, but then again it may be that I’m searching for a solution to the proverbial non-existent problem.  Perhaps they don’t need to be separated at all.

           This is due in part to my approach to user navigation.  One of the first things that struck me when I was introduced to filemaker was the pointlessness (outside of found sets) of the little bar in the upper left hand corner with the number of records and the pie chart.  It always sort of drew my eye despite having no functionality outside of found sets.  Unless I’m missing something, the sort order outside of found sets is always date of creation.  In all my applications, this is meaningless distinction (ie there would never be a situation where the end user would navigate from one contact (or author) to another base on creation date).

           I’ve always planned on not displaying this bar (and then just recreating a less prominent version on my layout.)  However if I *only* display navigation arrows (and # of found records and whatever else) within found sets & all finds from authors (or contacts as the case may be) are restrained to authors (or contacts) via an additional search criteria to all searches, then it seems to me that authors will always be displayed with the authors layout (and contacts with their layout) and that one will never be able to ‘arrive at’ a contact while in the author layout (and vice versa).  In this scenario the distinctions between contacts and authors would be based on a field (and Is_Author field and, amongst authors, an Is_Historical field.)

           Will this work?  Is there a better solution?

        • 1. Re: Layouts based on subsets of tables


               This is a problem, and it has always been a problem. I don't think there is a "perfect way to handle it in all cases." I know of 3 solutions.


               1. As you have, 2 tables, "Contacts" and "Authors". It works, OK. But, as you've noticed, it is kind of a pain if they both want to do something the same; anything to do with "invoice" being a common one. You could handle by being sure keep the IDs of each different, so it is impossible to have duplicates. Say, "C00001" for one and "A00001" (for < 100,000 records, add a 0 if more), text fields (not number). You'd do new Invoice in separate tables/layouts, each with the proper Value List for its table.


               In the Invoice table layout you would NOT be creating an Invoice, hence the VL's above would not be needed.


               However, as you noticed, the problem is if a person can be both, and, since they are not the "same" from the database point of view, it becomes awkward. Finding an Invoice for them would have to be done by "name", which is not a reliable way to run a database (especially if $ is involved).



               2. There is the 1 table, "People" only. A person could have a "type" field, with "Contact" or "Author" (or both, but a person only had 1 record). That would work, but it makes everything which is different about them to be awkward. As you said, every record has to go either here or there (and what about the people who are both?). Finds would need to be "constrained" to what the "type" you started from. 


               Also doable, but a lot of jumping around. And, as you said, some "authors" are not even alive anymore. 



               3. This is the one you haven't thought of yet. Not a lot of people know of this method. It was made more known by a friend of mine (long ago), from San Diego, CA, "David Graham". His file wanted to solve a similar problem, with "company" or "person". Sometimes a "company" was a single "person", but often multiple people. It was awkward dealing with these different things.


               His method was to handle most of their data in a 3rd table (as well as the 2 existing), which he called "contacts" (you'd likely want a different name :-). When a record was created in either of the existing 2 files, a record was created in the "contacts" file. Its ID would be passed BACK to the record of its parent ("company" or "person"). 


               David's trick used a rather unique ability if FileMaker. The Relationship from the "child" tables was based on the "contact ID" (table in both tables, but an auto-entered serial ID in contacts only), with allow creation of the "contacts" table. The "names" fields (first fields entered) were actually IN the "contacts" table; but was visible on the main layouts of the "child" tables. When you entered data into the name field(s), a new record was created in the "contacts" table, AND its Serial ID was brought back and entered into the "contact ID" in the "child" record ("company" or "person"). I was a quite surprised to see that FileMaker would do that for us, but it makes sense.


               In other words, after sending this up, you did not have to write a script in order to get the new "contacts" record or bring back its new ID to you. The "contacts" table, behind the seen, was storing every record of the other 2 tables. 


               So, the "contacts" table would be used for another where you wanted to handle the other 2 records as the SAME. Yet the 2 tables were used when you wanted to see them as "separate" tables. 



               HOWEVER, I see one flaw with this. It is that much of the data, such as names, is in the center "contacts" table, it was not action stored in the "child" table, hence not "indexed" there. So a Find in the "child" tables using the name would not be as fast as it could be (though I've found relationships based on a simple ID, which was always used, is still pretty fast). 


               A simple fix for this, especially in modern FileMaker (not in those days), using a Script Trigger, the below could be done. Upon changing one of the name fields (company name, first name, last name, etc.), a script could move the change to dedicated fields in the child table. There are 2 things to keep in mind:

               1. The first entry of the name fields MUST be into the "center contacts" table, as that is what triggers the Relationship to create the "contacts" record, and return its ID to the child field.

               2. After that however, it would be nice to use another field of the name, local within the child, for its "indexed" ability.


               OK, I got it :-] 

               Put the local child "name" field, enterable in Find mode only, on top of the related contacts::name field (enterable only in Browse mode).

               Create a script: Set Field [ local contact ID; local contact ID ] (set the ID field to itself). Commit Record.

               Add a Script Trigger to the contact::name field, runs only Browse mode, which calls the above script.

               Set any such local "name" field to Auto-enter by calculation, point at its "contact::name" field; uncheck [  ] Do not replace existing value of field

               The above causes any local "name" field to be reseted to any change to the "contacts::name" fields (which has the Script Trigger attached).

               So, you'd have reliable local "name" fields, to use for Finds, etc..


               P.S. If you put the "name" fields ONLY in the "child" fields, NOT in "contacts", then you wouldn't need the above. But then you'd need some OTHER method to case that "contacts" record to happen. Also you'd need a calculation field in the Contacts table anyway, for "invoices", etc., to use instead of the "child".


               You know, I do not speak very well, as "words" are no longer my "strong point"; in fact, nothing is really my "strong point" :-|  But I am old enough to have lots of old stuff to remember :-/  Oh, because of the "words" problem,* I have trouble reading; so hopefully the above kind of makes some sense.


               * I had a stroke 3 years ago. I am doing OK, considering (I could have died), but I have something called "aphasia", which means words are often hiding from me, and reading them is questionable, at best. Fortunately, the computer can often figure it out, and/or "speak" to me…

          • 2. Re: Layouts based on subsets of tables


                 That was a great post- thanks!

            “I was a quite surprised to see that FileMaker would do that for us, but it makes sense.“

                 I’m pretty sure I encountered the same effect (under different circumstances) with similar surprise/pleasure which I expressed in this post: Creating new join records via relationships

            “So, the "contacts" table would be used for another where you wanted to handle the other 2 records as the SAME. Yet the 2 tables were used when you wanted to see them as "separate" tables. “

                 That is clever!  I especially like that it leverages the power of relationships and minimizes scripting.  That’s basically been my motto in the development of my database (which is my first).  I’ll have to give the issue some thought. 

            “You know, I do not speak very well”

                 I understood you perfectly!  I am familiar with aphasia.  While your syntax is unusual on occasion, your meaning is crystal clear.  Besides, strict grammar/syntax is less of an issue in this day and age when so many netziens speak English as a second language.  What’s important is the content of your message.  Your ‘hidden parent table’ method is a great tool.