Profound API - Format Date Output

[James-S]

I have an api which uses database input to output to an api. The column in the table is a date type and the api object element is date.

{
"totalCount": 1,
"data": [
{
"cartID": 1,
"description": "Test List 1",
"type": "L",
"plant": "XX",
"user": "SHERWOOD",
"buyerCode": "JS",
"needByDate": "2021-12-25T00:00:00.000Z"
}
]
}

I only need the needByDate to be "2021-12-25". Since the API output property name only allows the selection of output["data"] there's not way to override the output formatting. Is there another approach I should use?

[Scott Klement]

I'm not sure that I understand.

As far as I can tell from your description (I sure wish you had included the code used to set "needByDate" instead of an English description -- English is VERY imprecise) you are doing this:

Code: Select all

output["data"] = myDatabaseField;

Is there some reason you can't do something like this?

Code: Select all

output["data"] = myDatabaseField.substr(0, 10);

Or perhaps better:

Code: Select all

output["data"] = MyDateFormattingFunction(databaseField);

Please post code and/or screenshots showing what you are doing. Note that while showing us the output of your code is helpful -- it's very hard to tell you why you are getting a particular output (or how to change it) if you don't show us what you are currently doing to achieve that output.

[James-S]

Scott,

One thing I know is when you reply you will reply with honesty. :) When doing low-code API it is hard to describe but I did a bit more online Googling and found a video by Brian May which showed how to see the source code for the low-code. Hope this is what you are asking to see.

