1 2 Previous Next 23 Replies Latest reply on Jun 23, 2011 3:12 PM by aammondd

    Looking for a somewhat more complicated scripting example

    MichaelKaufman

      Title

      Looking for a somewhat more complicated scripting example

      Post

      I am working on my first filemaker DB, and it is going to invlove a fair amount of scripting. Does anyone know of an unlocked DB out there (so I can look at the code) that:

      1) Reads the rows of a data table into an array (or a repetition variable). Perferably, it should give me a way to read only rows that match some criteria.

      2) Create Rows in some new table based on those vaiues.

      Obvioulsy, I plan to do a lot between 1 and 2, but this will let me see what the command syntax looks like. Alternativly, if someone could point me to a reference that contains this information, that would help too. I have looked through the "MIssing Manual" and the FTS, and couldn't find what I was looking for.

      Thanks, Michael

        • 1. Re: Looking for a somewhat more complicated scripting example
          philmodjunk

          You might be able to do this by performing a find ( to find just the "rows that match some criteria" ) then use Import records to move the data into new records in the other table.

          Go To record [first]
          Loop
             If [Table1::Status = "Good"]
                 Set Variable [$I ; value : $I + 1]
                 Set Variable [$Array[$I] ; Table1::Value]
             End If
             Go To Record [next ; exit after last]
          End Loop
          Go To layout [Layout for table 2 (table2)]
          Loop
              Exit Loop If [$I < 1 ]
              New Record/Request
              Set Field [Table2::value ; $Array[$I] ]
              Set Variable [$I ; value: $I - 1 ]
          End Loop

           

          • 2. Re: Looking for a somewhat more complicated scripting example
            MichaelKaufman

            This is great, thanks.  

            A couple of questions:

            > Set Variable [$Array[$I] ; Table1::Value]

            Does this set $Array[$I] to the value of all of the fields in the table, or just to hypothetical field "Value"?

            > Go To layout [Layout for table 2 (table2)]

            Will this actually display the Layout for Table 2 (which I do not want) or just set the script context to table 2?

            Is there a good scripting reference out there so I can stop bugging you?

            THanks, Michael

            • 3. Re: Looking for a somewhat more complicated scripting example
              philmodjunk

              Just the value of the field. You'd need additional variable for additional fields--another reason where Import Records might be much simpler here.

              If you add a freeze window at the start of the script and a Go to Layout [original layout] step at the end, layout 2 will not be visibile during this script's execution and you'll stay on the layout for table 1.

              I'm not "bugged" in the least. I assume you are aware of the books, tutorials and videos on Filemaker that are available?

              • 4. Re: Looking for a somewhat more complicated scripting example
                aammondd

                Im doing something very similar but am using the arrays to house table and fieldnames and constructing the Set Field by Name [$tablefieldfromarray; $arrayvalue] You can use the Go to Layout by calculation to idenify the layout name

                (I store the layout and table names in a reference table as well as fieldnames for that layout::table that require special processing )

                I do my finds in these tables and build a processing array.

                I do this because I have my users data entry screen consist of a table of Global Fields (Often on disparate tables) then I perform any fieldlevel audits or other processing based on the values in these global fields

                This allows me to control the creation of records and apply complex validation logic across multiple tables from a single data entry screen even when no direct relationship exists. 

                I dont have  the script code handy at the moment but I can post it later if you want.

                • 5. Re: Looking for a somewhat more complicated scripting example
                  philmodjunk

                  Sometimes it's also possible to establish a one to one relationship between the records in table 1 and Table 2 with "allow creation of records via this relationship" enabled for Table 2.

                  Then this script step:

                  Set field [Table2::Field1 ; Table1::Field1 ]

                  Will both move the data and create a new record in Table2 the first time such a step is executed in the script (Provided it is executed from a layout based on Table 1). Thus, a block of set field steps like this:

                  set Field [Table2::Field1 ; Table1::Field1 ]
                  Set Field [table2::Field2 ; Table1::Field2 ]
                  Set Field [table2::Field3 ; Table1::Field3 ]

                  will create one new record in Table2, with the values in Fields 1 - 3 copied over to it from Table1.

                  And no variables, repeating or otherwise nor any changes in layout were needed.

                  • 6. Re: Looking for a somewhat more complicated scripting example
                    MichaelKaufman

                    > I assume you are aware of the books, tutorials and videos on Filemaker that are available?

                    I am aware of some, but while all I have seen are helpful in many areas, none seem to go that deep into scripting.

                    > I dont have  the script code handy at the moment but I can post it later if you want.

                    I would love to see it. Thanks.

                    > Sometimes it's also possible to establish a one to one relationship between the records in table 1 and Table 2 with "allow creation of records via this relationship" enabled for Table 2.

                    The problem with this is that I am going to have to do a large amount of processing on the date before I apple the information to table 2. I am reading investment amounts from the database, but I am writing distribution amount into the new table.

                    • 7. Re: Looking for a somewhat more complicated scripting example
                      philmodjunk

                      > Sometimes it's also possible to establish a one to one relationship between the records in table 1 and Table 2 with "allow creation of records via this relationship" enabled for Table 2.

                      The problem with this is that I am going to have to do a large amount of processing on the date before I apple the information to table 2. I am reading investment amounts from the database, but I am writing distribution amount into the new table.

                      What I suggest here may not be possible or may not be practical, but if you have a one to one relationship and also need to do 'additional data processing' you may still be able to make this method work. You'd replace the refrences to Table1::Field1, Table1::Field2, etc with expressions that perform that additional processing before assigning that value to the new record in the second table.

                      • 8. Re: Looking for a somewhat more complicated scripting example
                        MichaelKaufman

                        Exactly. I will create the key fields using the code you have above, and create the distribution amounts manually. 

                        One more related question.

                        Lets say I have a table:

                        Table A

                        --------

                        CustomerID        Amount 

                        1                      100

                        1                      200

                        2                      200

                        2                      300

                        1                      300

                        3                      100

                        And I want to perform some action to end up with:

                        Table B

                        --------

                        CustomerID        Amount 

                        1                      600

                        2                      500

                        3                      100

                        What would be the eaiset way to do it?

                        If I could create queries, I would do a query on Table A of the form Select CustomerID, sum(Amount) from TableA group by CustomerID. Is there some way to do this kind of thing in Filemaker?

                        Assuming I have to use code, is there some more elegent way to do this then just brute force?

                        Thanks.

                        • 9. Re: Looking for a somewhat more complicated scripting example
                          philmodjunk

                          If you create a summary field, sAmountTotal, that computes the total of your amount field, then this expression would return the subtotals you need for table B provided you first sort the records by Customer ID to group them correctly:

                          GetSummary ( CustomerID ; sAmountTotal )

                          It may look like you would need to modify your script with an extra loop to skip forward until you reach the next customer ID. This is not strictly necessary as each time you use Set Field for the same customer ID, you'd be setting the field to the same subtotal amount. Skipping to the next customer ID would however, enable the script to execute more quickly.

                          Please note that you can also create a summary report for a layout based on Table A that can produce a report that looks exactly like Table B, without needing a separate table, or script, at all or a calculation field in Table B can use the Sum function to compute the total without needing to compute and store the value in Table B.

                          • 10. Re: Looking for a somewhat more complicated scripting example
                            MichaelKaufman

                            Thanks for all the help. I think you have given me the information I need. FileMaker is very different to what I am used to, but I think I will get used to it eventually.

                            Are you going to DevCon next month? If so, your first drink is on me.

                            • 11. Re: Looking for a somewhat more complicated scripting example
                              aammondd

                              Yes filemaker is very different but the differences allow for some very powerful things.

                              Calculation fields (with a large amount of functions to choose from) are something rather unique.

                              Find mode is also something rather alien.

                              Relationships instead of joins during query is also incredibly useful

                              The scripting language is very powerful too.

                              Global fields are also a great too.

                              All in all its more of an application building toolset than a database.

                              The import/export handling is still a bit limited for some applications but still very useful.

                              • 12. Re: Looking for a somewhat more complicated scripting example
                                philmodjunk

                                I wish I were. I've wanted too go for many years, but always, either finances or work commitments have prevented me.

                                • 13. Re: Looking for a somewhat more complicated scripting example
                                  MichaelKaufman

                                  > The scripting language is very powerful too.

                                  Yes, but the way you have to enter scripts is going to take a long time to get used to. I wish it was possible to just type in the scripts. This whole things about picking a function and then filling in a list box seems to me to be a lot more work. 

                                  • 14. Re: Looking for a somewhat more complicated scripting example
                                    philmodjunk

                                    We've asked for a text based script editor for years. So far FileMaker has not seen fit to deliver such a capability. :-(

                                    1 2 Previous Next