We’re syncing purchase order records from our ERP system into ServiceNow San Diego using Integration Flow, and the workflow is blocking approvals due to missing foreign keys. Specifically, when a PO references a supplier that doesn’t exist in our supplier table (u_suppliers), the sync fails and the approval workflow gets stuck. We’re processing about 300 POs daily, and roughly 15-20% fail due to this foreign key issue.
The error we see:
Foreign key constraint violation: u_supplier_id
Record: PO-2024-8847
Referenced table: u_suppliers, Key: SUP-9234 not found
The supplier ID assignment seems inconsistent between systems-our ERP uses one numbering scheme, and ServiceNow uses another. We have a mapping table, but it’s not comprehensive. The approval workflow blocks when the supplier lookup fails, leaving POs in a pending state that requires manual intervention. How have others handled foreign key validation in Integration Flows to prevent workflow blockages?
Tom, that makes sense about the sync order. But we can’t always control when the ERP sends data-it’s event-driven. Jen’s suggestion about validation logic sounds more practical for our real-time integration. Can I use a Script action in the Integration Flow to do the supplier lookup and create missing records on-the-fly?
Don’t forget to update your approval workflow to handle pending suppliers. Add a condition in your workflow that checks if supplier.status=‘Pending Validation’ and routes those POs to a special approval path that requires procurement manager sign-off. This prevents auto-approval of POs with incomplete supplier data while still allowing the workflow to progress. We implemented this and reduced manual intervention by 80%.
Yes, absolutely. Add a Script step in your Integration Flow after receiving the PO data but before the Create Record action. In the script, query u_suppliers using the incoming supplier ID from your mapping table. If it returns empty, insert a new supplier record with status=‘Pending Validation’ and use that sys_id for the PO foreign key. Then trigger a notification to procurement to complete the supplier details. This way, the PO sync succeeds and the workflow proceeds, while flagging incomplete supplier data for follow-up.
We faced this exact scenario. The root cause is usually a timing issue-suppliers are created in the ERP after POs are generated, but the sync order is wrong. Implement a two-pass sync strategy: first sync suppliers, then sync POs. Use a scheduled import job that runs supplier sync 30 minutes before PO sync. Also, update your mapping table regularly through an automated reconciliation process that compares ERP supplier IDs to ServiceNow sys_ids weekly.
Consider implementing a supplier staging table (u_suppliers_staging) that acts as a buffer. Your Integration Flow writes all incoming suppliers there first, then a separate scheduled job validates and promotes them to u_suppliers. This decouples supplier creation from PO sync and gives you a controlled environment to handle foreign key validation, deduplication, and data quality checks before committing to the production supplier table.
Here’s a comprehensive solution that addresses all three focus areas:
1. Foreign Key Validation in Integration Flow:
Modify your Integration Flow to include validation before record creation. Add these steps in sequence:
- Step 1: Lookup Supplier - Use a “Lookup Records” action to query u_suppliers table:
var gr = new GlideRecord('u_suppliers');
gr.addQuery('erp_supplier_id', source.supplier_id);
gr.query();
if (!gr.next()) { /* Supplier not found - handle below */ }
- Step 2: Create Missing Supplier - If lookup fails, insert a placeholder:
var supplier = new GlideRecord('u_suppliers');
supplier.initialize();
supplier.erp_supplier_id = source.supplier_id;
supplier.name = 'Pending: ' + source.supplier_name;
supplier.status = 'pending_validation';
var supplier_sys_id = supplier.insert();
- Step 3: Assign Foreign Key - Use the found or newly created sys_id for the PO’s u_supplier_id field.
2. Purchase Order Sync Enhancement:
Implement a robust sync strategy that handles the 15-20% failure rate:
-
Sync Order Management: If possible, configure your ERP integration to send supplier updates before PO updates. Use message sequencing or timestamps to enforce order.
-
Mapping Table Maintenance: Create a scheduled job (daily at 2 AM) that reconciles your mapping table:
- Query all unique supplier IDs from recent PO failures
- Compare against ERP supplier master
- Auto-populate missing mappings where possible
- Flag unresolvable mappings for manual review
-
Exception Handling: Create a custom table (u_po_sync_exceptions) to log failed syncs with full context. Include: PO number, supplier ID, error message, timestamp. Build a dashboard for procurement to review and resolve these daily.
3. Supplier ID Assignment Logic:
Standardize how supplier IDs are assigned and mapped between systems:
-
Mapping Table Structure: Ensure your mapping table (u_supplier_mapping) has these fields:
- erp_supplier_id (string, indexed)
- snow_supplier_sys_id (reference to u_suppliers)
- mapping_status (choice: active, pending, deprecated)
- last_verified (date/time)
-
Auto-Mapping Logic: When creating placeholder suppliers, attempt to match by name similarity (using GlideStringUtil.getLevenshteinDistance) against existing suppliers to prevent duplicates.
-
Supplier Enrichment Workflow: For suppliers created with status=‘pending_validation’, trigger a workflow that:
- Sends notification to procurement with ERP supplier ID and name
- Provides UI Action to either: (a) complete supplier details, or (b) map to existing supplier
- Updates mapping table once resolved
- Reprocesses any POs waiting on that supplier
Approval Workflow Modifications:
Update your purchase order approval workflow to handle foreign key scenarios:
- Add a condition at workflow start: `if (current.u_supplier.status == ‘pending_validation’)
- Route these POs to a special approval lane requiring procurement manager review
- Add a UI notification on the PO form indicating incomplete supplier data
- Prevent auto-approval for POs with pending suppliers
Monitoring and Alerts:
Set up proactive monitoring:
- Create a scheduled report showing daily foreign key failure rate
- Alert if failure rate exceeds 25% (indicating systemic issue)
- Dashboard widget showing count of pending validation suppliers
- Weekly email to procurement with unresolved supplier mappings
This approach ensures your Integration Flow validates foreign keys before database operations, maintains PO sync reliability through comprehensive error handling, and standardizes supplier ID assignment with automated reconciliation. The workflow modifications prevent approvals from blocking while still maintaining data quality controls.