4 Replies Latest reply on Oct 17, 2013 2:44 AM by JonJ

    FMSA 11 - PHP - how to create a set of records by recid



      FMSA 11 - PHP - how to create a set of records by recid

      Your post

           In my PHP CWP solution, I need to recreate a set of records that has been defined elsewhere and stored as an array of record IDs (or primary key field values). The set could be as large as 500-600 records. They don't have any reliable common criteria I can use for creating a simple find -- they may as well be completely random.

           Getting the records one at a time using GetRecordByID is not feasible of course, because I'd have to hit the database once for each record. (How I wish that method could take an array as the ID parameter!)

           The only find request I can compose to create the set is a compound find request, searching a recid field (or primary key) and creating a new request for each record I want to get. I can do this easily by looping through my array of IDs, creating a new find request with each iteration, and then executing the find. This works, even for the large record sets (though slow), and I will probably end up using this method, but I'm wondering if anyone has solved this problem more elegantly.

           If I were doing this in FileMaker Pro, I would simply place the stored list of return-separated primary key values into a global field and create the desired found set via a relationship and the Get Related Records script step. But I can't conceive of a similar solution that would work in PHP.

           Any thoughts? Thanks!

        • 1. Re: FMSA 11 - PHP - how to create a set of records by recid

               What is keeping you from doing just that? Have that global field and relationship and use them from your PHP solution.

               Global fields are sort of special in IWP/CWP but they do work and are very useful; in short they are global per session, f.e. one CWP findRequest or editRequest. Such a request returns a record or recordset (as you know) and that might be a way to go. I have not tested these methods but I suspect that if you use a simple editRequest setting the global field with your list on a layout with a portal using the relationship, your result-set ( i.e. result->getRecords() ) will  have all the related-records.

               Otherwise a findCommand on that layout with a pre-command script setting the field will likely do the trick, but I guess you won't need that. 


          • 2. Re: FMSA 11 - PHP - how to create a set of records by recid

                 Jan, thanks for your suggestions. A complicating factor I didn't mention in my post is that the records I need to fetch have related data sets of their own that I will be using. So, if I were in FMP, I would set the global field with the list of primary key values, then go to the related records on a different layout -- that layout includes a few portals. 

                 From my understanding of how FileMaker PHP works, that rules out getting the records as a related set, because I can't create a layout that has nested portals.

                 If I could recreate the Go To Related Record functionality in PHP, I'd be golden. But I can't think of how that would be possible.

            • 3. Re: FMSA 11 - PHP - how to create a set of records by recid

                   Ah, slight gotcha, that one. Nested portals are a no go as far as I know.  Ok, assuming all the related records come from the same table, how about this (if only as a a line of thought as it will not be a speedy one): use a findRequest that searches for all records that are marked using a script that uses the selection method you mentioned for FMP. 

                   So; a pre-command script sets a new field Marked for all records to 0 (Select All Records, then use Replace Field Contents). Then use the global field to select all the primary records on the layout that contains the portals. Use the Replace Field Contents on the related records to set the Marked field.  (Replace Field Contents sets the value in ALL related records in the found set after all). The findRequest should return the desired records. (I think, untested)

                   Anyway, not elegant and it will take some time, but it might be an alternative... There might be variations on the theme depending on how the related records are linked to the records of the Primary Keys. Perhaps using the List command getting the IDs of the Related Records, placing them in a global field and return the related records for that field. 

                   These are untested thoughts, and I have no idea of speed (Replace Field Contents takes time especially for large found sets) but they might be an alternative. 

              • 4. Re: FMSA 11 - PHP - how to create a set of records by recid

                     I'd be tempted to let FileMaker to the heavy lifting here, and write a FIleMaker script that will find the records you need, then:

                     1. Set a newPerformScriptCommand with the IDs as the parameter. 
                     2. Execute the script
                     3. The receiving filemaker script finds the records, related data, etc. and packages it up in a format that PHP can use (for example as a text JSON array).
                     3. The filemaker script exits by dumping the resultant text in a global field
                     4. The newPerformScriptCommand returns the found-set at the end of the script. As you use a global field, this is fine...
                          Just use getFirstRecord()->getField("myGlobalField");
                     5. If you are using JSON, simply run json_decode and you've got an array of all the data you need

                     The advantage of this method is that filemaker handles the things it does best. Any change in the schema is easily coped with by your DBA editing the script--the script steps need not concern you, as you only need the end result (JSON in a global).

                     Also, the PHP to FileMaker contact is reduced to two steps, which can be made into a class and re-used for lots of different scripts. Run a script, get the result from the global, process the JSON.

                     Finally, JSON's pretty modular, and so each filemaker record could have a calculated field with the core information as a JSON 'fragment'. For example, for a person record, it would have the {"id":"1234","nameFirst":"Jon","nameSecond":"Jeffery"}, then it's easy to build a complete JSON by using the List() function on that field, and substituting the ¶s with commas, and surrounding it with [ ] .