Code: Select all

        
 {
  "apiFileID": "baa4b272-05eb-4747-bda2-c85466520de2",
  "routes": [
    {
      "apiRouteID": "d97772a2-f012-4d7e-9126-92bda16d681b",
      "name": "Get List",
      "enableCors": true,
      "method": "get",
      "path": "/shoppingLists",
      "inputs": [
        {
          "type": "string",
          "from": "query",
          "name": "countOnly",
          "description": "Send yes to only return the total count of the matching records",
          "example": "",
          "multipleValues": false
        },
        {
          "type": "date",
          "from": "query",
          "name": "needByDate",
          "description": "Filter by due by date.",
          "example": "",
          "required": false
        }
      ],
      "summary": "Get shopping lists",
      "tag": "ShoppingList",
      "description": "Get shopping lists by criteria",
      "outputdescription": "Returns an array of shopping list and count if requested.",
      "outputs": [
        {
          "type": "integer",
          "name": "totalCount"
        },
        {
          "type": "object",
          "name": "data",
          "multipleValues": true,
          "children": [
            {
              "type": "integer",
              "name": "cartID"
            },
            {
              "type": "string",
              "name": "description"
            },
            {
              "type": "string",
              "name": "type"
            },
            {
              "type": "string",
              "name": "plant"
            },
            {
              "type": "string",
              "name": "user"
            },
            {
              "type": "date",
              "name": "needByDate"
            }
          ]
        },
        {
          "type": "string",
          "name": "buyerCode"
        }
      ],
      "subcategory": "",
      "steps": [
        {
          "text": "if needByDate passed",
          "answers": {
            "plugin": "Conditions:comparison",
            "variable": "input[\"needByDate\"]",
            "type": "truthy",
            "and-or": ""
          },
          "isStructureStart": true
        },
        {
          "text": "Get count of needbyDate list rows",
          "answers": {
            "plugin": "Database:get-record-count",
            "tables": "shopping_cart_header",
            "criteria": "needByDate = ? AND type = 'L'",
            "parameter": "input[\"needByDate\"]",
            "destination": "API output",
            "api_output": "output[\"totalCount\"]"
          }
        },
        {
          "isStructureEnd": true
        },
        {
          "text": "Otherwise",
          "answers": {
            "plugin": "Conditions:else"
          },
          "isStructureStart": true
        },
        {
          "text": "Get count of all list rows",
          "answers": {
            "plugin": "Database:get-record-count",
            "tables": "shopping_cart_header",
            "criteria": "type = 'L'",
            "destination": "API output",
            "api_output": "output[\"totalCount\"]"
          }
        },
        {
          "isStructureEnd": true
        },
        {
          "text": "if not requested countOnly",
          "answers": {
            "plugin": "Conditions:comparison",
            "variable": "input[\"countOnly\"]",
            "type": "!=",
            "value": "'yes'",
            "and-or": ""
          },
          "isStructureStart": true
        },
        {
          "text": "if needByDate passed",
          "answers": {
            "plugin": "Conditions:comparison",
            "variable": "input[\"needByDate\"]",
            "type": "truthy",
            "and-or": ""
          },
          "isStructureStart": true
        },
        {
          "text": "Get list rows by needByDate",
          "answers": {
            "plugin": "Database:get-records",
            "tables": "shopping_cart_header",
            "columns": "cartID,description,type,plant,user,buyerCode,needByDate",
            "criteria": "needByDate = ? AND type = 'L'",
            "parameter": "input[\"needByDate\"]",
            "add_order_by": false,
            "limit": "",
            "skip": "",
            "destination": "API output",
            "api_output": "output[\"data\"]"
          }
        },
        {
          "isStructureEnd": true
        },
        {
          "text": "Otherwise",
          "answers": {
            "plugin": "Conditions:else"
          },
          "isStructureStart": true
        },
        {
          "text": "Get all list rows",
          "answers": {
            "plugin": "Database:get-records",
            "tables": "shopping_cart_header",
            "columns": "cartID,description,type,plant,user,buyerCode,needByDate",
            "criteria": "type = 'L'",
            "add_order_by": false,
            "limit": "100",
            "skip": "",
            "destination": "API output",
            "api_output": "output[\"data\"]"
          }
        },
        {
          "isStructureEnd": true
        },
        {
          "isStructureEnd": true
        }
      ]
    },
    {
      "apiRouteID": "c20979db-330b-48d1-bb28-3be203be087f",
      "name": "Get List by number",
      "enableCors": true,
      "method": "get",
      "path": "/shoppinglist/:cartid",
      "inputs": [
        {
          "name": "cartid",
          "type": "string",
          "from": "path",
          "required": true
        }
      ],
      "summary": "Get list by number",
      "outputs": [
        {
          "type": "object",
          "name": "data",
          "children": [
            {
              "type": "integer",
              "name": "cartID"
            },
            {
              "type": "string",
              "name": "description"
            },
            {
              "type": "string",
              "name": "type"
            },
            {
              "type": "string",
              "name": "plant"
            },
            {
              "type": "string",
              "name": "user"
            },
            {
              "type": "string",
              "name": "buyerCode"
            },
            {
              "type": "date",
              "name": "needByDate"
            }
          ]
        }
      ],
      "subcategory": "",
      "steps": [
        {
          "text": "Get record",
          "answers": {
            "plugin": "Database:get-record",
            "tables": "shopping_cart_header",
            "columns": "cartID,description,type,plant,user,buyerCode,needByDate",
            "criteria": "cartID = ?",
            "parameter": "input[\"cartid\"]",
            "destination": "API output",
            "api_output": "output[\"data\"]"
          }
        }
      ]
    }
  ]
}

[Scott Klement]

Looks like Brian is using code like this to set the API output:

brianex.png (24.07 KiB) Viewed 25402 times

What you can do is tell the low-code plugin to write the output to a work variable, and then you can manipulate the work variable. so change it to write to a work variable instead of API output. I called mine "recordRead"

newWorkVar.png (24.27 KiB) Viewed 25402 times

Then add a new step to the API using the "Set API Output" plugin. Here you can specify each field separately, and code an expression if you like.

newApiOutput.png (28.35 KiB) Viewed 25402 times

[James-S]

Scott,

I was thinking a work variable but wasn't sure how to break down the SQL results. As always, you are always helpful and it is much appreciated by all of us who use this forum. Thank you!