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.