10 Replies Latest reply on Jul 26, 2011 7:16 PM by aikiko

    Scripting Extend Found Set

    shkovacs

      Title

      Scripting Extend Found Set

      Post

      Hopefully my explanation below makes sense, and I've included the relevant variables. Having read other theads, that's a "hope", not an expecation! 

       

      I have a master table with event id's, and the dates those events took place. An event that takes place on two different dates is represented by two different records. I specifically want replication in that way for other purposes.  The table has approx a million records, with about 54,000 different event id's taking place over 1300 dates. 

       

      Each record is pretty long, lots of fields, but a shortened example of the master table might be

       

      id 1234      2/10/2006

      id 1234      6/15/2007

      id 3456     2/10/2006

      id 3456      8/9/2008

      id  6789    3/15/2008

       

      I've made a script that prompts the user to enter a date, then executes a find for all events (id's) that took place on that date. Per the above if you entered 2/10/2006, the found set would be

       

      id 1234  2/10/2006

      id  3456  2/10/2006

       

       For clarity (?) the same event id can only happen once on a given date. A found set generally ends up having about 200 records in  the initial find (by date). Ie, of the 54,000 possible event ids that could take place on any date, only about 200 actually take place on a specific date. 

       

      From within the found set (let's say 200 records with unique event id's), I can right click on an individual Event ID, choose extend found set. All the occurences for that rule id (all the records for all dates for that rule id) are added to the found set. If I right clicked on rule id 1234 and choose extend found set, my found set would change to

       

      id 1234  2/10/2006

      id 1234  6/15/2007

      id 3456  2/10/2006

       

      Then ((manually) i'd right click/extend on 3456 and my found set would expand to

       

      id 1234  2/10/2006

      id 1234  6/15/2007

      id 3456  2/10/2006

      id 3456      8/9/2008

       

      Every event id on the original by date found set will have multiple occurrences as I work through the right click/extend.

       

      In effect what I'm doing is using the initial find by date to find the event id's I want to focus on. Then rebuilding the full set of occurrences for those particular event id's. 

       

      The manual extend found set action, using the right click on a particular event id, accomplishes what I'm looking for, but it's manual, one id at a time. Doing that 200 times is pretty tedious. And, ultimately, I will be creating multiple found sets (looking at multiple dates) so I'll have many 200 items sets to work through. 

       

      Once I get an expanded found set in place, I'll want to save that set. Then work within that extended set (another process, not going to try to get into that now!)  On average, and extended found set will end up being about 1500 records. 

       

      How do I script to execute that id by id "extend found set" process?  I'm assuming I'll be able to figure out how to script a save found set once I've finished extended it. 

       

      As an aside, I'm finding it a real challenge to translate between script examples from previous versions of Filemaker, into the script creation process in Filemaker 11. The examples I find in Filemaker Help, and often even in the forum, don't really describe the steps you'd take to accomplish the end results with the filemaker 11 gui? That's particularly frustrating when you're pulling up Filemaker 11 help. You'd hope Filemaker would have translated all their examples to match the new gui? (obviously forum examples are a different matter)  I end up with a lot of dents on my forehead from beating my head on the keyboard!

       

       

        • 1. Re: Scripting Extend Found Set
          LaRetta_1

          Welcome to the world of flat foreheads. :smileyvery-happy:

           

          What if you considered this approach:

           

          Create a global text field (call it gIDs) and relate it to another table occurrence of your main table (call it selfjoin) as:

          Main:: gIDs = selfjoin::: ID.

           

          You would start by typing all the IDs you want to find 'all records for' into the global as multiline (or copy/paste your list into field).  Then script:

           

          Go To Related Records [ based upon self join ; related records only; match only this record ]

           

          You now have all records for all the IDs.  Then Extend your found set to include only the date.

          • 2. Re: Scripting Extend Found Set
            shkovacs

            Hmm.  I find the id's I want all dates for, by starting with one date. It's easy to get the list of id's with a find by that initial date. 

             

            Then using the approach you wrote about I guess I could do the find,  move only the id's from the find results to that selfjoin table. That creates my "list" in the second table. The list would have one instance of the Id's of interest. Then the Go To you described could take place.  So I'd need to figure out how to move results from one table to another (which has to be an obvious thing to find out about? grin)  I thought about putting the list into another table but couldn't quickly see how to find multiple instances of one id from my self join list table, within my master table. 

             

            Is that go to statement exactly what it would look like in the Filemaker 11 script writer/gui? (assuming the names of things are adjusted). Hmm maybe not but I see the concept. So a Go To is (or can be) a find function? I never tried to figure that out, always assumed goto was like a physical action: go to ___   where ____ would be a single thing, then you'd do something to that single thing. 

             

            I woke up this morning thinking to myself why are you breaking this into what are almost different scripts? I want the end result, which is all occurrences of id's where one occurrence of the id meets my date. So the first date match triggers what ID to get all occurrences for, why not create the list of ID's directly into a find? Put your date of interest into a global field. If global matches id date, add id to a find request. Keep matching through the entire record set and build a multiple line Find with one id pre line. That means figuring out how to build a multiple request find with a script.

             

            I guess somewhere in there the choice of approach becomes which runs faster but I'd be happy with one that works. Speed of execution isn't the driver in this instance.  

             

            Question about copy/paste. If you copy a list of cells in excel, or from a text document, and paste into filemaker, the paste (the list) ends up in one field in Filemaker rather than many as it would in Excel. The functionality of copy/pasting that's present in Excel of cells to cells does not seem to be present in Filemaker. Is that correct? i can see how a db manager would require you to write a script to do that.  Excel is not "really" a db manager and Filemaker is not really a spreadsheet. Just want to make sure I wasn't missing something in filemaker. 

             

             

             

             

            • 3. Re: Scripting Extend Found Set
              LaRetta_1

              Using Go To Related Record (GTRR) from one global multiline to another (or same) table is lightening fast.  You don't need another table; just another copy (table occurrence) of it.  How that global field is populated with a multiline doesn't matter ... you can find the records in Excel and copy them or you can find the records in your current table and copy them (per script in my demo file).

               

              You know how to copy records in Excel so I have provided example on copying a set of found records in FileMaker (that you specify in Custom Dialog) and pasting them into the global field and then extending the find using Custom Dialog.   I have heavily commented the script so each step is clear but if you have FMA (Advanced), walk through it with debugger to see it in action.

               

              If you enter the dates as instructed when Custom Dialog asks, you will first get 3 records with a date of 3/15/2008 - IDs 3, 6 and 7.  This part of script could be replaced with manual copy/paste into that gIDs field from Excel.  Then script will ask again for a date (this will be the 2/10/2006 date in which you want to EXTEND and find additional records with that date).  You will end up with this:

               

              (2) ID3

              (3) ID6

              (2) ID7

              ... and added in the extend ... (1) ID1 and (1) ID5.

               

              Link to sample file: Extend Set

               

              If this isn't helping, let us know.  Others may have ideas as well. :smileyhappy:

               

              • 4. Re: Scripting Extend Found Set
                shkovacs

                That's very cool. I'm learning all kinds of things! (I think? lol)  

                 

                So the copy all records only copies what shows on the layout you're looking at. Then you want to make sure you have an empty field (in this case) in another layout, to paste to. That makes sense. And when I paste it will paste into separate cells (fields), I guess those are repeating fields? As opposed to putting all the id's into one really long field? 

                 

                In the file you sent (the scripts) I think there are two different global fields. gDatefind and gId, right? I thought a global field had the same value on all records. Am I incorrect?  It looks like I am copying all the different id's from the find, into the gID fieldS ? So the first gID will have one id, the second gID will have another ID, etc..  

                 

                Is  gID not a global field, or is the definition of a global field different than I understood? hmmmm or does the paste put all those id's into one long field that's global (same long string with a delimiter between the id's,  that same string in all occurrences of the gID). If so does that mean for the purposed of finding, having one long field with multiple delimited elements  works  the same as trying to create a multi line request, with one id in a field, for each request line? 

                 

                Another question. I am doing all this within the same database file, right? If I copy/paste, even though I am pasting to the found set and into a new blank field (the gID field) I am putting this into the underlying master data set, right? Doesn't seem like it would impact anything, I'm not touching my original data fields, but just wondering. 

                 

                Last question (at least that I can see  grin).... I find my first set of id's using the first entered date.

                 

                When I go to "extend" I dont have a second specific date I'm looking for. I may have misled on that. On the second pass, using that set of found id's,  I am then looking for all dates each of those ID's took place on. Is there a catch all symbol to use rather than a specific date, to put in for that second pass on the extend? 

                 

                Not to make this too long but just so it doesn't seem like I am nuts. The first find by date gives me a sub set of ID's. The reason for using a date is just to find that subset. Then for that subset I look for all occurrences of those id's, on any date.

                 

                That seems a little crazy, the original master set has all the occurrences of all ids. But I'm trying to isolate a group of id's (a pattern) within that master set . With a million records/occurrences there's too much "noise" in the master data to find those groups or patterns manually. 

                 

                What I will actually do, when I get that final group of specific id's with all dates, is group them together across dates, creating new events that consist of multiple id's, and on multiple dates. It's back and forth between dates and id's, almost like pivoting. I'm not concerned about doing that in this script. 

                • 5. Re: Scripting Extend Found Set
                  LaRetta_1

                  So you won't be using Excel or some other method to paste the IDs?  Then you can simplify this greatly and you won't even need the global field nor the Extend script step but let me answer your questions about the existing demo first:

                   

                  The selfjoin is not another table.  It is the same table (only another representation of it in the graph).  Then you want to make sure you have an empty field (in this case) in another layout, to paste to.

                  No, we are pasting the record set of IDs into ONE field – a global field in the same table.  

                   

                  If so does that mean for the purposed of finding, having one long field with multiple delimited elements  works  the same as trying to create a multi line request, with one id in a field, for each request line?

                  Yes, single field with multiple entries allows each line within the global to relate independently as if they were records.


                  I am doing all this within the same database file, right? If I copy/paste, even though I am pasting to the found set and into a new blank field (the gID field) I am putting this into the underlying master data set, right? Doesn't seem like it would impact anything, I'm not touching my original data fields, but just wondering. 

                  It doesn’t impact anything … that’s one of the beauties of global fields.  

                  • 6. Re: Scripting Extend Found Set
                    LaRetta_1

                    Now the simplified version.  Join Mainfile:: ID to selfjoin:: ID

                     

                    Shorten script to:

                     

                    Enter Find Mode [ pause so you can type the date you want ] ... or use Custom Dialog, doesn't matter

                    Set Error Capture [ On ]

                    Perform Find []

                    If [ not Get ( LastError ) ]

                    Go To Related Records [ Show only related records; match found set ; from selfjoin using current layout ]

                    End If

                     

                    The GTRR will 'find' all records only for the IDs you have isolated in your found set.  A GTRR ( Go To Related Record ) is a find except it uses relationships to find associated records instead of find criteria.

                     

                    Note: I haven't done extensive testing to verify in all cases but I can notice a HUGE difference in speed ... a GTRR from one global (using match current record only) instead of using a found set (using match all records in current found set) is MUCH MUCH faster.  So, although the above script and process if easier and quicker for the Developer to produce, I believe it is slower for the User who has to wait for the process to run.  Overall personally, I would still use a global unless the record sets are very small (under 1,000 or so). 

                     

                    Here is a file showing the short version without the copy/paste portion Extend Set SHORT

                    • 7. Re: Scripting Extend Found Set
                      shkovacs

                      I'm starting to get it. And I think some of the answers to my questions are implicit in the tables, etc., in the demo you sent. I had to poke around more. 

                       

                      Yes, excel was being used to find that first "list" of id's but it really shouldn't be. Way to much back and forth between programs and data exports. That creates too much potential confusion by the user, and error vectors. They are using Excel because they are used to it, but it's really not the right tool. As we get fielmaker figured out (as here) it will be much more effective. It's interesting to know how to copy/paste from Excel anyway, however. 

                       

                      The results from entering that first date are actually returning not just when an ID happened on the requested date, but given that entered date, then on any other date as well. That what I was trying to make happen.

                       

                      My first "find" was getting every id for the entered date,  but only returning that date's "event". (event being the id on that date). Your demo result is returning all the events (dates)  for every id that also took place on the request date. Ie what I wanted from two steps, in one step.  I need to think about how that is happening a few more times to get how/why clearly. I "see" it but it isn't "my idea" yet.  That's very cool! (what other word is there but cool? hehe)

                       

                      As I look at the other layouts I see how the id's are listing with multiple instances. I need to think about what's happening there more, but I see it. 

                       

                      On the tables I see the second table occurrence as you described. So that second table occurrence has the fields from the original, but no data goes over from the original when you create it? And you populate that second table from the script? I guess that's what happens, or else the script putting data into that table overwrites what was there on creation.

                       

                      That's interesting if I understand it. A table might, or might not, update from another depending on how you set up the relationship? That lets you use a second instance of a table as a sort of temporary holder to let you do other things. (that might be a twisted definition of second table instance use.).

                       

                      In any case I think just the first date entry and completion is doing what I was originally looking for! 

                       

                      Then the second date should let me add another 'full set" using that second date. That second date entry is returning an error message that nothing was found, but that's ok the concept is there. I can figure it out/drill down on it.

                       

                      I'm actually not sure I want to add that second date to this found set, for this application. That first found set from the date entry in your demo seems to be creating the "full" subset I was looking for. Each full subset gets worked on separately. I'll export that found set to do that other work.  When I want to look at a second date, it will be to create a separate full sub set to be worked on separately. But I want to figure out how that second date entry step works anyway. I can see how it would have other applications.

                       

                      This is really helpful, and I'm getting a lot more out of it than just "how do I do this one thing". 

                       

                       

                      • 8. Re: Scripting Extend Found Set
                        LaRetta_1

                        On the tables I see the second table occurrence as you described. So that second table occurrence has the fields from the original, but no data goes over from the original when you create it? And you populate that second table from the script? I guess that's what happens, or else the script putting data into that table overwrites what was there on creation.

                        It isn’t a different table.  It is the same table, just another virtual occurrence of it.  You create it by selecting your main table and then click the ++.  All tables and virtual copies of them are called TOs (table occurrences) in the graph.  They are used to create relationships between tables (even relationships back to themselves, as we did with your situation).   

                         

                         Yes, rockin’ cool stuff! :smileyvery-happy:

                        • 9. Re: Scripting Extend Found Set
                          shkovacs

                          I really appreciate this help. I dont know when, if ever, I would have figured out what you've shown me here. Without the specific background/experience writing scripts and actually building databases, it's like trying to reverse engineer a car engine to build your own, when you dont even know all the parts?!! 

                           

                          I really like these TO's ! 

                           

                          • 10. Re: Scripting Extend Found Set
                            aikiko

                            Dear Wolfbyte,

                            I appear to be having the same forehead flattenning challenges as you!

                            <http://forums.filemaker.com/posts/10e79a9d3f>

                            Did you end up figuring out how to place the Extend Found Set into your script? Or did you use the Tables/self-join/globalID route?

                            Many thanks

                            Aikiko