7 Replies Latest reply on Sep 18, 2015 6:50 AM by foxman

    Update a record using CWP

    foxman

      Hi, I've currently got a script that takes JSON data and using the Filemaker API, puts it into my Filemaker database. This works great, I run my php script and it fills my filemaker table with all the pulled in data.The issue I have is when a record in the table needs to be updated, the api skips the record it as there is a unique field in the filemaker database (my chosen key field) as it already exists.

       

      I can get round this by using php to create a csv from the Json, then run a script to import into filemaker matching on the unique field, but it seems more of a hack than a robust solution.

       

      Is there a way of grabbing the internal record id from filemaker when creating it so it can be used to update the record if it already exists?

       

      Or is there another way of updating I haven't though of through the api?

        • 1. Re: Update a record using CWP
          beverly

          can you post what calls you use to bring in the data?

           

          Yes, there are calls to 'update', but you must know the value to test (-recid).

           

          I may go through and test for a match and create new if no match otherwise edit the record (or just don't re-import if you don't need to).

           

          beverly

          • 2. Re: Update a record using CWP
            foxman

            Hi Beverly, thanks for the super fast reply! Here is what is filling the table when the Json is pulled in   

             

              $newWebOrder = $fm->newAddCommand($layout);

                      $newWebOrder->setField('zk__custID__f',$value['user_id']);

                      $newWebOrder->setField('amount',$value['payment_total']);

                      $newWebOrder->setField('zk__orderID__p',$value['order_number']);

                      $newWebOrder->setField('company',$value['billing_address']['company_name']);

                      $newWebOrder->setField('ip_address',$value['ip_address']);

            $result = $newWebOrder->execute();


            If I add another field or update one it will skip over in Filemaker.

            • 3. Re: Update a record using CWP
              mikebeargie

              You just need to code in an if statement in your PHP inside of your loop that's running through the JSON data.

               

              EG

              foreach($jsonData->record as $r) {

                   $id = $r->primaryKey
                   $findCommand = $fm->newFindCommand('YourLayout'); $findCommand->addFindCriterion('PrimaryKey', $id );

                   $result = $findCommand->execute();

                   $count = $result->getFoundSetCount();

                   if($count == 1) {

                        $newEdit = $fm->newEditCommand(StuffGoesHere);

                        $result = $newEdit->execute();

                   } elseif ($count > 1) {

                        //Capture error for more than one record

                   } else {

                        //your existing "new record" code

                   }
              }

              • 4. Re: Update a record using CWP
                beverly

                MB's correct, or you may choose to NOT edit an existing record, but finding these keeps them out of the 'newAddCommand' (thus duplicating the same records).

                • 5. Re: Update a record using CWP
                  mikebeargie

                  And given the sample code you posted above, I'd assume you're already looping through records. An easy if statement like the above shouldn't be too taxing on your system, but another way to do it would be to append the $count above to your JSON data as a new key in a single query (don't really have the methodology together in my pre-coffee haze), and then just read out the $count from your JSON data instead of doing a query separately for each record.

                  • 6. Re: Update a record using CWP
                    Mike Duncan

                    When you add a record, the resulting record is returned in an array that includes it's record ID. After this bit:

                     

                      $result = $newWebOrder->execute();


                    you can error check and then get the record ID like this:

                     

                      if(!FileMaker::isError($result)) {

                        $records = $result->getRecords();

                        $my_recid = $records[0]->getRecordID();

                      } else {

                       // you got an error

                      }


                    Does that help?

                    Mike

                    • 7. Re: Update a record using CWP
                      foxman

                      Mike and Bev, thank you so much!

                       

                      Mike I had to add that last bit (in Bold) to your original code and it works a treat!

                       

                      foreach($jsonData->record as $r) {

                           $id = $r->primaryKey
                           $findCommand = $fm->newFindCommand('YourLayout'); $findCommand->addFindCriterion('PrimaryKey', $id );

                           $result = $findCommand->execute();

                           $count = $result->getFoundSetCount();


                                  $record = $result->getRecords();

                                  $recID = $record[0]->getRecordId();


                           if($count == 1) {

                                $newEdit = $fm->newEditCommand(StuffGoesHere);

                                $result = $newEdit->execute();

                           } elseif ($count > 1) {

                                //Capture error for more than one record

                           } else {

                                //your existing "new record" code

                           }
                      }

                       

                      Was struggling to pull the RecordId from the getRecords() returned array, that bit solves it! Legend!