OpenPro XML interface basics

Back

Openpro XML Interface Information

What is XML?

XML is a markup language for documents containing structured information. Structured information contains both content (words, pictures, etc.) and some indication of what role that content plays (for example, content in a section heading has a different meaning from content in a footnote, which means something different than content in a figure caption or content in a database table, etc.).

Almost all documents have some structure.

A markup language is a mechanism to identify structures in a document. The XML specification defines a standard way to add markup to documents.

What's a Document?

The number of applications currently being developed that are based on, or make use of, XML documents is truly amazing (particularly when you consider that XML is not yet a year old)! For our purposes, the word “document” refers not only to traditional documents, like this one, but also to the myriad of other XML “data formats”. These include vector graphics, e-commerce transactions, mathematical equations, object meta-data, server APIs, and a thousand other kinds of structured information.

So XML is Just Like HTML?

No. In HTML, both the tag semantics and the tag set are fixed. An <h1> is always a first level heading and the tag <ati.product.code> is meaningless. The W3C, in conjunction with browser vendors and the WWW community, is constantly working to extend the definition of HTML to allow new tags to keep pace with changing technology and to bring variations in presentation (stylesheets) to the Web. However, these changes are always rigidly confined by what the browser vendors have implemented and by the fact that backward compatibility is paramount. And for people who want to disseminate information widely, features supported by only the latest releases of Netscape and Internet Explorer are not useful.

XML specifies neither semantics nor a tag set. In fact XML is really a meta-language for describing markup languages. In other words, XML provides a facility to define tags and the structural relationships between them. Since there's no predefined tag set, there can't be any preconceived semantics. All of the semantics of an XML document will either be defined by the applications that process them or by stylesheets.

So XML Is Just Like SGML?

No. Well, yes, sort of. XML is defined as an application profile of SGML. SGML is the Standard Generalized Markup Language defined by ISO 8879. SGML has been the standard, vendor-independent way to maintain repositories of structured documentation for more than a decade, but it is not well suited to serving documents over the web (for a number of technical reasons beyond the scope of this article). Defining XML as an application profile of SGML means that any fully conformant SGML system will be able to read XML documents. However, using and understanding XML documents does not require a system that is capable of understanding the full generality of SGML. XML is, roughly speaking, a restricted form of SGML.

For technical purists, it's important to note that there may also be subtle differences between documents as understood by XML systems and those same documents as understood by SGML systems. In particular, treatment of white space immediately adjacent to tags may be different.

Why XML?

In order to appreciate XML, it is important to understand why it was created. XML was created so that richly structured documents could be used over the web. The only viable alternatives, HTML and SGML, are not practical for this purpose.

HTML, as we've already discussed, comes bound with a set of semantics and does not provide arbitrary structure.

SGML provides arbitrary structure, but is too difficult to implement just for a web browser. Full SGML systems solve large, complex problems that justify their expense.

Viewing structured documents sent over the web rarely carries such justification.

This is not to say that XML can be expected to completely replace SGML. While XML is being designed to deliver structured content over the web, some of the very features it lacks to make this practical, make SGML a more satisfactory solution for the creation and long-time storage of complex documents.

In many organizations, filtering SGML to XML will be the standard procedure for web delivery.

What Do XML Documents Look Like?

If you are conversant with HTML or SGML, XML documents will look familiar. A simple XML document is presented in Example 1.

Example 1. A Simple XML Document

<?xml version="1.0"?>
<oldjoke>
<burns>Say <quote>goodnight</quote>,
Gracie.</burns>
<allen><quote>Goodnight,
Gracie.</quote></allen>
<applause/>
</oldjoke>

A few things may stand out to you:

  • The document begins with a processing instruction: <?xml …?>. This is the XML declaration While it is not required, its presence explicitly identifies the document as an XML document and indicates the version of XML to which it was authored.
  • There's no document type declaration. Unlike SGML, XML does not require a document type declaration. However, a document type declaration can be supplied, and some documents will require one in order to be understood unambiguously.

