How to create a view based on Syspro’s AdmFormdata

In Syspro, it is very handy to add as many custom fields as you want – but then to report on these fields could be a pain. 

I found it easiest to create a view based on AdmFormdata (where the custom field data are stored) – and then report using only the view.

For example, if you are storing and capturing stock dimensions (Length, Width, Height, etc) – your AdmFormdata might look like this:

FormType KeyField FieldName AlphaValue NumericValue
STK A100 DIM_L    10
STK A100 DIM_W    20
STK A100 DIM_H    30
STK A100 DIM_M    40
STK A201 DIM_L    11
STK A201 DIM_W    22
STK A201 DIM_H    33
STK A201 DIM_M    44

where 

–  ‘FormType’ indicates the data is connected to Inventory (InvMaster) and will always be ‘STK’.

–  ‘KeyField’ contains the InMaster primary key = StockCode (in this case two stock codes A100 and A201)

–  ‘FieldName’ is the (user defined) fieldname e.g. DIM_L = Length, DIM_W = Width, DIM_H = Height and DIM_M = Weight (Mass)

–  ‘AlphaValue’ not used in this case – but can be used for text/alphanumeric values.

–  ‘NumericValue’ is the field value.

 

Now I would create the following simple view:

CREATE VIEW StockCodeDimensions

AS

SELECT KeyField AS StockCode,

MAX(CASE WHEN FieldName = ‘DIM_L’

 THEN NumericValue

 ELSE NULL END) AS Length,

MAX(CASE WHEN FieldName = ‘DIM_W’

 THEN NumericValue

 ELSE NULL END) AS Width,

MAX(CASE WHEN FieldName = ‘DIM_H’

 THEN NumericValue

 ELSE NULL END) AS Height,

MAX(CASE WHEN FieldName = ‘DIM_M’

 THEN NumericValue

 ELSE NULL END) AS Weight

FROM AdmFormData

WHERE FormType = ‘STK’ AND (FieldName LIKE ‘DIM%’)

GROUP BY KeyField;

 

 Resulting in the following table:    (SELECT * FROM StockCodeDimensions)

StockCode Length Width Height Weight
A100 10 20 30 40
A201 11 22 33 44

 

Note that I used  ” FieldName LIKE ‘DIM% ” in my query above.  This can simply be modified/copied to create a view for (say) ‘ANA’ if analysis codes are used to specify (eg) stock code category, colour, age group, chemical restriction, etc.

 

Sage Line 500 promotion price query

The following is a useful query for extracting the current (active) promotional prices from a Sage Line 500 price list table (oplistm) – where (say) ‘MN’ is the main selling Finished Goods warehouse in stockm and all prices should be shown as inclusive of VAT (14%):

select p.price_list, p.product_code, s.long_description, round(s.price*1.14,2) normal_price,
CASE
WHEN p.vat_inclusive_flag = “Y” THEN p.price
WHEN p.vat_inclusive_flag = “N” THEN round(p.price*1.14,2)
END promo_price,
p.price_start_date,
p.price_end_date
FROM oplistm p, stockm s
WHERE p.product_code = s.product AND s.warehouse = “MN”
AND p.sequence_number <> ”  ”
AND p.price_start_date <=today
AND p.price_end_date >= today
ORDER BY p.product_code, p.price_start_date;