1 2 Previous Next 17 Replies Latest reply on Jan 26, 2012 12:49 PM by JMadison

    Master List

    JMadison

      Title

      Master List & Slave List  Synch Question - Please Help

      Post

      I have a bit of an odd situation and I can't for the life of me figure it out. I'd appreciate any help on the matter.

       

      I need to have a setup like THIS:

      MASTERLIST - contain main data

      SLAVELIST - contains MASTERLIST data + SLAVELIST data synched

       

       

      Here is an example:

      Masterlist

      site 1 - www.yahoo.com

      site 2 - www.msn.com

      site 3 - www.google.com

       

      Create Slavelist 1 through script that populates with info from Masterlist + creates Slavelist 1 fields for EACH Masterlist item (in the above case it "site")

      site 1 - www.yahoo.com + Slavelist 1 Yahoo info

      site 2 - www.msn.com + Slavelist 1 MSN info

      site 3 - www.google.com + Slavelist 1 Google info

       

      If I choose to create Slavelist 2 then the process should be able to repeat itself (EG - autocreate Slave list based on Masterlist).

      Also, if I ADD or delete anything from MASTERLIST this should reflect on the Slavelists.

      In Mysql this was pretty easy but in Filemaker I'm completely lost.

      Don't know if I explained my situation clearly, please ask if something is unclear. I've tried to start this project in different ways but I can't figure it out.

      Any help is greatly appreciated - thanks.

       

      PS: Another way to explain it would be like this:

      • The Masterlist contains the sites which can be added and deleted.
      • Each Project is comprized of a Slavelist which contains the Masterlist info plus specific project info for each site.
      • The Masterlist should be synched with all projects (Slavelists) which should reflect any additions or deletions from the Masterlist.

      I get stumpped when it comes to synching the tables (Masterlist with Slavelists). Is there a script for such a thing?

        • 1. Re: Master List & Slave List  Synch Question - Please Help
          philmodjunk

          I don't see a need for any script, but I may not have a complete picture of your issue either.

          You appear to be describing a standard one to many relationship:

          MasterList ----<SlaveList

          Where one record in MasterList is linked to many records in SlaveList. This would appear to require this relationship in Manage | Database:

          MasterList::__pk_MasterListID = SlaveList::_fk_MasterListID

          Where __pk_MasterListID is an auto-entered serial number and _fk_MasterListID is a simple number field. (pk and fk are simple naming conventions to help identify fields that are primary (pk) or foreign (fk) keys.)

          With such a relationship, you can choose to enable "allow creation of records via this relationship" for SlaveList and then you can put a portal to SlaveList on your MasterList layout to view and edit the related records in SlaveList. With "allow create" enabled, you can add new related slave list records simply by entering data in the bottom blank row of the portal.

          With such a relationship, you can also set up a list view or table view layout based on SlaveList and include fields from MasterList in a header, grand summary and/or sub summary part. Such layouts require performing finds and (often) sorting to display data properly but offer layout options not possibile with a portal and also are a better option for printing reports that include data from both fields.

          Not sure what you meant by this sentence:

          Also, if I ADD or delete anything from MASTERLIST this should reflect on the Slavelists.

          But if you enable the "delete" option for SlaveList in the above relationship, deleting a MasterList record automatically deletes all related SlaveList records. This is called a cascading delete and must be used with caution or you can accidentally delete records you did not intend to delet.

          • 3. Re: Master List & Slave List  Synch Question - Please Help
            JMadison

            Thank you for the help. I ran into the problem I alluded to before:

            "Not sure what you meant by this sentence:

            Also, if I ADD or delete anything from MASTERLIST this should reflect on the Slavelists."


            What I meant was this:

            I created the Masterlist:

            Masterlist:

            Site1

            Site2

            Site3

             

            Slavelist 01:

            Site1 (from Masterlist) + Slavelist 01 info

            Site2 (from Masterlist) + Slavelist 01 info

            Site3 (from Masterlist) + Slavelist 01 info

             

            But if I create Slavelist 02 it does NOT automatically generate records from the Masterlist for Site1, Site2 and Site3. If I create a new record in Masterlist AFTER I create Slavelist 02, then that record is reflected in Slavelist 02

            Slavelist 02:

            Site4 (newly created in Masterlist) + Slavelist 02 info

            But WHERE is Site1, Site2 and Site3? They weren't created because at the time I entered Site 1, 2 and 3 there was NO Slavelist 02

            Is there a script or a way of making Filemaker create automatically for the Slavelists all the previous entries from the Masterlist?

            I hope I'm making sense here :) ... I mean is there a way of making Filemaker synch all

            • 4. Re: Master List & Slave List  Synch Question - Please Help
              philmodjunk

              But if I create Slavelist 02 it does NOT automatically generate records from the Masterlist for Site1, Site2 and Site3. If I create a new record in Masterlist AFTER I create Slavelist 02, then that record is reflected in Slavelist 02

              Why would it? I don't follow the logic of your design. The "from masterlist" data should not be present in the records in Slave list. Instead, selecting the ID of the appropriate Master list links that new slave list record to a specific master list record and the data in the record is now accessible from the slave list based layout. You can just add fields from teh Master List table to this layout to see the data from the selected related record.

              Can we revisit this with some sample data?

              Here's the take I have on your project and I could easily be mis-interpeting your posts....

              YOu create a masteList record, __pk_MasterListID = 1, with a URL field storing "www.yahoo.com"

              In slave list, you'd create a new record and select or enter 1 in the _fk_MasterListID field of that slave list record. If you have added the URL field from MasterList to your layout, you'll see "www.yahoo.com" appear in every slave list record where you select or enter a value of 1 in the _fk_MasterListID field for that record.

              I'm guessing that you want more than that, that you want data from all three sites (from your example) to appear when you create a single new slavelist record. That appears to stand this relationship on its head. Can you describe what you want in greater detail here. In particular, how do you determine which "master list recrods" should be part of a new "slave list record's" set of data? (Perhaps we have a many to many relationship instead of one to many.)

              • 5. Re: Master List & Slave List  Synch Question - Please Help
                JMadison

                Hi PhilModJunk. First of all thanks for taking the time to try to make sense of all this... Yes, it's a bit of a more complicated situation but let me explain a simplified purpose of what it needs to do:

                EXAMPLE (kinda....)

                I don't have any data from the client but here is an example:

                John opens new project in his name (John) and adds 25 links to article pages with pertinent comments for each one (one link and comment per record)

                Bob comes along and opens a new project in his name (Bob). Bob's project automatically contains the 25 links that John did, but WITHOUT the comments. Bob puts his comments on those 25 articles and then adds 25 sites of his own, with his own comments.

                John comes in the next day and opens his project (John project) and sees the previous 25 sites PLUS the sites that Bob added, but without the comments. John now fills in his comments for the sites that Bob added.

                John also mentions to the administrator that site nr. 13 link to article isn't working anymore. Administrator deletes site nr 13. Next time John and Bob open their respective projects they see that site 13 has been deleted.

                All this would apply no matter how many Projects are created and when. If a new project is created by Dave 3 months down the road after John and Bob collected about 315 sites, then David's project would automatically be created with 315 sites (but with blank comments for Dave to fill).

                Wheeeeeew... this is turning into a novel :) but I hope now it's clear :) Thanks again for having the patience to read all this. I did say in the begining this is an odd situation and I just cannot figure it out. It may be a simple answer.... and it may turn out that I'm as dumb as a 2 x 4...  :)

                 

                In mysql I did this by parsing the info. So when somebody opened a new project there was a script that looked at the masterlist and added them to the slavelist. When somebody made a change to the masterlist (as in adding or deleting a site) then a scrip would check ALL slavelists and do the same - add or delete a certain entry. Any time you'd select a project the script would sync it's respective slavelist to the masterlist.

                 

                PS: Am I stretching the possibilities of what Filemaker can do??

                • 6. Re: Master List & Slave List  Synch Question - Please Help
                  philmodjunk

                  What's unclear to me is what constitutes a "new project".

                  How do you know (if doing this with paper and pencil) that John's "25 sites" should also be listed as part Bob's (and John's and...) project?

                  Should all site records in that table be listed or just a specific subset of them?

                  If a subset, what common value identifies the ones to select for this list?

                  • 7. Re: Master List & Slave List  Synch Question - Please Help
                    JMadison

                    What's unclear to me is what constitutes a "new project".

                    It would be a new slavelist (table?) that features ALL the sites created other contributors (ie: John, Bob & Dave in our example). Obviously without the comments, which would be blank for the created of the project to fill in.

                    How do you know (if doing this with paper and pencil) that John's "25 sites" should also be listed as part Bob's (and John's and...) project?

                    Not sure I understand your question, but this is what the client wants. Everytime a new project is created it should include ALL the sites that have been added so far by other users.

                    Think of it this way - If John adds 25 article links to his project and then Bob creates a new project, Bob should NOT have to do the work again of finding and re-entering the links that John did. Bob's project should automatically create and update with ALL the sites John did, leaving space only for the personalized comments from Bob. This way Bob goes over John's sites and gives HIS opinion on it.

                    Same should be true for the reverse: if Bob adds sites John should also see those sites and be able to give HIS opinion on them. This would be true no matter how many users.

                    Should all site records in that table be listed or just a specific subset of them?

                    All records should be listed in all Projects - not subsets. So if John collected 25 links and Bob collected 25 links then they both should have 50 links in their project - difference being that Bob would have John's sites but blank comment space for him to fill, while John would have the reverse.

                    If a subset, what common value identifies the ones to select for this list?

                    There is no real subset. I may not have expressed myself clearly, but each project (or slavelist) should mirror the collective work (sum of sites from all projects) with blank comments for the new user to fill.

                    That's why I was using the concept of masterlist, where masterlist = all sites added. Maybe I was thinking of this wrong and it should not be done with masterlist and synched slavelists - but I don't know how else you'd do it.

                    • 8. Re: Master List & Slave List  Synch Question - Please Help
                      philmodjunk

                      I think the term "project" is not being used consistently here. You have a master project for all sites in your list of sites--no matter which contributor added that site to the list. You then have sub projects, where each is the set of sites added/commented on by a different contributor. This is where the confusion lies as I see it.

                      It would be a new slavelist (table?) that features ALL the sites created other contributors (ie: John, Bob & Dave in our example). Obviously without the comments, which would be blank for the created of the project to fill in.

                      Don't use a separate table for the next such list. Separate tables will be very very cumbersome in this situation. It sounds like you really have this relationship:

                      Contributors----<Comments>----Sites

                      Contributors::__pk_ContributorID = Comments::_fk_ContributorID
                      Sites::__pk_SiteID =Comments::_fk_SIteID

                      Thus, your list sites would be all sites of a given project. "Bob's" sites would be all sites for which he has recorded at least one comment. Since the sites and comments are in different tables, it's simple to list the sites without the comments.

                      What you have here is a many to many relationship between contributors and sites. A site can have many comments (one from each contributor) and a contributor can add and/or comment on many different sites. The Comments table here functions as the "join" table between contributors and Sites to make that many to many relationship possible.

                      Since creating a new set of tables for every MasterProject is not a good option, you also appear to need either of these two relationships in order to manage different lists of sites for each MasterProject. The difference in structure depends on which fits the role of your contributors in this process:

                      MasterProject-----<Contributors

                      MasterProject::__pk_MasterProjectID = Contributors::_fk_MasterProjectID

                      or more likely you have this one:

                      MasterProject----<Project_Contributor>-----Contributors

                      MasterProject::__pk_MasterProjectID = Project_Contributor::_fk_MasterProjectID
                      Contributors::__pk_ContributorID = Project_Contributor::_fk_ContributorID

                      The difference is in whether a single contributor might possibly work on more than one MasterProject. If so, you use this second option and use Project_Contributor to manage which contributor is assigned to a given Master Project.

                      Here's a demo file on many to many relationships with a join table that you may find helpful in figruing out how to design your system to support such relationships: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

                       

                      • 9. Re: Master List & Slave List  Synch Question - Please Help
                        JMadison

                        Ok, it took me a while to wrap my head around this. Your examples really helped. Thanks PhilModJunk.... I have a LOT to learn... Right now I'm beating my head against the wall trying to figure out how to allow a user to copy certain fields from filemaker once he has a security privilege set up.

                        Thanks again for the help.... it was very appreciated.

                        • 10. Re: Master List & Slave List  Synch Question - Please Help
                          philmodjunk

                          Copy fields to where? the clipboard? or to another location in the database?

                          • 11. Re: Master List & Slave List  Synch Question - Please Help
                            JMadison

                            To clipboard... I gave a user a restricted privilege... but they can see and edit that particular field... however they cannot copy it to the clipboar and I cannot find info as to where that option (to allow a user access to clipboard copy) resides.... Thanks

                            • 12. Re: Master List & Slave List  Synch Question - Please Help
                              philmodjunk

                              If they can edit the data, I do not see why they can't copy it to the clipboard. Perhaps "allow exporting" must be enabled before the system will let you copy data to the system clipboard.

                              • 13. Re: Master List & Slave List  Synch Question - Please Help
                                JMadison

                                I tried that - "allow exporting" is ON for the privilege - but when I login under their user and pw I cannot copy it... If I right click, I don't get the dropdown with the usual - Cut, Copy etc.... Instead it deselects the content of the field. Any ideas what might be causing this?

                                In their privilege I can select delete and edit the field at will.... but no access to COPY PASTE ETC.... However, in my admin privilege I can rightclick on the same field and get the proper dropdown with CUT COPY ETC... That's why I figured it might be related to privileges.

                                • 14. Re: Master List & Slave List  Synch Question - Please Help
                                  philmodjunk

                                  Are the copy/paste options listed in the edit menu?

                                  What menu options did you specify in the drop down for that purpose in Manage Security? (may have to choose a less restrictive option.)

                                  1 2 Previous Next