Skip to content
Postman API Testing Tips

How To Use Postman For V Lookup Apis

API Testing Blog

Emulating VLOOKUP Functionality with Postman for API Testing

Postman is a powerful tool for testing APIs, but it doesn’t natively support VLOOKUP functionality. However, we can emulate this behavior by combining different request types and using the power of JSON manipulation. This guide will demonstrate how to effectively implement VLOOKUP-like operations within your API testing workflows using Postman.

Understanding the VLOOKUP Analogy

In essence, VLOOKUP allows you to search for a specific value within a data set (lookup table) based on a given lookup value. We’ll achieve a similar functionality through API calls and data processing.

Scenario: Lookup Customer Details Based on ID

Let’s assume we have two APIs:

  1. GET /customers: This API returns a list of all customers, each with a unique customer ID.
  2. GET /customer/{id}: This API retrieves details of a specific customer based on the provided ID.

Our goal is to create a workflow in Postman that:

  1. Fetches a list of customers from GET /customers.
  2. Uses the provided customer ID as a lookup value.
  3. Finds the corresponding customer details from GET /customer/{id}.

Step 1: Fetching Customer Data

  • Create a new request: In Postman, create a new GET request for GET /customers.
  • Send the request: Execute the request to retrieve the customer list.
  • Save the response: Save the response body as a “Test Data” variable.

Example:

// This sample JSON is a representative response from the GET /customers call.
[
  { "id": 1, "name": "Alice", "city": "New York" },
  { "id": 2, "name": "Bob", "city": "London" },
  { "id": 3, "name": "Charlie", "city": "Tokyo" },
  { "id": 4, "name": "David", "city": "Paris" }
]

Step 2: Defining the Lookup Value:

  • Add a “Pre-request” script: Within the request setup, click “Pre-request Script” and define a variable lookupId containing the customer ID you want to search for.

Example:

pm.environment.set("lookupId", 2);  // Set the lookup ID to 2

Step 3: Building a Search Logic

  • Add a “Tests” script: This script will iterate through the fetched customer data and compare customer IDs to find a match.

Example:

const customerData = pm.response.json(); // Retrieve the customer data
let foundCustomer = null;


for (let i = 0; i < customerData.length; i++) {
  const customer = customerData[i];
  if (customer.id == pm.environment.get("lookupId")) {
    foundCustomer = customer;
    break;
  }
}


if (foundCustomer !== null) {
  pm.environment.set("customerDetails", foundCustomer);
  console.log("Customer Found:", foundCustomer);
} else {
  console.log("Customer Not Found");
}

Step 4: Fetching Detailed Information

  • Create a new GET request: Create another request for GET /customer/{id}.
  • Dynamically set the ID: Within the URL of this request, replace {id} with ${lookupId}. This dynamically substitutes the ID we want to lookup.

Example:

// URL of the second request
GET https://api.example.com/customer/${lookupId}
  • Send the request (optional): If you need to directly see the detailed customer information, execute this request. The customerDetails variable will already contain the found customer details.

Step 5: Using the Lookup Results

Now you have the customerDetails variable containing the fetched customer information. You can use this data within subsequent Postman requests, assertions, or any other manipulation you need.

Example (Assertion):

pm.test("Customer details match", () => {
  const expectedName = "Bob";
  pm.expect(pm.environment.get("customerDetails").name).to.be.equal(expectedName);
});

Summary: VLOOKUP by API Integration

By following these steps, we leveraged the capabilities of Postman to perform VLOOKUP-like operations on API responses. This approach allows you to combine data from multiple API endpoints, search for specific values within responses, and then use the retrieved information for further testing or actions.

Remember, this is a flexible technique adaptable to various API scenarios. You can modify the scripts and request configurations to suit your specific testing needs.

API Testing Blog