9 Replies Latest reply on Sep 27, 2016 6:04 PM by philmodjunk

    Auto-Create records in related tables

    AndrewFoo

      Title

      Auto-Create records in related tables

      Post

      I'm working on a database that will allow me to keep track of employees and their skill sets.

      I've created tables that contain different information about an employee. Each table is related by a key that is automatically created in the parent table.

      I'm trying to program the database to auto-create records in the related tables when a record is created in the parent table.

      I've edited the relationships between each table and checked off: "Allow creation of records in this table via this relationsip" and "Delete related records in this table when a record is deleted in the other table" under the "child" tables.

      However, everytime I create a new record in the parent table, the child tables don't automatically create records with the same ID number that each table is related by.

      Can anyone help me with this problem?

      Thank you!

        • 1. Re: Auto-Create records in related tables
          philmodjunk

          FileMaker is doing exactly what you've told it to do. It's just not what you want it to do ;-)

          The "allow creation..." option makes it possible for you to place a portal to the child records on the parent table's layout and then add related records in the child table by entering data in the bottom blank row of this portal.

          Any other automatic creation of related records usually needs to take place by performing a script to create the related record. A set field script step performed from this layout, that assigns data to a field in the related table will also create a related child record if no such child record exists yet or you can write a script that captures the current primary key value in a variable of the parent and switches to the child layout to create a new record, assign the variable's value to the foreign key and then switches back to the parent record's layout.

          Generally speaking, you don't need to create the related records when you first create the parent. Instead, you can create them when you start to enter data in the related child table--and the portal is one way to do this. You can also set up a layout based on the child table with the foreign key field set up as a required field with a drop down list of Parent Record ID's (Put ID number in column 1 and a name field in column 2). You can then create a child record and attach it to a parent by selecting the parent from the drop down.

          • 2. Re: Auto-Create records in related tables
            stcav

            I have what I think is a similar challenge in creating a process type diagram display using objects of the same type. The process objects are all kept in one table. The display I've created is like this:

            A -> B -> C

            So process A feeds process B which feeds process C. Process B could be fed by any number of processes in position A; process B can feed any number of processes in position C. Each display is focussed on one process (B) so might show several As and Cs. 

            I've created the view in a layout with the parent table occurrence (Process) displaying B. A is displayed by a portal based on an intersection table linking to a table occurrence 'Process feeding', which is based on table 'Process'. C is displayed by a portal based on an intersection table linking to a table occurrence 'Process fed', also based on table 'Process'.

            When looking at B, I can successfully display A, which already exists in the table 'Process', by creating a record in the intersection table using a drop-down box showing a reference number and title of the 'Process feeding'. This works fine.

            In principle, I could do the same thing to display C, and the layout works well when I manually create the downstream intersection records linking B and C. However, the logical link between B and C already exists because I would have created it when C was the focus and I linked it B. So I reason that the intersection table to the right is a mirror of that to the left of the focus B. Indeed for data integrity I need it to be so, and it must stay synchronised when the links are altered or deleted.

            Therefore, I want the right hand table, B to C, to be populated with a new record whenever a record in the left hand table is created by linking B from A. Then, if I move the focus from B to A, A moves centre stage, B goes to the right and shows up due to the automatically created record, and D comes in from and is shown on the left. (Then the display would be D -> A -> B.) Look-ups and calculations don't do the trick (or I'm not doing them right).

            Would I be right to judge from Phil's answer above that I need a script to this? What would trigger such a script? Ideally, I would like it to be completely automatic when the A to B link is created. Similarly, would I need other scripts to trigger when changes or deletions are made?

            Alternatively, am I making this too complicated? I have concluded that I can't use the same intersection table (A to B) because it could not distinguish between downstream and upstream processes so would show A -> B -> A, which is not allowable.

            Any advice would be much appreciated, please.

            Stephen

            • 3. Re: Auto-Create records in related tables
              philmodjunk

              I'm not sure I follow all that... Frown

              What I am understanding is that you have one data source table, three table occurrences of this table and two join (intersection) tables to implement the many to many relationships involved.

              I think you've set up:

              A---<A_B>---B--<B_C>----C

              A::ID = A_B::ID1
              B::ID = A_B::ID2

              B::ID=B_C::ID1
              C::ID=B_C::ID2

              Where A_B and B_C are the intersection tables linking the different TO's of the "Process" data source table. It is possible to use the same data-source table for A_B and B_C, but if you do, you have to add an additional field to the table that you would use to distinguish between "A->B" records and "B->C" records so that you can filter them out of portals as well as correctly organize your reports.

              • 4. Re: Auto-Create records in related tables
                stcav

                Hello Phil,

                Yes, I think you've followed all that. Smile

                A---<A_B>---B--<B_C>----C ...That's correct.

                So, assuming all the entities are present (usually by importing in my case) I can set up A_B quite happily in a portal. The main layout is focused on B, the portal holds A.

                So let's assume I have Process no. 1 in position B, and I create the link  A_B so that Process no. 2 in position A is identified as feeding no. 1.

                What I want to happen then is for B_C to be created automatically immediately afterward so that when Process no. 2 is in position B it correctly shows Process no. 1 in position C to the right. 

                I have tried to use the same intersection table A_B to create this, but I stumble over identifying how to create the display. I concluded that another portal was required, so I went for another table B_C. However, your reply suggests that another field in A_B would do the trick, which is where I started and seems intuitively to be right.

                Could you steer me in the right direction with a bit more information on how to do this, please?

                Many thanks,

                Stephen

                • 5. Re: Auto-Create records in related tables
                  philmodjunk

                  The main layout is focused on B, the portal holds A.

                  In FileMaker, I usually use the Portal to display A_B, with related fields from A added to the portal as needed. I then enable "allow creation of records..." for A_B in the relationship between B and A_B. I can then select a Process record from a drop down in A_B that creates the join record and links it to A.

                  This is actually simpler than I originally thought.

                  You do need two portals, but the relationships will keep the A process in the A portal and the B processes in the B portal. The secret is to base the portals on the Join TO's, not the A and B TO's.

                  Here's a demo file you can play with:  http://www.4shared.com/file/Q8-wDtiX/ProcessLinkage.html

                  • 6. Re: Auto-Create records in related tables
                    stcav

                    Phil,

                    You're a star! Laughing

                    Many thanks for your guidance, and especially for the demo file - worth a thousand words. I was nearly there, but I was over-complicating things. Your solution is elegant and I'm grateful for your time.

                    Stephen

                    • 7. Re: Auto-Create records in related tables
                      BenOtto

                      Dear Phil,

                       

                      how do I access the file on the server webpage? All Links and buttons on the page lead to the attempt of some (creepy) software installation. I suppose those are not the intended buttons I should press. :)

                      Do you still have a copy of the file or might be able to direct me to the "right button" on the 4shared.com webpage? 

                       

                      Thanks a lot

                      Benjamin

                      • 8. Re: Auto-Create records in related tables
                        dang

                        Man! you weren't kidding! That is a difficult and spammy site to download from. I know it's a bit late, but for others trying:

                        1. I'd HIGHLY suggest some type of anti-malware (Malwarebytes) or something and perhaps an Adblock to keep your computer safe while you do this. You are looking for a box that says "WAIT 20 SECONDS" and has a download button.If it's not there, read on. Do NOT install any software or click links inside the pages that pop up. (Looking at the page source now, I see that my adblocker may be the reason it takes that button so long to appear. It looks like there is a 4 minute timer if you have an adblocker that causes issues - go to next step.

                         

                        2. Click the little download button ONE time. Close the resulting spam tab. EITHER wait 4-5 minutes and THEN click the download button again OR set your computer clock ahead 5 minutes then click the little download button again.

                         

                        3. EVENTUALLY you'll see a 2 inch box that says "Wait 20 seconds" and has a download button on it. THAT is the one you are waiting for and want to click. You'll need to log in with your Facebook, Twitter or Google+ account or create a new account. With such an amazingly spammy site, I'd probably use a throwaway email address from something like mailinator site or something.

                         

                        The download is tiny and works!

                        • 9. Re: Auto-Create records in related tables
                          philmodjunk

                          This is a very old thread. I stopped using that share site years ago and you are describing why I stopped using it. Drop box is much simpler and does not expose the user to Spam or possible Malware.

                           

                          If I could, I would edit that post to remove this link--since I haven't used it in years, I'm surprised it even works.

                           

                          (I can't edit that post because it was created in a different system with different software and imported here when FM Inc finally gave up on RightNow and switched to Jive.)