1 2 Previous Next 21 Replies Latest reply on Oct 8, 2013 3:09 PM by ChrisJohnston

    Pick and choose relationship?

    ChrisJohnston

      Title

      Pick and choose relationship?

      Post

           If someone wants a system of information and the data that supports could this model work? I was trying to understand the possibility of a many to many relationship where the recipient of the data could be one of three members. In my illustration there are three possible tables the join needs to send data to. The reason I want the join to send info to different tables is because on some information I do not have Subtopics, or maybe I don’t have Topics, but some I do have both. To make that more clear:

           Information1
               •Topic1
               •Topic2
                   *Subtopic1
               •Topic3
           Information2
               •Topic1
               •Topic2
           Information3
           Information4
               •Topic1
                   *Subtopic1
            

           I need what the Entry (table) distributes (being Documents, Media, and Links) to go directly to the needed area. I have learned about the way that a database can reach across related tables to retrieve what it wants. So my thinking is that just because It wants (in one instance a particular relationship) that it could pick and choose the relationship based on need. Am I thinking of this in proper way? Are there better ways to do this?

      pick%26chooserel.png

        • 1. Re: Pick and choose relationship?
          philmodjunk

               Hmmm, the Information--<topic---<SubTopic----<Entry

               chain breaks if there is no subTopic for your topic.

               Can you describe an example from the user's point of view and how you picture the data that they enter would be entered into each of these entities?

               Does each represent a different data source table or are some occurrences of the same table?

          • 2. Re: Pick and choose relationship?
            ChrisJohnston

                 If it actually breaks than I would be no good. I see what you mean though, and I thought that the way around it breaking to have a Foreign Key that is multiple place. I guess I needs its own relationship for that. I think I am making the mistake because there is a link, I am forgetting without a record there really is no link. Is that the correct way to put it? I am learning so much here. Is there a way to have conditional relationships. I have seen some theories of doing this with a “MATCH FIELD” doing this job. The Task FileMaker 12 Starter Solution uses this. So I guess I need a way to have logic that creates a conditional relationship.

                 something simple
                     •Step1
                     •Step2
                     •Step3
                 something more complicated
                     •Step1
                     •Step2
                         *In Depth Info1
                         *In Depth Info2
                     •Step3
                         *In Depth Info1
                         *In Depth Info2
                         *In Depth Info3
                     •Step4
                         *In Depth Info4

                 Well this data based is being designed to use data that is already in place. What I mean by that Links, Mac and PC Documents, Multimedia. Stuff you have already shown me on this forum make me see how efficient it all can be. I need a way to have something like computer training and something like a recipe access essentially the same data. A recipe will take the data that is available and apply it to the Recipe but a piece of Computer training will have Topics and Subtopics.

                 I am sensing that there may be a way to get a Table Occurrence to do this sort of conditional relationship. In some of the training I have read/look at there was the concept of invalidating a relationship. I wonder if that can become useful here.

            • 3. Re: Pick and choose relationship?
              ChrisJohnston

                   In PHP and other program languages I know something about there is concept of static or class methods, where there does not have to be a record and you can still retrieved what "would" of happened if there was. Do relationships have anything like this?

              • 4. Re: Pick and choose relationship?
                ChrisJohnston

                     I think that I have found the solution already! So my chain needs a record to be a chain? So what if the database just says Hey? If they want a record dependent chain just give em one! A record that is! Just don’t display it if there is no data in it. I just don’t know how or If (I imagine there is) it is possible in FileMaker. Any new record of Information gets a Topic and a Subtopic record, any new record of a Topic gets a Subtopic record. If there is no data in the record don’t display it. This builds on the way a bulleted list works anyway. Sound like the perfect job for a Table Occurrence! I am going to research this.

                • 5. Re: Pick and choose relationship?
                  philmodjunk

                       First, a "match field" is any field we use in defining a relationship where we match the value in that field to the value in another match field from the related table. Every relationship requires at least one pair of match fields.

                       There are indeed special operators and special match fields that extend the capabilities of the basic relationship. We can also modify what records are matched by specifying sort orders and portal filters. Then, with FileMaker 12, we also gain the ability to set up a SQL query using the ExecuteSQL function and that opens up an additional range of possibilities.

                       So you have a number of options to play with. wink

                       And "dummy" records used to maintain the chain are sometimes used, but it's not at the top of the list of what I'd reach for to make a database work as managing those records can be a challenge.

                       I'm having trouble matching your example text of "something simple" and "something more complicated" to what you imagined as a possible entity relationship diagram. What would be the purpose of the "Entry" entity? Why would you link it to SubTopic instead of Topic or Information? Where does "information" fit in here; does it correspond with the "Something Simple" and "something more complicated" lines in your example?

                       And your list "indents" twice (Something more complicated>Step 2>In depth...

                       As that the ultimate limit here or could this be a case where any row shown might need a set of "subs" grouped underneath it?

                  • 6. Re: Pick and choose relationship?
                    ChrisJohnston

                         In my revised version I am wondering if I will be able to take advantage of Execute SQL if my Topic and Subtopic tables need Stock. Am I over complicating this with the Stock table? I was figuring that it could be a way to manage and report on where a group of relative media was sent or collected. If anyone has a suggestion on a better design please let me know. I only want this to be a working design, if my ideas are whack please let me know!

                    • 7. Re: Pick and choose relationship?
                      philmodjunk

                           I simply cannot answer your questions as there is not enough info in your posts to use to come up with any meaningful suggestions.

                           Now we have a "stock" entity. What is it's purpose? How do you need stock to work in relation to your other entities? What do you mean by "relative media"? Can you provide an example of that?

                      • 8. Re: Pick and choose relationship?
                        ChrisJohnston

                             The related media (which I will refer to from now on to related assets) will be a combination of:

                               
                        •           Documents – files on computer any form
                        •      
                        •           Links – addresses to  web pages live or static and other esoteric links
                        •      
                        •           Media – video or audio


                             The reason for Stock is because I want a way report on when media on hand has been used. The media will get included in the database on demand but the Stock entity will show how it came together. I understand that it could be unnecessary, and the job of that could be left to Information Topic and Subtopics. That is where I need the help, this is a database that is a tool to solve problems, and some of these problems repeat themselves over and over again. I consider stock to be somewhat like “Line Items” in an invoicing database INVOICES>--LINEITEMS--<PRODUCTS. Should I just dump that idea and find a way to keep track of usage of the related assets by simpler relationships? Everything in this database will be pointed to, or a FileMaker puts it a reference. I am not storing any video, documents or even letting FileMaker open web pages. Maybe I should have not but I left off two entities in my diagram Problem and Subject. Those seem to be clear to me how they will work with all of this. To pick up from Information

                             Example:

                               
                        •           Problem: WordPress Permalinks will not format correctly
                        •      
                        •           Information: Sitepoint.com setting up Permalinks in WordPress on localhost
                        •      
                        •           Stock (related assets)           
                                           
                          •                     Media: Sitepoint.com Permalinks YouTube video
                          •                
                          •                     Links: Sitepoint.com Permalinks web page
                          •                
                          •                     Document: sample .htaccess file download
                          •           
                               
                        •      
                        •           Tasks: Thing we need to do with this data to solve our problem


                             Some information may come in the form of CBT training or we will give Information the sanction that it need to break down into Topic and Subtopic. Not all Information will have Topics or even Subtopics, we just need the option. That is why in the first diagram I was trying to find a way to have the Stock go to Information, Topic or Subtopic. Sorry for confusing things with the term related media.

                        • 9. Re: Pick and choose relationship?
                          philmodjunk

                               Note: Media and Documents are actually both files that would need to be stored on the computer. Thus, both could actually be represented by the same entity and use the same data source table.

                               The example helps but is incomplete:

                               What role does "Subtopic" and "Topic" play in your given example?

                          • 10. Re: Pick and choose relationship?
                            ChrisJohnston

                                  

                                 I and a small group of computer user, developers, and field people use CBT (Computer Based Training) a lot. Computer based training nine times of ten comes in this form (picture). We also collect Information, this could be Information that solves a problem or Information that will someday be helpful to us. I think that breaking down Information of our own, other than CBT’s is a good thing to do, and the Topic and Subtopic is good buck stop (way to put an end to forks in the road). I want it where if Information does not need a Topic or Subtopic it does not get one. By all mean it could develop a topic later. As for the Stock (which I have renamed Assets) I see a need for Topics and Subtopics to add Assets (Media Documents Links) to different levels of the Information. Some Information that has Topics needs Assets on the Topic level, some on the Subtopic level. You make great point on that Media and Documents are both computer files. I strive to keep them separate because Audio/Video will do something special with this database, that I currently developing the mechanism for. For the purpose of the help you give we can just make them one entity.

                                 Examples of Information:

                                 Information

                                 Topic

                                 Subtopic

                                 WordPress Quick Tips: Migration and Database Reset

                                 picture (explains the layout)

                                 Canon Camcorder Settings

                                 Indoor settings

                                 Outdoor settings

                                 How to avoid BQE rush hour trafic

                                 Load OS via VMWARE

                                 Load Linux

                                 Load Windows XP

                                 Setting for Windows XP when using localhost server

                                 Load Window 7

                            • 11. Re: Pick and choose relationship?
                              ChrisJohnston

                                   I have started working with this layout and have had some good results so far I am sure your expertise will expose potential problems or concerns

                              • 12. Re: Pick and choose relationship?
                                philmodjunk

                                     I believe that this question which I asked at the very beginning remains unanswered:

                                     How many times do you need to "indent" down to a lower level of detail?

                                     You show

                                     Information
                                         Topic
                                              Subtopic

                                     Is that the limit or could you need to go further?

                                     The reason that I am asking is that I can envision a relationship that treats Information, Topic, SubTopic as different records in the same table with a "self join" relationship that would, in theory allow for a nearly unlimited number of "levels" to your hierarchy and any one of them could be linked to a set of supporting documents/websites etc.

                                     Topics-----<SubTopicList>-----Topics|SubTopics

                                     Topics::__pkTopicID = SubTopicList::_fkParentTopicID
                                     Topics::|SubTopics::__pkTopicID = SubTopoicList:_fkSubTopicID

                                     Topics and Topics|SubTopics are both Tutorial: What are Table Occurrences? with the same data source table. SubTopicLIst is a "join" table that facilitates a many to many relationship between different records in Topics. If a specific record is a "leaf" in your tree structure that this produces, it will have no related records in SubTopicList. If it is a branch record, there will be one or more related records in SubTopicList and the records in Topices that these link to can in turn be branches with links to additional topics.

                                • 13. Re: Pick and choose relationship?
                                  ChrisJohnston

                                       I don’t need to go any further. That is what I mean when I said the structure is a good “Buck Stop”. After exploring some possible techniques of making it work with the ASSETS I have gotten what I want with this (pic in link). What I want a way to have this INFORMATION ----<TOPIC----<SUBTOPIC Structure and it also have individual access to a collection of DOCUMENTS, LINKS, and MEDIA. For instance I don’t want INFORMATION to be able to see what one of its SUBTOPICS has. When I say see I mean not to confuse where the content is relative to. You can access all content starting at the parent and navigating to the appropriate place. I am wondering if the way I have got it to work possess challenges with reporting on the data. When I tried to not have separate Table Occurrences everything I tried sent me back to where I was at. I even tried with Execute SQL but could not come up with a query that worked. I am experienced with SQL but not is FileMaker yet. According to this schema will this be difficult to report on? I need to test more but so far it works well for collecting and linking to the data!

                                       http://pctechtv.com/show/workingEDR.png

                                  • 14. Re: Pick and choose relationship?
                                    philmodjunk

                                         I think you should take another look at what I suggested. If you want to link information, or a top or a sub topic to a set of supporting documents and web links, what I suggested makes that pretty straight forward as you have one table for all three and thus one relationship or set of relationships to the tables that store or link to supplementary information.

                                    1 2 Previous Next