Merging data from multiple custom records

1. Identify the Data Structure

Before merging data, it’s crucial to understand the structure of the records and the response format. You need to know:

• What custom records need to be merged (e.g., GRW007 Work Effort, GRW007 Work Effort Status, etc.)
• The format of the merged data (e.g., JSON or any other structure)
• The required fields from each custom record

2. Use SuiteScript 2.1 API to Load and Search Custom Records

You can use N/record and N/search modules to load and search for custom record data.

define(['N/record', 'N/search'], function(record, search) {

  function getMergedData() {
    // Create a search to find all necessary records
    let workEffortSearch = search.create({
      type: 'customrecord_grw007_work_effort',
      columns: ['internalid', 'name', 'status'],
      filters: [
        ['status', 'anyof', 'Active']
      ]
    });

    let resultSet = workEffortSearch.run();
    let mergedData = [];

    resultSet.each(function(result) {
      let workEffortId = result.getValue({ name: 'internalid' });
      let workEffortName = result.getValue({ name: 'name' });

      // Load additional data from another custom record
      let statusRecord = record.load({
        type: 'customrecord_grw007_work_effort_status_type',
        id: workEffortId
      });

      let status = statusRecord.getValue('status');

      // Merge relevant data into a single object
      mergedData.push({
        workEffortId: workEffortId,
        name: workEffortName,
        status: status
      });

      return true;
    });

    return mergedData; // This would return the merged data array
  }

});

3. Define the Response Format

To ensure the response format remains consistent, you need to structure the data in a predefined format, such as a JSON object or array.

let mergedData = {
  "workEfforts": []
};

// Example of merging data from multiple records
mergedData.workEfforts.push({
  "workEffortId": workEffortId,
  "workEffortName": workEffortName,
  "status": status
});

4. Ensure Data Consistency

When merging data, it’s important to ensure the integrity of the response format. If you are merging fields from different record types (e.g., Work Effort and Work Effort Status), ensure that the merged data maintains a consistent structure across all records.

For example, if you are merging data from two custom records (GRW007 Work Effort and GRW007 Work Effort Status), each object in the response might look like this:

{
  "workEffortId": 12345,
  "workEffortName": "Task 1",
  "status": "In Progress"
}

5. Return Data as JSON

If you are returning the merged data from a Suitelet or RESTlet, you will typically want to return it as a JSON object.

Example using Suitelet:

define(['N/http', 'N/response', 'N/log'], function(http, response, log) {

  function onRequest(context) {
    let mergedData = getMergedData();  // Assume getMergedData() merges data from multiple records

    // Respond with merged data as JSON
    response.write({
      output: JSON.stringify(mergedData)
    });
  }

  return {
    onRequest: onRequest
  };
});