The matrix database has a pre-defined structure, as described below. The prime index is a composite of the account code or group and the product code or group (and the expiry date if so configured). This is separated by a pre-defined character that must never appear with an account or stock, typically the hyphen, ‘-‘, is used.
An account or product group is padded out with zeroes to a five digit number and then prefixed with the hash sign ‘#’. When expiry date processing not in use the matrix look-up code for customer group 3 and product group 2 would therefore be #00003-#00002. The entry for customer BAL001 and product group 4 would be BAL001-#00004, the entry for customer group 5 and product item CH0167 would be #00005-CH0167 and the entry for customer GRE001 and product DT0167 would be GRE001-DT0167.
Where expiry date processing is in use the code for an entry for customer GRE001 and product DT0167 to expire on the 31st December 2003 would be ‘GRE001-DT0167-31/12/03’.
In use there is a hierarchy in which the matrix entries would be checked, the system first checks for an exact match on the customer and product codes, next the system checks for a customer account combined with the product group, then the customer group and product code before finally checking the combination of customer group and product group. If all searches fail then the standard stock price is applied.
No Item Name Type Comment
1 CODE X (32) The prime index of the matrix record which holds the combination of account/stock details as discussed above. Note that the maximum size is 32 characters so therefore the length of the customer code and stock code (and expiry date if so configured) together cannot exceed 31 characters – the separator using one character.
2 DISCOUNT K, L The normal discount which is to be offered for this matrix entry, if zero then any value set as the ‘line discount’ on the customer/supplier record is applied.
3 PRICE K, L, M The price that is to be offered for this matrix entry.
4 QTY-BREAK ? If quantity breaks are to apply for this matrix entry then this flag is set to ‘Y’.
5 DATE D, E The expiry date for the matrix entry, if a date is entered here then the special ‘price’ only applies for orders/invoices up to this date.
6 COMMENT1 X (30) If any text is entered into this or the subsequent two items then when the matrix entry is applied a form is displayed containing this comment.
7 COMMENT2 X (30)
8 COMMENT3 X (30)
9 ACCOUNT-CODE X (10) If the matrix entry is entered for a specific customer / supplier then the account code is recorded here.
10 ACCOUNT-NAME X (30) If the matrix entry is entered for a specific customer / supplier then the account name is recorded here.
11 STOCK-CODE X (16) If the matrix entry is entered for a specific stock/product item then the stock code is recorded here.
12 STOCK-DESC X (30) If the matrix entry is entered for a specific stock / product item then the stock description is recorded here.
13 SPECIAL-ITEM-13 This item, of any input type, can be used to record extra information against the matrix record which can be copied to the sales / purchase order detail line.
17 QUANTITY-1 K, L, M For quantity break matrix entries a series of (up to) 10 quantities are recorded as the break points for a change in price band, price or discount. This item holds the quantity at which the first price band is to take effect.
18 DISCOUNT-1 K, L For quantity break entries this is the discount to apply when the first quantity break is reached.
19 QUANTITY-2 K, L, M
20 DISCOUNT-2 K, L
21 QUANTITY-3 K, L, M
22 DISCOUNT-3 K, L
23 QUANTITY-4 K, L, M
24 DISCOUNT-4 K, L
25 QUANTITY-5 K, L, M
26 DISCOUNT-5 K, L
27 QUANTITY-6 K, L, M
28 DISCOUNT-6 K, L
29 QUANTITY-7 K, L, M
30 DISCOUNT-7 K, L
31 QUANTITY-8 K, L, M
32 DISCOUNT-8 K, L
33 QUANTITY-9 K, L, M
34 DISCOUNT-9 K, L
35 QUANTITY-10 K, L, M
36 DISCOUNT-10 K, L
37 QTY-PRICE-1 K, L, M This item holds the price for the first quantity break point. As with the quantity / discount items a series of (up to) 10 items are required.
38 QTY-PRICE-2 K, L, M
39 QTY-PRICE-3 K, L, M
40 QTY-PRICE-4 K, L, M
41 QTY-PRICE-5 K, L, M
42 QTY-PRICE-6 K, L, M
43 QTY-PRICE-7 K, L, M
44 QTY-PRICE-8 K, L, M
45 QTY-PRICE-9 K, L, M
46 QTY-PRICE-10 K, L, M
49 START-DATE-1 D, E If applying prices by date range then this item holds the date at which the price offer is to start.If apply matrix entries by end date then this item holds the date at which the matrix expires and should be named accordingly.
50 END-DATE-1 D, E If applying prices by date range then this item holds the ate at which this price offer ends. If apply matrix entries by end date then this is the date at which the matrix entry starts and should be named accordingly.
51 PRICE-1 K, L, M If applying prices by date range then this item holds the price for the first date range.If applying matrix entries by expiry date then this item (and items 52-78) should not be added to the database.
52 START-DATE-2 D, E
53 END-DATE-2 D, E
54 PRICE-2 K, L, M
55 START-DATE-3 D, E
56 END-DATE-3 D, E
57 PRICE-3 K, L, M
58 START-DATE-4 D, E
59 END-DATE-4 D, E
60 PRICE-4 K, L, M
61 START-DATE-5 D, E
62 END-DATE-5 D, E
63 PRICE-5 K, L, M
64 START-DATE-6 D, E
65 END-DATE-6 D, E
66 PRICE-6 K, L, M
67 START-DATE-7 D, E
68 END-DATE-7 D, E
69 PRICE-7 K, L, M
70 START-DATE-8 D, E
71 END-DATE-8 D, E
72 PRICE-8 K, L, M
73 START-DATE-9 D, E
74 END-DATE-9 D, E
75 PRICE-9 K, L, M
76 START-DATE-10 D, E
77 END-DATE-10 D, E
78 PRICE-10 K, L, M
80 QTY-WH-PR-1 X (1) If applying matrix entry by quantity break this item holds the price band once the first break point is reached. As with the other quantity break items a series of (up to) 10 items are required.
81 QTY-WH-PR-2 X (1)
82 QTY-WH-PR-3 X (1)
83 QTY-WH-PR-4 X (1)
84 QTY-WH-PR-5 X (1)
85 QTY-WH-PR-6 X (1)
86 QTY-WH-PR-7 X (1)
87 QTY-WH-PR-8 X (1)
88 QTY-WH-PR-9 X (1)
89 QTY-WH-PR-10 X (1)
97 WHICH-PRICE X (1) If using the multiple discount option then this is the price band to which the discounts are to apply.
98 MULT-DISC-1 K, L If using the multiple discounts option then this is the first of (up to) four progressive discounts to be applied.
99 MULT-DISC-2 K, L
100 MULT-DISC-3 K, L
101 MULT-DISC-4 K, L
Article ID: 991
Created On: Wed, Jun 27, 2012 at 10:54 AM
Last Updated On: Mon, Jun 12, 2023 at 7:30 PM
Online URL: https://kb.datafile.co.uk/article/discount-matrix-file-structure-991.html