【客户需求】仅有库存管理模块，需要实现功能在录入采购入库单实时查询该存货的最新采购价、历史最高价以及历史最低价初始需求界面【U8软件替代解决】一、建立存货扩展自定义项1，启用存货自定义项13、4 分别作为最低价、最新价、最高价。然后前期分别测试测试最新价的数据库语句 update Inventory set cInvDefine13=(select iUnitCost from rdrecords01 where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ) if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } 测试最高价的数据库语句 update Inventory set cInvDefine14=( select iUnitCost from rdrecords01 where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ) if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } 测试最低价数据库语句 update Inventory_extradefine set cidefine1=( select iUnitCost from rdrecords01 where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ) if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } 测试完成全部通过二、写通过采购入库单主表审核字段更新价格语句的触发器 create trigger rukushenhe0103 on RdRecord01 for update as if update (chandler) --更新存货自定义项13为采购入库单最新入库单价-- update Inventory set cInvDefine13=(select iUnitCost from rdrecords01 where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ) --更新存货自定义项14为采购入库单最高入库单价-- update Inventory set cInvDefine14=( select iUnitCost from rdrecords01 where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ) --更新存货扩展自定义项1为采购入库单最低入库单价-- update Inventory_extradefine set cidefine1=( select iUnitCost from rdrecords01 where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ) if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } 本语句在已经有存货入库记录的情况下无任何报错，但是在新增存货没有任何入库记录的情况下出现子表查询异常报错；检查发现新存货没有入库记录，导致更新表没有来源导致报错对语句进行修正，修改后的语句新增存货没有任何记录时体现正常 create trigger rukushenhe0103 on RdRecord01 for update as if update (chandler) --更新存货自定义项13为采购入库单最新入库单价-- update Inventory set cInvDefine13=ISNULL((select iUnitCost from rdrecords01 where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0') --更新存货自定义项14为采购入库单最高入库单价-- update Inventory set cInvDefine14=ISNULL(( select iUnitCost from rdrecords01 where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0') --更新存货扩展自定义项1为采购入库单最低入库单价-- update Inventory_extradefine set cidefine1=ISNULL(( select iUnitCost from rdrecords01 where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ),'0') if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } 但是，出现2个同样最高单价的入库存货出现异常，修正重复值取一个，得到最后语句 create trigger rukushenhe0103 on RdRecord01 for update as if update (chandler) --更新存货自定义项13为采购入库单最新入库单价-- update Inventory set cInvDefine13=ISNULL((select DISTINCT iUnitCost from rdrecords01 where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0') --更新存货自定义项14为采购入库单最高入库单价-- update Inventory set cInvDefine14=ISNULL(( select DISTINCT iUnitCost from rdrecords01 where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0') --更新存货扩展自定义项1为采购入库单最低入库单价-- update Inventory_extradefine set cidefine1=ISNULL(( select DISTINCT iUnitCost from rdrecords01 where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ),'0') if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } — 演示效果（可以下载后观看）也可以直接预览

采购入库单显示最新价、最高价和最低价

用友小张

【客户需求】

仅有库存管理模块，需要实现功能在录入采购入库单实时查询该存货的最新采购价、历史最高价以及历史最低价

初始需求界面

【U8软件替代解决】

一、建立存货扩展自定义项1，启用存货自定义项13、4 分别作为最低价、最新价、最高价。

然后前期分别测试

测试最新价的数据库语句

update Inventory 
set cInvDefine13=(select iUnitCost from rdrecords01 
where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) )

测试最高价的数据库语句

update Inventory 
set cInvDefine14=(
select iUnitCost from rdrecords01 
where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) )

测试最低价数据库语句

update Inventory_extradefine
set cidefine1=(
select iUnitCost from rdrecords01 
where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) )

测试完成全部通过

二、写通过采购入库单主表审核字段更新价格语句的触发器

create trigger rukushenhe0103
on RdRecord01 for update
as
if update (chandler)

--更新存货自定义项13为采购入库单最新入库单价--

update Inventory 
set cInvDefine13=(select iUnitCost from rdrecords01 
where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) )

--更新存货自定义项14为采购入库单最高入库单价--
update Inventory 
set cInvDefine14=(
select iUnitCost from rdrecords01 
where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) )
--更新存货扩展自定义项1为采购入库单最低入库单价--
update Inventory_extradefine
set cidefine1=(
select iUnitCost from rdrecords01 
where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) )

本语句在已经有存货入库记录的情况下无任何报错，但是在新增存货没有任何入库记录的情况下出现子表查询异常报错；检查发现新存货没有入库记录，导致更新表没有来源导致报错

对语句进行修正，修改后的语句新增存货没有任何记录时体现正常

create trigger rukushenhe0103
on RdRecord01 for update
as
if update (chandler)

--更新存货自定义项13为采购入库单最新入库单价--

update Inventory 
set cInvDefine13=ISNULL((select iUnitCost from rdrecords01 
where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0')

--更新存货自定义项14为采购入库单最高入库单价--
update Inventory 
set cInvDefine14=ISNULL((
select iUnitCost from rdrecords01 
where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0')
--更新存货扩展自定义项1为采购入库单最低入库单价--
update Inventory_extradefine
set cidefine1=ISNULL((
select iUnitCost from rdrecords01 
where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ),'0')

但是，出现2个同样最高单价的入库存货出现异常，修正重复值取一个，得到最后语句

create trigger rukushenhe0103
on RdRecord01 for update
as
if update (chandler)

--更新存货自定义项13为采购入库单最新入库单价--

update Inventory 
set cInvDefine13=ISNULL((select  DISTINCT iUnitCost from rdrecords01 
where AutoID =(select max(AutoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0')

--更新存货自定义项14为采购入库单最高入库单价--
update Inventory 
set cInvDefine14=ISNULL((
select   DISTINCT iUnitCost from rdrecords01 
where iUnitCost =(select max(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode) ),'0')
--更新存货扩展自定义项1为采购入库单最低入库单价--
update Inventory_extradefine
set cidefine1=ISNULL((
select  DISTINCT iUnitCost from rdrecords01 
where iUnitCost =(select min(iUnitCost)from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode) ),'0')

—

演示效果（可以下载后观看）也可以直接预览

用友小张

优化后

create trigger rukushenhe0103
on [dbo].[RdRecord01] for update
as
if update (chandler) begin
--更新存货自定义项13为采购入库单最新入库单价--
update Inventory
set Inventory.cInvDefine13=ISNULL((select  DISTINCT rdrecords01.iUnitCost from rdrecords01
where rdrecords01.AutoID =(select max(rdrecords01.autoID)from rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode 
) ),'0')

--更新存货自定义项14为采购入库单最高入库单价--
update Inventory 
set Inventory.cInvDefine14=ISNULL((
select   DISTINCT rdrecords01.iUnitCost from rdrecords01  
where rdrecords01.iUnitCost =(select max(rdrecords01.iUnitCost)from 
rdrecords01 where rdrecords01.cinvcode=Inventory.cInvCode
) ),'0')

--更新存货扩展自定义项1为采购入库单最低入库单价--
update Inventory_extradefine
set Inventory_extradefine.cidefine1=ISNULL((
select  DISTINCT rdrecords01.iUnitCost from rdrecords01  
where rdrecords01.iUnitCost =(select min(rdrecords01.iUnitCost)
from rdrecords01 where rdrecords01.cinvcode=Inventory_extradefine.cInvCode
) ),'0')
set nocount off
end