10 Replies Latest reply on Jan 6, 2017 10:17 AM by philmodjunk

    How to create new related records for all in found set

    Interrobang

      So, I've looked through the posts and found multiple questions similar to mine, but I am not FileMaker-savvy enough to tweak the instructions for my own use.

       

       

      The situation:

      I have two tables. "TableA" is a list of communities and "TableB" is a list of assignments for each community (created on a weekly basis), this is a one-to-many relationship.

       

      The solution:

      I would like to have a script that allows me to

      1) choose the week value from a list on "TableB"

      2) create a new "TableB" related record for every "TableA" record in my found set

      3) have each new record in "TableB" marked with the previously chosen week value

       

       

       

      I really do appreciate any help that you can give and will be sure to mark the question as answered (I noticed that a lot of users seem to forget).

       

       

      More details (may not be needed or helpful):

      • My criteria for the found set of communities is just whether they are marked "active" or not.

      • The key for the one-to-many relationship is simply the community's name (basic and probably a poor choice, I know).

      • The assignments are just like a to-do list to be checked off and a place to make notes about the community visit.

      • I look at my "to-do list" each week by searching for records marked with the current week (not a date-based field system).

      • Right now I have to go to the list view of active communities, tap my "new" button that creates a related record (which opens in the corresponding layout), the week field is auto-selected and I choose the correct value, lastly I tap the button that takes me back to my list of active communities and start over with the next record in the list.

       

       

      Happy New Year!

        • 1. Re: How to create new related records for all in found set
          philmodjunk

          You able to write a script with a loop?

           

          You can specify data that you want to be entered into fields of your related records in fields with global storage first, then run a script that loops through your records and creates a related record in table B for each record in Table A. From what you describe, the script can also perform a find on Table A for all "active" communities as well.

           

          Enter Find Mode [ ]

          Set field [ Table A::Status ; "Active" ]

          Set Error Capture [ on ]

          Perform Find [ ]

          Set Error Capture [ off ]

          If [ Get ( FoundCount ) > 0 //active records were found ]

            Loop

                Set Variable [ $Community ; TableA::Name ]

                Go to layout [ "Table B" ; (TableB) ]

                New Record/Request

                Set Field [ TableB::CommunityName ; $Name ]
                 Set Field [ TableB::week ; TableA::GlobalWeek ]
                 Set Field... //use as many set fields and global fields as you need here

                Go to Layout [ Original Layout ]
                 Go to Record/Request/Page [ next ; exit after last ]
             End Loop

          End If

           

          The above script is the simplest I can think of that does not also require making changes to your relationships graph. For a method that does not change layouts like this--which can trip script triggers that can then interfere, look up MagicKey to see a relationship based method for creating these new records that does not require a change of layouts.

          1 of 1 people found this helpful
          • 2. Re: How to create new related records for all in found set
            Interrobang

            Thank you for your prompt response! I will test it out as soon as possible

            • 3. Re: How to create new related records for all in found set
              erolst

              An alternate method would be to find the desired records in A, then change to B and import from A.

              1 of 1 people found this helpful
              • 4. Re: How to create new related records for all in found set
                Interrobang

                I am SO thrilled — thank you philmodjunk!

                 

                Thanks to you I learned about Global Fields (I can't believe how much foundation knowledge I'm lacking) and the rest of the script just took a little tweaking (because I'm pretty basic and tried to work with what I knew so I didn't have to come back and ask a bunch of questions)...

                 

                I changed up the first Find to the steps I'd been using in my other scripts because I wasn't quite sure how yours worked (I did try yours first, but it didn't work so I bailed )

                 

                And I wasn't very familiar with Variables, but (again) thanks to you I'm more comfortable. In your script there are two Variables ($Community and $Name) and it looked to me like they should be the same, so I did make that change.

                 

                Lastly, I just added a step to sort the list after the loop since it would end up rearranged at the end.

                 

                 

                AND EVERYTHING WORKS SO PERFECTLY! I so appreciate your time and help This script is extremely useful and I wouldn't have figured it out myself.

                 

                Thank you again!

                1 of 1 people found this helpful
                • 5. Re: How to create new related records for all in found set
                  philmodjunk

                  Glad that you caught my mistake with the variable names. I'll edit my previous post for the sake of any who might later read this thread while researching their own issues.

                   

                  Now about matching records by community names. This isn't as big of a "sin" as matching records by a person's or a company's name--people and companies change there names where communities do so very rarely. But you do set yourself up for problems when you match by names.

                   

                  For example, If you enter a community name and misspell it, create related records that match by this misspelled name and then discover your error, you can't just go in and fix the misspelled name. If you do, you break the link to the related records that are all still set up to match by the misspelled version of the name. You also have to carefully find and update this field in every related record--with the real chance that you will complicate your problems should you make a mistake while updating them.

                   

                  Match your records by an auto-entered serial number or UUID value instead. These values, once assigned to a community, never, ever need changing and are always unique--thus avoiding issues that arise when you relate records by a name.

                   

                  A few more notes on this:

                  Just because you link records by ID doesn't mean that you can't use names or similar text fields to find or link to records. A standard method used in Filemaker solutions is a "use values from field" value list where the first field is the ID and the second field is a name or description. The user uses the text field to determine which value to select, but the value list enters the corresponding ID into the field set up with this value list.

                   

                  Sometimes it's perfectly OK to match tables by name--but only for special use purposes intended to support the standard ID based relationship matches or as a way to find records by name that is in addition to, not a replacement of the ID matching. For example, I sometimes use a name based relationship as part of an auto-enter scheme that looks up the unique ID matching the name used to find the record from which to look up the ID. This is done with scripted support to handle cases where the entered name does not match any record and cases where it matches more than one, but does make for a drop down list that can auto-complete on the name entered.

                  1 of 1 people found this helpful
                  • 6. Re: How to create new related records for all in found set
                    philmodjunk

                    Hmm apparently selecting a response as "best answer" locks the originator out of editing their post...

                    • 7. Re: How to create new related records for all in found set
                      Interrobang

                      Thank you for your additional notes — I'll use them when I'm updating!

                       

                      And I unmarked your response as the correct answer so you can edit it

                      • 8. Re: How to create new related records for all in found set
                        philmodjunk

                        Nice thought and thank you, but the system still won't let me edit that post, maybe that option disappears after a post reaches a certain age or maybe removing that setting does not "unlock" the edit feature...

                        • 9. Re: How to create new related records for all in found set
                          Interrobang

                          Do you want to rewrite the script and post? Or just mark your first post as correct again?

                          • 10. Re: How to create new related records for all in found set
                            philmodjunk

                            Just mark it. the error is small and well documented in the follow on posts so I see no need to rewrite.