Overview
Generating a bank payment text file from Joget is a common enterprise workflow requirement. A form captures the payment details, the BeanShell tool validates the values, pulls invoice rows from the Joget database, and writes a text file to a secure shared folder for the bank or finance integration team.
This version uses generic field IDs, table names, paths, and placeholders so the pattern is safe to reuse publicly.
How It Works
- Read payment fields from Joget hash variables.
- Validate important values before creating the file.
- Query invoice rows using a parameterized SQL statement.
- Write header, transaction, and invoice lines into a text file.
- Close the database, result set, and writer in inally to avoid resource leaks.
Where to Use in Joget
Use this in Workflow Builder as a BeanShell Tool after the payment request is approved and ready for export.
Full Code
import java.io.File;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
public void createPaymentTextFile() {
DateTimeFormatter fileDate = DateTimeFormatter.ofPattern("yyyyMMdd");
DateTimeFormatter fileTime = DateTimeFormatter.ofPattern("HHmmss");
LocalDateTime now = LocalDateTime.now();
String organizationCode = "#form.payment_request.organization_code#";
String fileReference = "#form.payment_request.file_reference#";
String notificationEmail = "#form.payment_request.notification_email#";
String debitAccount = "#form.payment_request.debit_account#";
String debitCurrency = "#form.payment_request.currency_code#";
String debitCountry = "#form.payment_request.debit_country#";
String orderingPartyName = cleanText("#form.payment_request.ordering_party_name#");
String orderingPartyAddress = cleanAddress("#form.payment_request.ordering_party_address#");
String orderingPartyBic = "#form.payment_request.ordering_party_bic#";
String beneficiaryEmail = "#form.payment_request.beneficiary_email#";
String beneficiaryIban = "#form.payment_request.beneficiary_iban#";
String beneficiaryName = cleanText("#form.payment_request.beneficiary_name#");
String beneficiaryAddress = cleanAddress("#form.payment_request.beneficiary_address#");
String beneficiaryBankAddress = cleanAddress("#form.payment_request.beneficiary_bank_address#");
String beneficiaryBic = "#form.payment_request.beneficiary_bic#";
String beneficiaryCountry = "#form.payment_request.beneficiary_country#";
String beneficiaryCity = cleanText("#form.payment_request.beneficiary_city#");
String paymentMode = "#form.payment_request.payment_mode#";
String paymentAmount = "#form.payment_request.payment_amount#";
String paymentDetails = cleanPaymentDetails("#form.payment_request.payment_details#");
String chargeType = "#form.payment_request.charge_type#";
String purposeCode = "#form.payment_request.purpose_code#";
String paymentType = "#form.payment_request.payment_type#";
String invoiceReference = "#form.payment_request.invoice_reference#";
String voucherNumber = "#form.payment_request.voucher_number#";
if (!isValidEmail(beneficiaryEmail)
|| !isValidEmail(notificationEmail)
|| !isAlphaNumeric(fileReference, 15)
|| !isAlphaNumeric(debitAccount, 23)
|| !isCode(debitCurrency, 3)
|| !isCode(debitCountry, 3)
|| !isCode(paymentMode, 2)
|| !isAlphaNumeric(beneficiaryIban, 40)
|| !isCode(beneficiaryCountry, 3)
|| !isNumeric(invoiceReference, 10)) {
LogUtil.warn("payment-export", "Payment text file was not created because validation failed.");
return;
}
String fileName = organizationCode + "_PAYMENT_" + fileDate.format(now) + "_" + fileTime.format(now) + ".txt";
File outputFile = new File("\\\\your-file-server\\secure-payment-output\\" + fileName);
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
PrintWriter writer = null;
try {
DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
con = ds.getConnection();
ps = con.prepareStatement(
"select c_invoice_number, c_invoice_date, c_description, c_invoice_id, c_amount " +
"from app_fd_payment_invoice where c_payment_request_id = ?"
);
ps.setString(1, invoiceReference);
rs = ps.executeQuery();
writer = new PrintWriter(outputFile, "UTF-8");
writer.println("FILE_HEADER," + fileReference + "," + fileDate.format(now) + "," + notificationEmail);
writer.println("TRANSACTION_HEADER," + fileReference + "," + debitAccount + "," + debitCurrency + "," + debitCountry + "," + orderingPartyName + "," + orderingPartyAddress + "," + orderingPartyBic);
writer.println("TRANSACTION_DETAIL," + fileReference + "," + beneficiaryName + "," + beneficiaryEmail + "," + beneficiaryIban + "," + paymentMode + "," + paymentAmount + "," + beneficiaryAddress + "," + beneficiaryCountry + "," + beneficiaryBankAddress + "," + beneficiaryBic + "," + beneficiaryCity + "," + paymentDetails + "," + chargeType + "," + purposeCode + "," + paymentType + "," + invoiceReference);
while (rs.next()) {
writer.println("INVOICE_INFO," + invoiceReference + "," + rs.getString("c_amount") + "," + rs.getString("c_invoice_number") + "," + rs.getString("c_invoice_date") + "," + cleanPaymentDetails(rs.getString("c_description")) + "," + rs.getString("c_invoice_id") + "," + voucherNumber);
}
LogUtil.info("payment-export", "Payment text file created: " + fileName);
} catch (Exception e) {
LogUtil.error("payment-export", e, "Unable to create payment text file.");
} finally {
try { if (writer != null) writer.close(); } catch (Exception e) { }
try { if (rs != null) rs.close(); } catch (Exception e) { }
try { if (ps != null) ps.close(); } catch (Exception e) { }
try { if (con != null) con.close(); } catch (Exception e) { }
}
}
public String cleanText(String value) {
return value == null ? "" : value.replaceAll("[^a-zA-Z0-9\\s]", "").trim();
}
public String cleanAddress(String value) {
return value == null ? "" : value.replaceAll("[^\\w\\-\\s:+_]", " ").trim();
}
public String cleanPaymentDetails(String value) {
return value == null ? "" : value.replaceAll("[^a-zA-Z0-9 _-]", " ").trim();
}
public boolean isValidEmail(String value) {
return value != null && Pattern.compile("^[\\w!#$%&'*+/=?`{|}~^-]+(?:\\.[\\w!#$%&'*+/=?`{|}~^-]+)*@(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,6}$").matcher(value.trim()).matches();
}
public boolean isAlphaNumeric(String value, int maxLength) {
return value != null && Pattern.compile("^[a-zA-Z0-9]{1," + maxLength + "}$").matcher(value.trim()).matches();
}
public boolean isCode(String value, int maxLength) {
return value != null && Pattern.compile("^[A-Z]{1," + maxLength + "}$").matcher(value.trim()).matches();
}
public boolean isNumeric(String value, int maxLength) {
return value != null && Pattern.compile("^[0-9]{1," + maxLength + "}$").matcher(value.trim()).matches();
}
createPaymentTextFile();
Example Use Cases
- Export approved payment requests to a secured network folder.
- Generate bank-upload text files from Joget form data.
- Create finance integration files with header, detail, and invoice sections.
- Validate payment fields before file generation.
Customization Tips
- Replace pp_fd_payment_invoice with your sanitized Joget child-table name.
- Replace the form hash variable IDs with your own form field IDs.
- Update the output line format to match your bank or middleware specification.
- Store file locations and connection settings outside the script when possible.
- Keep the output folder restricted to the finance or integration service account.
Key Benefits
- Keeps payment export logic inside the workflow.
- Prevents invalid data from reaching the bank file.
- Uses prepared statements for safer SQL.
- Separates header, transaction, and invoice lines clearly.
- Closes resources properly after the file is generated.
Security Note
Do not publish or hard-code real server paths, IP addresses, database usernames, passwords, table names, account numbers, bank identifiers, or organization-specific field IDs. Use Joget-managed data sources, environment variables, credentials managed by your platform, or protected plugin properties.
Final Thoughts
This approach is useful when Joget must prepare a structured payment file for another system. Keep the public example generic, then map the placeholders to your real finance fields only inside your private environment.
