Announcement

Collapse
No announcement yet.

API call expected date format?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • API call expected date format?

    I'm trying to make an API call (List>Leads), which aside from this current issue was working fine (well, sort of) when my where clause was ["type" => "currentMonth"], but now I'm attempting to use ["type" => "between" , "values" => from, to ], but I have zero idea what format is expected of me? I just assumed YYYY-MM-DD. It's not failing, or throwing an error, it's just giving me everything. It also happens to be returning leads that don't even have anything in the createdAt field (failed import), and it appears to be letting deleted leads in in spite of the "isFalse","deleted".

    Can somebody give me a bit of guidance here? The API is working, it's just not doing what I expect it to. I feel like my where clauses are more of a suggestion than a rule. Actually, they're just being ignored entirely.

    I'm wondering if I should just add hard-coded day, month, and year fields to the Lead entity and sidestep the date format issue entirely, but that doesn't help me anyway because it's giving me deleted leads too. I'm going to try a simpler query that it could not possibly get wrong (assuming it even tries).

    Code:
    require_once('vendor/autoload.php');
    
    use Espo\ApiClient\Client;
    
    $url     = REDACTED;
    $headers = [ "Content-Type:application/json",
                 "Accept:application/json",
                 "X-Api-Key:REDACTED" ];​
    
    $month   = $_GET['month'];
    
    if(isset($_GET['year'])){
        $year      = $_GET['year'];
        // format incoming year/month into YYYY-MM-01, then subtract a day
        $fromDate = date_format(
                        date_sub(
                            date_create( $year."-".str_pad($month, 2, '0', STR_PAD_LEFT)."-01" ),
                            date_interval_create_from_date_string("1 day")
                        ),
                        "Y-m-d"
                    );
    
        // format incoming year/month into YYYY-(MM+1)-01
        $month = $month + 1;
        $toDate   = date_format(
                        date_create(
                            $year."-".str_pad($month,2,'0', STR_PAD_LEFT)."-01"
                        ),
                        "Y-m-d"
                    );    
    
    }
    
    if($month == "currentMonth"){
    
        $data =    [
            "select" =>    [
                "createdAt",
                "howDidTheyHearAboutUs",
                "showedUpForCall",
                "showedUp4Assessment",
            ],
            "where"  => [
                [
                    "type"     => "and",
                    "value"    => [
                        [
                            "type"         => $month,
                            "attribute" => "createdAt",
                        ],
                        [
                            "type"        => "isFalse",
                            "attribute" => "deleted",
                        ],
                    ],
                ],
            ],
        ];
    
    }else if(isset($month)&&isset($year){
    
        $data =    [
            "select" =>    [
                "createdAt",
                "howDidTheyHearAboutUs",
                "showedUpForCall",
                "showedUp4Assessment",
            ],
            "where"  => [
                [
                    "type"     => "and",
                    "value"    => [
                        [    
                            "type"         => "between",
                            "attribute" => "createdAt",
                            "value"        => [
                                $fromDate,
                                $toDate
                            ]
                        ],
                        [
                            "type"        => "isFalse",
                            "attribute" => "deleted",
                        ],
                    ],
                ],
            ],
        ];
    
    }
    
    $payload = json_encode($data);
    $ch      = curl_init( $url );
    curl_setopt( $ch, CURLOPT_POSTFIELDS, $payload );
    curl_setopt( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_Setopt( $ch, CURLOPT_CUSTOMREQUEST, "GET");
    $response = curl_exec($ch);
    curl_close($ch);
    
    echo json_encode($response);
    
    ?>
    ​
    Last edited by GordonZed; 03-25-2023, 10:33 PM.

  • #2
    It suddenly dawns on me that the fact that I'm sending the data as POSTFIELDS when it's a GET request might be the reason they're having zero affect on anything.

    Rookie mistake *facepalms* But then again, so was using php_curl.

    I'm going to do some tweaking and report my findings. In the meantime, if someone could provide some clarity on the expected date format, that would still be much appreciated.

    Thanks.

    Comment


    • #3
      Turned params into a query string appended to the end of the URL, and it's still returning every lead, including deleted ones, and selecting all columns, not just the few I defined in my parameters.

      Please tell me I'm making some incredibly obvious error because I've been working too long, and it's going to be as sinple as editting a couple lines or something.

      EDIT:

      Ran this much simpler call just now, same result

      Click image for larger version

Name:	image.png
Views:	157
Size:	29.3 KB
ID:	89966
      Last edited by GordonZed; 03-25-2023, 10:07 PM.

      Comment


      • #4
        Try separating select columns by comma in a single string (stated in the docs).

        Inspect what the browser sends (F12 > Network) when you filter record on the list view. The front-end uses the same API.

        Comment


        • #5
          That did it! I don't know how I missed this, but as per the manual, select does indeed expect a string of comma separated values, and it looks like my assumption of YYYY-MM-DD for the date format was correct. Thanks for pointing me in the right direction.

          Click image for larger version

Name:	image.png
Views:	152
Size:	31.6 KB
ID:	89991

          Comment

          Working...
          X