1. zg_Serial_Mask 模組 INV-[YY][MM][DD]-{99999}, 日期如果有9會被空白掉 (流水號「9」改以「_」替代)
2. Serial_Number 計算內容
代碼: 選擇全部
f_table_1 = Quote ( GetValue ( Substitute ( GetFieldName ( Serial_Number ) ; "::" ; Char ( 13 )) ; 1 ))
s_field_1 = "t1." & Quote ( GetValue ( Substitute ( GetFieldName ( Serial_Number ) ; "::" ; Char ( 13 )) ; 2 )) ;
w_field_1 = "t1." & Quote ( GetValue ( Substitute ( GetFieldName ( Serial_Number ) ; "::" ; Char ( 13 )) ; 2 )) ;
3. 目前有個子表ID問題, 子表ID格式為主表ID(Serial_Number)再加流水號,
如主表ID為INV-160718, 則子表ID為INV-160718-xx (xx指流水號)
小弟目前做法如下所述, 不知是否另有想法可不另建字段, 因若使用原碼子表ID帶不出主表ID, 只會顯示 -01, -02, -03....
(1) 先在子表下建立 f_table, s_field, w_field 等字段
(2) 子表ID計算內容以萬用流水號模組下去修改
代碼: 選擇全部
Let ([
prefix = Serial_Number ;
number_length = 2 ;
f_table_1 = f_table ;
s_field_1 = s_field ;
w_field_1 = w_field ;
criteria_1 = prefix & "%" ;
sqlStatment = "
SELECT Max ( Right ( s_field_1 , " & number_length & " ))
FROM f_table t1
WHERE UPPER ( w_field_1 ) LIKE ?
" ;
sqlQuery = Substitute ( sqlStatment ;
[ "f_table_1" ; f_table_1 ] ;
[ "s_field_1" ; s_field_1 ] ;
[ "w_field_1" ; w_field_1 ]
)
] ;
prefix & "-" &
Right ( "000000000000000000000000000000" &
ExecuteSQL ( sqlQuery ; " " ; "" ; criteria_1 ) + 1 ;
number_length )
)