Skip to Content
avatar image
Former Member

How to create FMS to capture data in pair (2 fields)

Hi all,

I need some help to create FMS to capture Serial number and Lot number in pair. This is for vehicle industry where we need VIN (Serial number) and Engine No. (Lot number) of the item. A/R invoice has to show VIN and Engine No. we assigned under Delivery. The Queries generated for FMS are per below:

SELECT DISTINCT T1.IntrSerial AS 'Serial Number' FROM [dbo].[SRI1] T0 INNER JOIN [dbo].[OSRI] T1 ON T1.SysSerial = T0.SysSerial and T0.ItemCode = T1.ItemCode WHERE T0.BaseType = 15 AND T0.BaseEntry = 0 AND T0.BaseLinNum = 0 AND T0.LineNum = 1

SELECT DISTINCT T1.BatchId AS 'Engine No.' FROM [dbo].[SRI1] T0
INNER JOIN [dbo].[OSRI] T1 ON T1.SysSerial = T0.SysSerial and T0.ItemCode = T1.ItemCode
WHERE T0.BaseType = 15 AND T0.BaseEntry = 0 AND T0.BaseLinNum = 0 AND T0.LineNum = 1

However, how can I pair up the data? for example there are 1 item with 2 quantities under Delivery.

1. VIN no.001 Engine No.002

2. VIN no.002 Engine No.002

A/R invoice will be created one by one, if copy from deliver, VIN and Engine No. will show only the first one. if change VIN no. to 002, the Engine No. will not change accordingly. How can we make it Engine No. auto change (pair up with) per selected VIN no?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers