Streamlining Bill Payment Processing with Custom Automation

Overview

The client required an automated solution to create a .txt file in a specified folder within the file cabinet. This file had to detail bill payment transactions where the payment method is “Check” and had to include specific fields as outlined in the Bank of America (BOA) Spec file. The solution needed to process transactions for three accounts.

Challenges Faced 

• Vendor bill and vendor credit records were detailed at the line level within the Bill Payment record, making it impossible to use saved searches to retrieve specific information about vendor bills and vendor credits. 

• If the vendor’s name in the text file exceeds 35 characters, it had to be split correctly without losing any letters. However, this could cause the words to be arranged incorrectly.  

• Ensuring all transaction amounts in the bill payment system were consistently formatted to two decimal places presented a formatting challenge. 

• In the footer line of the text file, the sum of all transaction totals had to be displayed as a whole number with two decimal digits, but without a decimal point. 

• The header line had to accurately indicate the date of file generation. 

Solution Offered by SuiteMatrix 

• Created a new saved search to retrieve all Bill Payment records where the payment method was ‘Check’. 

• Developed a scheduled script to fetch these Bill Payment records. 

• Obtained the search results by loading the saved search. 

• Utilized SQL queries to extract vendor bill and vendor credit details, which were not accessible through the saved search alone. 

• Executed a SuiteQL query to retrieve and map these results accordingly. 

• Implemented a function to handle vendor names exceeding 35 characters:
  
  
  1. If the name is shorter than 35 characters, return the original name.
  
  
  2. If the name exceeds 35 characters, find the last space before the 35th character using lastIndexOf.
  
  
  3. Calculate the number of spaces needed to reach exactly 35 characters before the word that exceeds the limit.
  
  
  4. Insert the calculated number of spaces at the identified position by slicing the sentence, adding the spaces, and concatenating the rest of the sentence.
  
  
• Used .toFixed(2) to ensure all transaction amounts in the bill payment system are consistently formatted to two decimal places. 
  
• For the footer line, multiplied the total of all transactions by 100, rounded the value, and set it in the footer line as a whole number with two decimal digits (without a decimal point). 
  
• Showed the date of file generation in the header by getting the current date, formatting it as ‘YYYYMMDD’, and including it within the header line. 
  
• Generated and saved a file named output.txt in the file cabinet to store the Bill Payment information, containing specific fields as per the BOA Spec File derived from the SQL query results. 
  
• Scheduled the script to run daily. 

Results 

Following the modifications, the script generated the ‘output.txt’ file and stored it in the file cabinet. This file contained the Bill Payment details as created by the scheduled script. The automated solution provided by SuiteMatrix’s NetSuite consultants successfully addressed the client’s needs by accurately retrieving and formatting the necessary bill payment transaction details, ensuring compliance with BOA specifications, and handling complex formatting challenges. This not only improved the efficiency and accuracy of the client’s bill payment processing but also facilitated seamless daily updates through the scheduled script.