7 Replies Latest reply on Jun 6, 2013 11:03 AM by philmodjunk

    Two Questions Revolving around bulk changes to data in tables

    MrMLK

      Title

      Two Questions Revolving around bulk changes to data in tables

      Post

           I have a schema that look like this

            

           Table 1:

           Name : Text

           SelectTemp: Number

            

           Table 2:

           Table 2 Key:

           Name: Text

            

           I want to insert new rows into table 2 where temp =1 in table 1. I have a form set up where users can select all of the rows they want and then hit a button. At that time I would like to create all of the rows in table 2.

            

           My two questions are:

            

           1) Is there a simple way to set the value of SelectTemp of all rows of table 1 to 0. I tried using ExecSql and I don't seem to be doing it correctly.

           2) Same thing for inserting the rows. I assume that there is a way to itterate over the table and do the inserts, but I can't seem to get it right.

           If there is some sample code in some database I could be pointed to, I could probably work it out from there.

           Thanks.

        • 1. Re: Two Questions Revolving around bulk changes to data in tables
          philmodjunk

               1) ExecuteSQL only supports SELECT queries so it cannot be used to modify data in your table. But take a look at Replace Field Contents--which is both a menu option in the Edit menu and also a script step.

               2) Loops in FileMaker are very simple. You use Loop-End Loop to define the block of code through which to loop and either Exit Loop If or Go to Record/Request/Page [ Next ; Exit after last] to set up an exit condition for exiting the loop. If you are trying to set up a For-Next type of loop, use set Variable to increment a $Variable and then use Exit Loop If to test for when the value in the variable has reached a value that should cause the looping to stop.

               For those interested in more of a challenge, scripts in FileMaker can also loop recursively.

               You may want to post the script you have attempted so that others can show you where you may have gone wrong with it.

               To post a script to the forum:

                 
          1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
          2.      
          3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (If you do this, please read the posted script and insert a few returns where needed to start each step on its own line.)
          4.      
          5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
          6.      
          7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
          • 2. Re: Two Questions Revolving around bulk changes to data in tables
            MrMLK

                 Phil,

                 I understand about the looping, I'm just not sure about how to read records or create new ones. Everything I have done so far involves going to layouts, but I don't want to do that. I'm trying to do it strictly by scripting.

                 Do any of the sample databases do anything like this that I could look at?

                  

                 Thanks.

            • 3. Re: Two Questions Revolving around bulk changes to data in tables
              philmodjunk

                   Your description of what you want to do is too vague for me to suggest anything.

                   In FileMaker, everything is tied to layouts, the Tutorial: What are Table Occurrences? specified for the layout in Layout Setup... | Show Records From and from there to the underlying table. The layout's table occurrence is what establishes the needed "context" for FileMaker to determine how established relationships will affect what data is accessible at any given time during a script's execution.

                   Thus, you typically use go to layout to select a specific layout and from there can create new records, delete them, edit data etc via the script and so forth...

                   And yes, this can be a bit awkward at times and if you have script triggers set up on your layouts, care must be taken to ensure that a change in layouts did not cause a script to be performed that should not be performed. Some times you can set up a "utility" layout that is hidden from the user and that does not have such triggers and other times you have to "disable" your triggers by setting a global variable to a value that your triggered scripts check before doing anything significant.

              • 4. Re: Two Questions Revolving around bulk changes to data in tables
                MrMLK

                     I was trying to save you from the specifics, but maybe that isn't possible.

                     I have a tables called Investors, Deals, Investoments, I_Distribtions D_Distributions

                     Invesments is used to related Investors to Deal and D_Distributions are associated with Deals and I_Distributions are assciated with Investors

                     Every time I create a record in D_Distributions, I want to create a number of records in I_DIstributions, one for each investor in the Deal

                      My Logic would be something like

                     Loop

                         For each Record in Investment for the current Deal

                        Create a record in I_Distributions with the correct information.

                     End Loop

                     So, my question is, If I am in the Investments layout (so I can do the loop) how do I create a record in  I_Distributions? Do I need to keep switching  layouts? I suppose I could do something like:

                     Loop

                         For each Record in Investment for the current Deal

                         Store the information for the Investor

                     End Loop

                     Loop

                        For each record you have stored

                        Create a record in I_Distributions with the correct information.

                     End Loop

                      
                     But that seems overly cumbersome. This is why I was trying to do everything as a script, but I guess that itsn't posibble.
                      
                     Does this make more sense?
                • 5. Re: Two Questions Revolving around bulk changes to data in tables
                  philmodjunk

                       Everything you describe can be scripted, but not only did I need the details you have now posted, I need more detail than what you have shared so far.

                       My best guess is that you have these relationships:

                       D_Distributions>-----Deals>-----Investors--<Investments---<I_Distributions

                       Investors::__pkInvestorID = Deals::_fkInvestorID
                       Investors::__pkInvestorID = Investments::_fkInvestorID

                       Deals::__pkDealID = D_Diestributions::_fkDealID

                       Investments::__pkInvestmentID = I_Distributions::_fkInvestmentID

                       Of course, your field names may be different and for an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       Let me know if I have this correct. If not, you may want to post a portion of a screen shot of the relationships that you actually have here.

                  • 6. Re: Two Questions Revolving around bulk changes to data in tables
                    MrMLK

                           Phil,

                         I think I worked it out:

                                        ----

                                        Set Variable [ $DistributionID; Value:Deal Distribution::Distribution ID ]

                         Set Variable [ $TotalAmount; Value:Deal Distribution::Amount ]

                         Set Variable [ $DateSent; Value:Deal Distribution::Date Sent ]

                         Go to Layout [ “Investor Utility” (Investor) ]

                         Go to Record/Request/Page [ First ]

                         Loop

                             If [ Investor::Investment Status = "funded" ]

                                 Set Variable [ $InvestorID; Value:Investor::Investor ID ]

                                 Set Variable [ $Percent; Value:Investor::Investment Percentage ]

                                 Go to Layout [ “Investor Distribution” (Investor Distribution) ]

                                 New Record/Request

                                 Set Field [ Investor Distribution::Investor ID; $InvestorID ]

                                 Set Field [ Investor Distribution::Distribution ID; $DistributionID ]

                                 Set Field [ Investor Distribution::Date Sent; $DateSent ]

                                 Set Field [ Investor Distribution::Amount; $TotalAmount * $Percent ]

                                 Go to Layout [ “Investor Utility” (Investor) ]

                             End If

                             Go to Record/Request/Page   [ Next; Exit after last ]

                         End Loop

                         Go to Layout [ original layout ]

                         -----

                         My concerns (which I didn't articulate very well) were mostly related to what would happen after I went to the next record.  I wasn't sure about all of the hopping around between layouts.

                         In any case, thanks for the help.

                          

                    • 7. Re: Two Questions Revolving around bulk changes to data in tables
                      philmodjunk

                           Jumping back and forth between layouts is often necessary, but be careful of any script triggers that might be tripped by the change in layout. And you may want to start your script with a Freeze Window step.

                           I would perform a find on the “Investor Utility" layout to limit the found set to only "funded" records. That will reduce the number of records you need to loop through and the script will complete a bit faster.

                           And while the script would be a bit more complex, it's also possible to use two loops in sequence instead of one loop nested inside the other. Loop one would store all the Investor ID's and Percents in list of values in two variable. Then the script changes layouts and loop two loops through these values, creating the new records before finally returning to the original layout. The difference here is that you only change back and forth between layouts a single time.