To access fields in a database, first you have to declare a file variable that references the file you want to access, then it must be opened. You do this using the m.file() and m.open() functions, like this:

mch = m.file(m.mch); m.open(mch)

For a virtual file, you must define the fields before the file is opened, like this:

myfile = m.file(m.v01)
m.field(myfile,’My string field’,m.str)
m.field(myfile,’My material’,m.material)
...
m.open(myfile)

Fields in an open file can be referenced by using their name, like this:

oldmanqty = mch.manqty --read a field
mch.manqty = m.measure(‘Metre,2’) –-assign to a field

The fields referenced can be ‘native’ fields or ‘qualifiers’.

Native fields are pre-defined as part of the standard Match-IT databases. A list of ‘native’ field names for each file is available in the reference section.

Qualifiers are fields added to the database by you and show in the ‘qualifiers’ tab on many forms. They are accessed in exactly the same way as a native field, e.g:

oldvalue = mch.myfield --read a qualifier
mch.myfield = m.measure(‘Metre,2’) –-assign to a qualifier

WARNING: When you write to a qualifier field, the database is updated immediately. Contrast this to native fields that are all written at once when you execute m.Create() or m.Update().

Drilling through

You can also access fields in objects that are referenced from some variable without having to declare and open the file. This is useful for accessing a single field but is very slow (because each access, opens the file, reads/writes the field, then closes the file again), so is not suitable when a large number of fields are required to be read or written.

To drill-through, you use the dot notation, like this:

sol = m.file(m.sol); m.open(sol);
... (do something to load a sales line sol)
if sol.OurPartNum.ManQty < m.measure(‘Each,2’) then
 sol.OurPartNum.ManQty = ‘Each,2’
end

This code fragment will update the reference quantity of the material in the sales line to 2 each if the current reference quantity is less than 2. Note: it’s the MCH that is being updated in this example, not the SOL. We’re just using the SOL to get at the MCH.

This dot notation can be continued to any depth. In general the syntax is:

object ‘.’ fieldname { ‘.’ fieldname }

The object must refer to a record in some database.

Direct file buffer access

You can also access fields directly in the file buffer provided the file is open (from within a Lua script or from the Match-IT context that invoked the Lua script, e.g. PPS). Use a notation like this:

if m.mch.ManQty < m.measure(‘Each,2’) then
 m.mch.ManQty = ‘Each,2’
end

This code fragment will update the reference quantity of the material currently in the file buffer. Only the file buffer is affected, not the file itself. The file itself must be updated via m.Create() or m.Update() or in the Match-IT environment the Lua script returns to.

Qualifier 'records'

If your system uses a large number of qualifiers that are intended to be accessed from scripts, it is useful to group related qualifiers into 'records'. A qualifier 'record' represents all qualifier fields associated with a file that have a common prefix. A 'prefix' is any part of the name that precedes a dot (.) character. E.g. if you have qualifiers associated with your materials catalogue with names like this:

•core

•VA

•primary.machine

•primary.wire

•primary.winding1.volts

•secondary.machine

•secondary.wire

Then primary and secondary are considered to be records consisting of the fields machine and wire, etc. They can be accessed using dot notation like this:

...

mch = m.file(m.mch)

m.open(mch)

m.load(mch,...)

primaries = mch.primary        --make a record 'reference'

if primaries.machine == ...    --referencing a value (via a record reference)

primaries.wire = ...           --assigning a value (via a record reference)

mch.primary.wire = ...         --the same assignment (without a record reference)

p1 = primaries.winding1        --a sub-record reference

p1.volts = 220                 --and an assignment though it

...

Note the use of assigning a reference to a record (primaries=). This is useful to aid the readability of your scripts. Note also, that there can be records within records (primary.winding1.volts), to any depth.

Qualifier 'arrays'

The qualifier system can also emulate arrays of records. If the name that follows a dot is entirely numeric, it can be accessed using the array notation in your scripts. E.g. if you define qualifiers like this:

•primary.1.wire

•primary.2.wire

•...

•primary.16.wire

Then the wire component of primary can be accessed as an array like this:

...

wire1 = mch.primary[1].wire  --get the wire for the first primary

wire2 = mch.primary[2].wire  --get the wire for the second primary

...

or, more interestingly, like this:

...

for winding = 1,16 do

 wire = mch.primary[winding].wire

 ...

end

...

The numeric element can be specified by any expression that evaluates to a number. Array elements, like Lua, are 1-based, so the first element is number 1. Also, the qualifier name elements for arrays must not start with a zero, this is to ensure there can be no ambiguity with '01' not being the same as '1', etc.