Empty elements (<applause/> in this example) have a modified syntax While most elements in a document are wrappers around some content, empty elements are simply markers where something occurs (a horizontal rule for HTML's <hr> tag, for example, or a cross reference for DocBook's <xref> tag).

The trailing /> in the modified syntax indicates to a program processing the XML document that the element is empty and no matching end-tag should be sought.

Since XML documents do not require a document type declaration, without this clue it could be impossible for an XML parser to determine which tags were intentionally empty and which had been left empty by mistake.

XML has softened the distinction between elements which are declared as EMPTY and elements which merely have no content.

In XML, it is legal to use the empty-element tag syntax in either case. It's also legal to use a starttag/end-tag pair for empty elements: <applause></applause>.

If interoperability is of any concern, it's best to reserve empty-element tag syntax for elements which are declared as EMPTY and to only use the empty-element tag form for those elements.

XML documents are composed of markup and content. There are six kinds of markup that can occur in an XML document: elements, entity references, comments, processing instructions, marked sections, and document type declarations. The following sections introduce each of these markup concepts.

Elements

Elements are the most common form of markup. Delimited by angle brackets, most elements identify the nature of the content they surround. Some elements may be empty, as seen above, in which case they have no content. If an element is not empty, it begins with a starttag, <element>, and ends with an end-tag, </element>.

Attributes

Attributes are name-value pairs that occur inside starttags after the element name. For example, <div class=“preface”> is a div element with the attribute class having the value preface. In XML, all attribute values must be quoted.

Entity References

In order to introduce markup into a document, some characters have been reserved to identify the start of markup. The left angle bracket, < , for instance, identifies the beginning of an element start- or end-tag.

In order to insert these characters into your document as content, there must be an alternative way to represent them. In XML, entities are used to represent these special characters. Entities are also used to refer to often repeated or varying text and to include the content of external files.

Every entity must have a unique name. Defining your own entity names is discussed in the section on entity declarations. In order to use an entity, you simply reference it by name. Entity references begin with the ampersand and end with a semicolon.

For example, the lt entity inserts a literal < into a document. So the string <element> can be represented in an XML document as &lt;element>.

A special form of entity reference, called a character reference, be used to insert arbitrary Unicode characters into your document. This is a mechanism for inserting characters that cannot be typed directly on your keyboard.

Character references take one of two forms: decimal references, &#8478;, and hexadecimal references, &#x211E;. Both of these refer to character number U+211E from Unicode (which is the standard Rx prescription symbol, in case you were wondering).

Comments

Comments begin with <!– and end with –>.

Comments can contain any data except the literal string . You can place comments between markup anywhere in your document.

Comments are not part of the textual content of an XML document. An XML processor is not required to pass them along to an application.

Processing Instructions

Processing instructions (PIs) are an escape hatch to provide information to an application. Like comments, they are not textually part of the XML document, but the XML processor is required to pass them to an application.

Processing instructions have the form: <?name pidata?>. The name, called the PI target, identifies the PI to the application. Applications should process only the targets they recognize and ignore all other PIs. Any data that follows the PI target is optional, it is for the application that recognizes the target. The names used in PIs may be declared as notations in order to formally identify them.

PI names beginning with xml are reserved for XML standardization.

CDATA Sections

In a document, a CDATA section instructs the parser to ignore most markup characters.

Consider a source code listing in an XML document. It might contain characters that the XML parser would ordinarily recognize as markup (< and &, for example).

In order to prevent this, a CDATA section can be used.

<![CDATA[
*p = &q;
b = (i <= 3);
]]>

Between the start of the section, <![CDATA[ and the end of the section, ]]>, all character data is passed directly to the application, without interpretation. Elements, entity references, comments, and processing instructions are all unrecognized and the characters that comprise them are passed literally to the application.

The only string that cannot occur in a CDATA section is: ]]>.

Document Type Declarations

