7 Replies Latest reply on Apr 13, 2017 3:48 PM by philmodjunk

    Adding new records to one client


      Hi l am new on the forum and am using FMP 14 advanced. I am trying to create a register to track client follow ups. I have 4 tables: Clients, FollowUps, Initial Assessment and Joint_Client_Follow.

      The idea is when new client comes: name is added with other details and an initial assessment done. On next visit the same client will be seen but as a follow up now.

      I have a layout based on the clients with a follow up portal.

      l want to be able to search client and then enter follow up information under that. I want to use one portal row such that when l press a button (new follow up) after searching and finding client, the portal row is empty and ready to accept new information. My problem is on the script of the button. Thank you all in advance.

        • 1. Re: Adding new records to one client

          I think that you have a more important issue with your tables and relationships.

          I have 4 tables: Clients, FollowUps, Initial Assessment and Joint_Client_Follow

          A join table--which is what Joint_Client_Follow appears to be is to facilitate a many to many relationship between two other tables. By the name, it would appear that you have set it up to join Clients and Followups. But you do not need to do this unless One FollowUp record might possibly need to be linked to more than one Client In addition to one Client being linked to more than one Followup. From your description of what you want to do, you have one client linking to multiple followups, but I see no indication that you also need to be able to link the same followup record to more than one client. Unless you need to do both, you don't need the join table and adding this table complicates your design--including how you would set up that portal for adding more followup records.


          So you really need to confirm whether you actually need Join_Client_Folllow or not.

          • 2. Re: Adding new records to one client

            Hi thanks for the reply. What l want is as you described one client linked to multiple followups. There is no need for the same followup record to be linked to multiple clients. I will remove the join table and have setup without. Please assist on how can formulate the script then to allow me to add new followup records after I have find client using ID. I am using one portal row and would need it to be cleared when enter the button (new followup).

            • 3. Re: Adding new records to one client

              If we had kept the join, we would have needed to create two records, one in the join table and one in the followup table.


              Now we just have this relationship to work from for your portal:


              Clients::__pkClientID = FollowUp::_fkClientID


              You may or may not know this, but if you enabled "allow creation of records" for FollowUps in the above relationship, you get a blank "add row" after the last real portal row record. Simply entering data into the fields of that add row will create a new FollowUp record. That may not be what you want, but I'm pointing it out as it's a very simple way to set this up.


              For your button, there are 3 ways to set it up to add a new record in the portal:

              1) The script can go to the last portal row (that add row) if "allow creation..." is enabled and set a field of that portal row record to a value to create a new record.


              2) The script can set a variable to the __pkClientID field's value, change to a layout based on FollowUp, create the new record and set _fkClientID to the value of the variable before returning to the original layout. This method is often enhanced by opening a new window hidden off the edge of the layout which is then used to change layouts and create the new portal record before closing that extra window


              3) A special "MagicKey" relationship can be used to create the new portal record.


              Clients::gMagicKey = FollowUp|MagicKey::__pkFollowUpID 

              gMagicKey is a simple data field of the same type as __pkFollowUpID. It is often defined as a global field. __pkFollowUPID must be defined to auto-enter a serial number or UUID and "allow creation..." must be enabled for FollowUp|MagicKey. FollowUp|MagicKey is a new occurrence of the FollowUp Table separate from the one used for your portal's relationship.


              For MagicKey, your button just needs to do this:

              Set Field [Clients::gMagicKey ; "" ]
              Set Field [FollowUp|MagicKey::_fkClientID ; Clients::__pkClientID ]
              Commit Records


              Options 2 and 3 do not require that "allow creation" be enabled in the Clients to Followup relationship used with your portal.

              Option 1 is fairly brittle. Simple layout changes can cause it to fail. Just adding the wrong script trigger at some future date might be enough to cause it to stop working correctly.

              Option 2 is better, but it can trip a few script triggers. If you don't open a hidden window, the layout change can also cause tab and slide controls to revert to their default settings and portal scroll bars can snap back to the top.

              Option 3 avoids those problems, but you have to add the needed relationship support it.


              There is also a way to set up a one row filtered portal as your "add row" and then you can move the "add row" to the top above your original portal instead of at the bottom.

              2 of 2 people found this helpful
              • 4. Re: Adding new records to one client

                Great, I really appreciate your help. I have opted for option 3 using the Magic key. I have attached a screenshot of how far l am at the moment. When l use the button (new follow up) its adding a new record to the client but then moves to the other client.Screen Shot 2017-04-13 at 11.18.57 PM.png


                And this is how my relationship table looks like.Screen Shot 2017-04-13 at 11.28.50 PM.png

                • 5. Re: Adding new records to one client

                  I can't tell anything from the screen shot except to note that I don't see any portal at all in it.


                  Do you have a portal to followUp?


                  Is this layout based on Client?


                  Please post the actual script preformed when you click "New FollowUp".


                  And why is there date field named "follow up" on the layout? Given the need for multiple followups, I don't see what that would be used for .

                  • 6. Re: Adding new records to one client

                    The layout is based on client.

                    The portal is for followup. All the info weight - Number of sexual partners is in a portal. Only that l have rearranged them. The date is meant to indicate the date follow up is recorded and the date client is to come. Date field is not in a portal.Screen Shot 2017-04-13 at 11.40.04 PM.png

                    This is my first attempt using FM.

                    • 7. Re: Adding new records to one client

                      But I do not see any portal on the layout that you showed to me. Where is it?


                      I also cannot picture what you mean by:


                      but then moves to the other client.

                      What "other client"?