如何将SAP Business One物料主数据的多个UDF字段值填充至BOM表头UDF字段？

核心需求 #

将物料主数据的5个UDF字段值合并填充到BOM表头的Drawing no UDF字段中，替代当前仅能获取单个值的问题。

方案一：批量更新脚本（DI API） #

适用于一次性批量更新现有BOM数据，以C#为例：

using SAPbobsCOM;
using System;
using System.Linq;

namespace BOMUDFMerge
{
    class Program
    {
        static void Main(string[] args)
        {
            Company oCompany = new Company();
            // 配置连接参数，替换为你的实际环境信息
            oCompany.Server = "你的数据库服务器地址";
            oCompany.DbServerType = BoDataServerTypes.dst_MSSQL2019;
            oCompany.CompanyDB = "公司数据库名";
            oCompany.UserName = "SAP登录账号";
            oCompany.Password = "SAP登录密码";
            oCompany.LicenseServer = "许可证服务器地址:30000";

            int connectStatus = oCompany.Connect();
            if (connectStatus != 0)
            {
                ErrorInfo err = oCompany.GetLastError();
                Console.WriteLine($"连接失败: {err.Message}");
                return;
            }

            try
            {
                Recordset oRs = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
                // 替换U_Draw1~U_Draw5为你实际创建的物料主数据UDF字段名
                oRs.DoQuery(@"
                    SELECT T0.DocEntry, T0.ItemCode, T1.U_Draw1, T1.U_Draw2, T1.U_Draw3, T1.U_Draw4, T1.U_Draw5
                    FROM OITT T0
                    INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
                ");

                BOM oBOM = oCompany.GetBusinessObject(BoObjectTypes.oBOMs);
                while (!oRs.EoF)
                {
                    // 合并非空UDF值，用逗号分隔（可自定义分隔符）
                    string mergedDrawings = string.Join(", ", 
                        new string[] { 
                            oRs.Fields.Item("U_Draw1").Value?.ToString() ?? "",
                            oRs.Fields.Item("U_Draw2").Value?.ToString() ?? "",
                            oRs.Fields.Item("U_Draw3").Value?.ToString() ?? "",
                            oRs.Fields.Item("U_Draw4").Value?.ToString() ?? "",
                            oRs.Fields.Item("U_Draw5").Value?.ToString() ?? ""
                        }.Where(s => !string.IsNullOrWhiteSpace(s)));

                    if (oBOM.GetByKey(oRs.Fields.Item("DocEntry").Value))
                    {
                        // 替换U_DrawingNo为BOM表头的`Drawing no`对应的UDF字段名
                        oBOM.UserFields.Fields.Item("U_DrawingNo").Value = mergedDrawings;
                        int updateStatus = oBOM.Update();
                        if (updateStatus != 0)
                        {
                            ErrorInfo updateErr = oCompany.GetLastError();
                            Console.WriteLine($"更新BOM {oRs.Fields.Item("DocEntry").Value} 失败: {updateErr.Message}");
                        }
                        else
                        {
                            Console.WriteLine($"BOM {oRs.Fields.Item("DocEntry").Value} 更新成功");
                        }
                    }

                    oRs.MoveNext();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"执行出错: {ex.Message}");
            }
            finally
            {
                oCompany.Disconnect();
            }
        }
    }
}

方案二：SQL批量更新（手动执行） #

如果不需要编程，可直接在数据库执行更新语句（执行前务必备份数据）：

UPDATE OITT
SET U_DrawingNo = 
    COALESCE(T1.U_Draw1, '') + 
    CASE WHEN T1.U_Draw2 IS NOT NULL THEN ', ' + T1.U_Draw2 ELSE '' END +
    CASE WHEN T1.U_Draw3 IS NOT NULL THEN ', ' + T1.U_Draw3 ELSE '' END +
    CASE WHEN T1.U_Draw4 IS NOT NULL THEN ', ' + T1.U_Draw4 ELSE '' END +
    CASE WHEN T1.U_Draw5 IS NOT NULL THEN ', ' + T1.U_Draw5 ELSE '' END
FROM OITT T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
-- 可添加WHERE条件指定需要更新的BOM范围
-- WHERE T0.DocEntry BETWEEN 1 AND 100

替换语句中的U_Draw1~U_Draw5和U_DrawingNo为实际字段名。

方案三：实时同步触发器（UI API） #

实现BOM创建/修改时自动同步物料UDF值，适用于需要实时更新的场景：

private void SBO_Application_ItemEvent(string FormUID, ref SAPbouiCOM.ItemEvent pVal, out bool BubbleEvent)
{
    BubbleEvent = true;
    // 112是BOM表单的默认FormUID，`1`是ItemCode字段的默认UID
    if (FormUID == "112" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_CHANGED && pVal.ItemUID == "1")
    {
        SAPbouiCOM.Form oForm = SBO_Application.Forms.Item(FormUID);
        string itemCode = oForm.Items.Item("1").Specific.Value.ToString();
        
        Recordset oRs = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
        oRs.DoQuery($"SELECT U_Draw1, U_Draw2, U_Draw3, U_Draw4, U_Draw5 FROM OITM WHERE ItemCode = '{itemCode}'");
        if (!oRs.EoF)
        {
            string mergedDrawings = string.Join(", ", 
                new string[] { 
                    oRs.Fields.Item("U_Draw1").Value?.ToString() ?? "",
                    oRs.Fields.Item("U_Draw2").Value?.ToString() ?? "",
                    oRs.Fields.Item("U_Draw3").Value?.ToString() ?? "",
                    oRs.Fields.Item("U_Draw4").Value?.ToString() ?? "",
                    oRs.Fields.Item("U_Draw5").Value?.ToString() ?? ""
                }.Where(s => !string.IsNullOrWhiteSpace(s)));
            
            // 替换U_DrawingNo为BOM表头UDF对应的控件ID或字段名
            oForm.Items.Item("U_DrawingNo").Specific.Value = mergedDrawings;
        }
    }
}

注意事项 #

1. 所有代码中的UDF字段名、表单ID、控件ID需替换为SAP B1系统中的实际值
2. 批量操作前务必备份数据库，避免数据丢失
3. 使用DI/UI API脚本时，需确保SAP B1客户端已注册对应的插件

内容的提问来源于stack exchange，提问作者anamika Singh