A large percentage of the XML specification deals with various sorts of declarations that are allowed in XML. If you have experience with SGML, you will recognize these declarations from SGML DTDs (Document Type Definitions). If you have never seen them before, their significance may not be immediately obvious.

One of the greatest strengths of XML is that it allows you to create your own tag names. But for any given application, it is probably not meaningful for tags to occur in a completely arbitrary order. Consider the old joke example introduced earlier. Would this be meaningful?

<gracie><quote><oldjoke>Goodnight,
<applause/>Gracie</oldjoke></quote>
<burns><gracie>Say <quote>goodnight</quote>,
</gracie>Gracie.</burns></gracie>

It's so far outside the bounds of what we normally expect that it's nonsensical. It just doesn't mean anything.

However, from a strictly syntactic point of view, there's nothing wrong with that XML document. So, if the document is to have meaning, and certainly if you're writing a stylesheet or application to process it, there must be some constraint on the sequence and nesting of tags. Declarations are where these constraints can be expressed.

More generally, declarations allow a document to communicate meta-information to the parser about its content. Meta-information includes the allowed sequence and nesting of tags, attribute values and their types and defaults, the names of external files that may be referenced and whether or not they contain XML, the formats of some external (non-XML) data that may be referenced, and the entities that may be encountered.

There are four kinds of declarations in XML: element type declarations, attribute list declarations, entity declarations, and notation declarations.

Element Type Declarations

Element type declarations identify the names of elements and the nature of their content. A typical element type declaration looks like this:

<!ELEMENT oldjoke (burns+, allen, applause?)>

This declaration identifies the element named oldjoke.

Its content model follows the element name. The content model defines what an element may contain. In this case, an oldjoke must contain burns and allen and may contain applause. The commas between element names indicate that they must occur in succession. The plus after burns indicates that it may be repeated more than once but must occur at least once. The question mark after applause indicates that it is optional (it may be absent, or it may occur exactly once). A name with no punctuation, such as allen, must occur exactly once.

Declarations for burns, allen, applause and all other elements used in any content model must also be present for an XML processor to check the validity of a document.

In addition to element names, the special symbol #PCDATA is reserved to indicate character data. The moniker PCDATA stands for parseable character data.

Elements that contain only other elements are said to have element content. Elements that contain both other elements and #PCDATA are said to have mixed content.

For example, the definition for burns might be:

<!ELEMENT burns (#PCDATA | quote)*>

The vertical bar indicates an or relationship, the asterisk indicates that the content is optional (may occur zero or more times); therefore, by this definition, burns may contain zero or more characters and quote tags, mixed in any order. All mixed content models must have this form: #PCDATA must come first, all of the elements must be separated by vertical bars, and the entire group must be optional.

Two other content models are possible: EMPTY indicates that the element has no content (and consequently no end-tag), and ANY indicates that any content is allowed.

The ANY content model is sometimes useful during document conversion, but should be avoided at almost any cost in a production environment because it disables all content checking in that element.

Here is a complete set of element declarations for Example 2:

Example 2. Element Declarations for Old Jokes

<!ELEMENT oldjoke applause?)>
(burns+, allen,
<!ELEMENT burns (#PCDATA | quote)*>
<!ELEMENT allen (#PCDATA | quote)*>
<!ELEMENT quote (#PCDATA)*>
<!ELEMENT applause EMPTY>

Attribute List Declarations

Attribute list declarations identify which elements may have attributes, what attributes they may have, what values the attributes may hold, and what value is the default. A typical attribute list declaration looks like this:

<!ATTLIST oldjoke
name
ID
#REQUIRED
label
CDATA
#IMPLIED
 ( funny | notfunny ) 'funny'>

In this example, the oldjoke element has three attributes:

  1. name, which is an ID and is required;
  2. label, which is a string (character data) and is not required; and
  3. status, which must be either funny or notfunny and defaults to funny, if no value is specified.

Each attribute in a declaration has three parts: a name, a type, and a default value.

You are free to select any name you wish, subject to some slight restrictions, but names cannot be repeated on the same element.

There are six possible attribute types:

  1. CDATA - CDATA attributes are strings, any text is allowed. Don't confuse CDATA attributes with CDATA sections, they are unrelated.
  2. ID - The value of an ID attribute must be a name. All of the ID values used in a document must be different. IDs uniquely identify individual elements in a document. Elements can have only a single ID attribute.
  3. IDREF or IDREFS - An IDREF attribute's value must be the value of a single ID attribute on some element in the document. The value of an IDREFS attribute may contain multiple IDREF values separated by white space.
  4. ENTITY or ENTITIES - An ENTITY attribute's value must be the name of a single entity (see the discussion of entity declarations below). The value of an ENTITIES attribute may contain multiple entity names separated by white space.
  5. NMTOKEN or NMTOKENS - Name token attributes are a restricted form of string attribute. In general, an NMTOKEN attribute must consist of a single word, but there are no additional constraints on the word, it doesn't have to match another attribute or declaration. The value of an NMTOKENS attribute may contain multiple NMTOKEN values separated by white space.
  6. A List Of Names - You can specify that the value of an attribute must be taken from a specific list of names. This is frequently called an enumerated type because each of the possible values is explicitly enumerated in the declaration.

Alternatively, you can specify that the names must match a notation name (see the discussion of notation declarations below).

There are four possible default values:

  1. #REQUIRED - The attribute must have an explicitly specified value on every occurrence of the element in the document.
  2. #IMPLIED - The attribute value is not required, and no default value is provided. If a value is not specified, the XML processor must proceed without one.
  3. “value” - An attribute can be given any legal value as a default. The attribute value is not required on each element in the document, and if it is not present, it will appear to be the specified default.
  4. #FIXED “value” - An attribute declaration may specify that an attribute has a fixed value. In this case, the attribute is not required, but if it occurs, it must have the specified value. If it is not present, it will appear to be the specified default. One use for fixed attributes is to associate semantics with an element. A complete discussion is beyond the scope of this article, but you can find several examples of fixed attributes in the XLink specification.

The XML processer performs attribute value normalization on attribute values: character references are replaced by the referenced character, entity references are resolved (recursively), and whitespace is normalized.

Entity Declarations

Entity declarations allow you to associate a name with some other fragment of content. That construct can be a chunk of regular text, a chunk of the document type declaration, or a reference to an external file containing either text or binary data.

A few typical entity declarations are shown in Example 3.

Example 3. Typical Entity Declarations

<!ENTITY
ATI
"ArborText, Inc.">
<!ENTITY boilerplate
SYSTEM
"/standard/legalnotice.xml">
<!ENTITY ATIlogo
SYSTEM "/standard/logo.gif" NDATA GIF87A>

There are three kinds of entities:

1. Internal Entities

Internal entities associate a name with a string of literal text. The first entity in Example 3 is an internal entity. Using &ATI; anywhere in the document will insert ArborText, Inc. at that location. Internal entities allow you to define shortcuts for frequently typed text or text that is expected to change, such as the revision status of a document.

Internal entities can include references to other internal entities, but it is an error for them to be recursive.

The XML specification predefines five internal entities:

  • &lt; produces the left angle bracket, <
  • &gt; produces the right angle bracket, >
  • &amp; produces the ampersand, & produces a single quote character (an apostrophe), '
  • &quot; produces a double quote character, “
  • &apos;
2. External Entities

External entities associate a name with the content of another file. External entities allow an XML document to refer to the contents of file. External entities contain either text binary data. If they contain text, the content of external file is inserted at the point of reference and parsed as part of the referring. Binary data is not parsed and may be referenced in an attribute. Binary data is used to reference figures and other non-XML content in the document.

The second and third entities in Example 3 are external entities.

Using &boilerplate; will have insert the contents of the file /standard/legalnotice.xml at the location of the entity reference. The XML processor will parse the content of that file as if it occurred literally at that location.

The entity ATIlogo is also an external entity, but its content is binary. The ATIlogo entity can only be used as the value of an ENTITY (or ENTITIES) attribute (on a graphic element, perhaps). The XML processor will pass this information along to an application, but it does not attempt to process the content of /standard/logo.gif.

3. Parameter Entities

Parameter entities can only occur in the document type declaration. A parameter entity declaration is identified by placing % (percentspace) in front of its name in the declaration. The percent sign is also used in references to parameter entities, instead of the ampersand.

Parameter entity references are immediately expanded in the document type declaration and their replacement text is part of the declaration, whereas normal entity references are not expanded. Parameter entities are not recognized in the body of a document.

Looking back at the element declarations in Example 2, you'll notice that two of them have the same content model:

<!ELEMENT burns (#PCDATA | quote)*>
<!ELEMENT allen (#PCDATA | quote)*>

At the moment, these two elements are the same because they happen to have the same literal. In order to make more explicit the fact these two elements are semantically the, use a parameter entity to define their model. The advantage of using a entity is two-fold. First, it allows you give a descriptive name to the content, and it allows you to change the content model only a single place, if you wish to update the element declarations, assuring that they always stay the same:

<!ENTITY % personcontent "#PCDATA | quote">
<!ELEMENT burns (%personcontent;)*>
<!ELEMENT allen (%personcontent;)*>

Notation Declarations

Notation declarations identify specific types of external binary data. This information is passed to the processing application, which may make whatever use of it it wishes. A typical notation declaration is:

<!NOTATION GIF87A SYSTEM "GIF">
Do I need a Document Type Declaration?

As we've seen, XML content can be processed without a document type declaration. However, there are some instances where the declaration is required:

Authoring Environments

Most authoring environments need to read and process document type declarations in order to understand and enforce the content models of the document.

Default Attribute Values

If an XML document relies on default attribute values, at least part of the declaration must be processed in order to obtain the correct default values.

White Space Handling

The semantics associated with white space in element content differs from the semantics associated with white space in mixed content. Without a DTD, there is no way for the processor to distinguish between these cases, and all elements are effectively mixed content. For more detail, see the section called White Space Handling, later in this document.

In applications where a person composes or edits the data (as opposed to data that may be generated directly from a database, for example), a DTD is probably going to be required if any structure is to be guaranteed.

Including a Document Type Declaration

If present, the document type declaration must be the first thing in the document after optional processing instructions and comments.

The document type declaration identifies the root element of the document and may contain additional declarations. All XML documents must have a single root element that contains all of the content of the document. Additional declarations may come from an external DTD, called the external subset, or be included directly in the document, the internal subset, or both:

<?XML version="1.0" standalone="no"?>
<!DOCTYPE chapter SYSTEM "dbook.dtd" [
<!ENTITY %ulink.module "IGNORE">
<!ELEMENT ulink (#PCDATA)*>
<!ATTLIST ulink

xml:link CDATA #FIXED "SIMPLE"
xml-attributes CDATA #FIXED "HREF URL"
URL #REQUIRED> CDATA
]>
<chapter>...</chapter>

This example references an external DTD, dbook.dtd, and includes element and attribute declarations for the ulink element in the internal subset. In this case, ulink is being given the semantics of a simple link from the XLink specification.

Note that declarations in the internal subset override declarations in the external subset. The XML processor reads the internal subset before the external subset and the first declaration takes precedence.

In order to determine if a document is valid, the XML processor must read the entire document type declaration (both internal and external subsets). But for some applications, validity may not be required, and it may be sufficient for the processor to read only the internal subset. In the example above, if validity is unimportant and the only reason to read the doctype declaration is to identify the semantics of ulink, reading the external subset is not necessary.

You can communicate this information in the standalone document declaration. The standalone document declaration, standalone=“yes” or standalone=“no” occurs in the XML declaration. A value of yes indicates that only internal declarations need to be processed. A value of no indicates that both the internal and external declarations must be processed.

Other Markup Issues

In addition to markup, there are a few other issues to consider: white space handling, attribute value normalization, and the language in which the document is written.

White Space Handling

White space handling is a subtle issue. Consider the following content fragment:

<oldjoke>
<burns>Say <quote>goodnight</quote>, Gracie.</burns>
</oldjoke>

Is the white space (the new line between <oldjoke> and <burns> ) significant? Probably not.

But how can you tell? You can only determine if white space is significant if you know the content model of the elements in question. In a nutshell, white space is significant in mixed content and is insignificant in element content.

The rule for XML processors is that they must pass all characters that are not markup through to the application. If the processor is a validating processor, it must also inform the application about which whitespace characters are significant.

The special attribute xml:space may be used to indicate explicitly that white space is significant. On any element which includes the attribute specification xml:space='preserve', all white space within that element (and within subelements that do not explicitly reset xml:space ) is significant.

The only legal values for xml:space are preserve and default. The value default indicates that the default processing is desired. In a DTD, the xml:space attribute must be declared as an enumerated type with only those two values.

One last note about white space: in parsed text, XML processors are required to normalize all end-of-line markers to a single line feed character (&#A;). This is rarely of interest to document authors, but it does eliminate a number of cross-platform portability issues.

Attribute Value Normalization

The XML processer performs attribute value normalization on attribute values: character references are replaced by the referenced character, entity references are resolved (recursively), and whitespace is normalized.

Language Identification

Many document processing applications can benefit from information about the natural language in which a document is written, XML defines the attribute xml:lang to identify the language. Since the purpose of this attribute is to standardize information across applications, the XML specification also describes how languages are to be identified.

Validity

Given the preceding discussion of type declarations, it follows that some documents are valid and some are not. There are two categories of XML documents: wellformed and valid.

Well-formed Documents

A document can only be well-formed if it obeys the syntax of XML. A document that includes sequences of markup characters that cannot be parsed or are invalid cannot be well-formed.

In addition, the document must meet all of the following conditions (understanding some of these conditions may require experience with SGML):

  • The document instance must conform to the grammar of XML documents. In particular, some markup constructs (parameter entity references, for example) are only allowed in specific places. The document is not well-formed if they occur elsewhere, even if the document is well-formed in all other ways.
  • The replacement text for all parameter entities referenced inside a markup declaration consists of zero or more complete markup declarations. (No parameter entity used in the document may consist of only part of a markup declaration.) No attribute may appear more than once on the same start-tag.
  • String attribute values cannot contain references to external entities.
  • Non-empty tags must be properly nested.
  • Parameter entities must be declared before they are used.
  • All entities except the following: amp, lt, gt, apos, and quot must be declared.
  • A binary entity cannot be referenced in the flow of content, it can only be used in an attribute declared as ENTITY or ENTITIES.
  • Neither text nor parameter entities are allowed to be recursive, directly or indirectly.

By definition, if a document is not well-formed, it is not XML. This means that there is no such thing as an XML document which is not well-formed, and XML processors are not required to do anything with such documents.

Valid Documents

A well-formed document is valid only if it contains a proper document type declaration and if the document obeys the constraints of that declaration (element sequence and nesting is valid, required attributes are provided, attribute values are of the correct type, etc.). The XML specification identifies all of the criteria in detail.

XML General Informaton

OpenPro XML Interface

OpenPro designed its XML interface to run in three different modes, batch xml processing, where you can run this process in batch php processing, interactive select a file and import, or using the SOAP communications method.

  1. Batch Processing, (ftp a file to OpenPro includes/import folder,) run the program from the command prompt. Php program name with parameters.
  2. Interactive, have the file on you local machine, select the program and import into the system
  3. Communications SOAP interface, you have a program the communications and send the files thru that communication method.

The program xml_importer is used for importing each method.

XML Format Validator

There is also a test mode and a xml formatter. This verifies that the XML is in the correct format.

With the validator, you can put in a URL or browse for the file and import into OpenPro.

Select the file.

It will let you know what the results are.

XML Importer

Select the file you are importing, it will display the transaction information.

  • First, it imports the file to the includes/import folder on the web server. (so you must give rights to write to that location.)
  • Second, it reads the temporary file form there and verifies the data, and then processes the data into OpenPro systems.

If there are any errors, the detail error information will show up.

XML transactions sample files

OpenPro accepts XML from all the tables within the OpenPro system.

Starts off with <openpro> shows that its an openpro transaction.

Then each transaction type in this example is gl_entry, (optional) other transactions are:

  • Gl_entry, inventory_trans, sales_order, ap_voucher, ar_open_items, po_order, po_receiver, sales_invoices, sm_incidents
  • Transactions are groups of records, processes and updates that can be imported into the system.
  • <table name> specifies the table name.
  • Action is insert, update, delete and or select. Select is used to specify the sql commands for an inquiry.
  • Field names are the fields to insert, update or delete.
  • And ends with </openpro>

  • Starts off with <openpro>
  • Then each transaction type, (optional)
  • Gl_entry, inventory_trans, sales_order, ap_voucher, ar_open_items, po_order, po_receiver, sales_invoices, sm_incidents
  • And ends with </openpro>

Special functions and things to know about OpenPro XML Processing

Below is a list of tips and special functions during the importing process.

- If there is a date field that is required, but not in the insert, the system will default to system date. - Some special functions with sales order: <table name=“so_orders” action=“insert” type=“invoice”>

Has different types for sales order, quote, order, invoice this will change the status of the order based upon the codes in type.

Getnextnumber

This is a special function getting the next transaction number from the business rules table. Example below of the next inventory transaction document number.

<field name="document_no" getnextnumber="next_inventory_transaction"></field>

All dates are in database date format 2007-05-08.

Function can be used to lookup table ids and insert them into this record.

<field name="customerid" link_table="ar_customers" ref="last"></field>

In this example, it looks up the customer id, from the ar_customers table, and grabs the last insert during this xml process, grabs that table id and inserts it into this field.

dblookup feature

Function can be used to lookup table ids and insert them into this record.

Example in the sales order entry xml importing:

<field name='itemid' dblookup_table='ic_items' dblookup_input='partnum' dblookup_output='id'>FG100</field>

This results in the following query:

SELECT id FROM ic_items WHERE partnum='FG100' AND companyid='YourCompany'

Fields:

  • dblookup_table = table we are querying from
  • dblookup_input = field name we are using in the query 'where' clause to look the data up
  • dblookup_output = field name we want returned from the query, and to replace the data in the xml tag.
  • xml tag data = this is the value used for the search criteria. this gets replaced by the value looked up.

companyid is automaticly filled in if the companyid field exists for the dblookup_table specified.

Sales Order Processing Type and Process Inventory Feature

if you want to process inventory and GL, include the following tags in your “so_orders” xml table.

<table name="so_orders" action="insert" type="invoice" special="process_inventory">

Type options are used to import Sales Order transactions with different status:

  • type=“quote” –> this tells us that it is an quote and no processing has been done.
  • type=“order” –> this tells us that it is an sales order and waiting in the shipping quote to be shipped and invoiced.
  • type=“invoice” –> this tells us that and order has been entered, shipped and invoiced, all detail history transactions have been processed, (customer history, receivables, gl, inventory, etc).
  • special=“process_inventory” –> this tells us to process inventory and GL. (just as if “Invoice Order” has been placed on a sales order.

Sample GL Entry transaction

Click to open

Sample inventory control adjustment transaction

Click to open

Some basic system features are built into the XML like getnextnumber.

Sample inventory control move order transaction

Click to open

Sample receiver transaction

Click to open

Sample Sales Order Transaction

This transaction is broken into 3 segments, customer first, then the order header information and then the order line items.

Click to open

Some functions like getnextnumber, field_link and link_table are used to call system functions.

Click to open