Stephen Smith's Blog

All things Sage ERP…

SData Referenced Resources

with 11 comments


Introduction

I blogged about a number of SData improvement in the upcoming Sage 300 ERP 2012 release here. At that time I felt that the SData support project was complete, but I was wrong, and the team was able to add another major feature in the last couple of sprints before code complete.

This feature is important because it introduces a feature that our current View Business Logic layer doesn’t handle well. This adds a foundation for developing UIs easier. This adds a foundation to allow customers, partners and developers to understand our data model much easier. So let’s introduce the feature in all its gory detail and then circle back to talk more about it at the end.

This article reproduces a lot of material from the DPP Wiki, mostly to make it a bit more accessible since hopefully this is of interest beyond just DPP partners. This material is on how to create these referenced resources in Sage 300 ERP SData feeds. Notice that the SData resources  are based on Sage 300 Views and that the extra information is added via XML files, so you get all the benefits of this feature with no additional programming. For more on how to make use of this feature check out the SData website specifically here.

Overview

Within the Resource View Mapping xml, you can define a section (called referencedResources):

  • For enabling you to link in read-only fields from other views. In Accpac 5.x, this was known as the “lookup fields” feature.
  • For enabling SData related resources.

Another way to look at this is that this is where you can define a foreign key relationship between the resource kind’s view and a different view.

Although the two areas are virtually identical we describe them here separately to reflect the two slightly different aims.

Lookup Fields

For example, the A/R Customers maintenance UI displays the description for the Terms Code field. This description is not a field that is part of the A/R Customers view and the A/R Payment Terms view is not composed with that A/R Customers view. There is a foreign key relationship between these 2 views, between the Terms Code (CODETERM) field in the A/R Customers view and the primary key of the A/R Payment Terms view. Therefore, the referencedResources section can be added to the A/R Customers resource view mapping to support displaying the Terms Code Description field from the A/R Payment Terms in the A/R Customers resource. The 2 views are related thru the CODETERM field in the A/R Customers view and the primary key of the A/R Payment Terms view and the Terms Description field has the field name of TEXTDESC in the A/R Payment Terms view. An example of how this would be represented in the resource view mappings of the 2 resources is shown below.

Note that if you already have a relationship defined between the views due to “view composition”, then you do not need to define this relationship in the referencedResources section. The Resource View Mapping xml already supports defining header/detail relationships using multi-level resources sections in the xml.

Configuration

These are the details of the sections of the resource file.

referencedResources Section

The referencedResources section must come between the Optional Fields (if present) and the IncludedFields or ExcludedFields, whichever is used.

The section can contain multiple referencedResource sections, each of which describes a resource that you want to link to the primary resource, in order to bring in additional field(s) from that resource into this one.

referencedResource Section

This section contains the information for an additional, referenced resource that will be linked to this resource. It has one attribute, name, which is the name of another SData resource (for which there is a separate resource view mapping). The name is the plural name of the resource as found in the classmap.xml file.

Within this section are 2 additional sections which describe the way in which this referenced resource is related to the primary resource:

  • referencedKeyFields
  • lookupFields

referencedKeyFields

This section contains a referencedKeyField item for each field in the defining key of the reference resource. It will normally be a field in the primary resource whose value will be used to “lookup” a value in the reference resource. Alternatively a fixed value might be necessary.

Note: any fields that you specify in this section will automatically be tagged as “autoPostback” – so that whenever their value is changed on the client, the change will be immediately submitted to the server so that the referenced fields can be recalculated.

The main information that you normally need to specify for this referencedKeyField is the viewFieldName, which is the name of that key field in the primary resource.

However if this reference actually uses a fixed value instead of the value of a field then use the fixedValue to specify that value.

The number of referencedKeyField items that you define will depend on the number of fields that are in the index that is being used by the reference resource. The index that is being used by a resource is defined by the definingIndex section of the resource definition. If this is not specified in the resource definition, then the index will be assumed to be the primary key (index 0).

The order of these items is important: they must match the order of the key field(s) in the reference resource’s index.

lookupFields

This section contains a lookupField item for each field in the reference resource whose value will be “looked up” (according to the key information specified in the referencedKeyFields section) in the referenced resource and will be displayed as a read-only field in the primary resource.

The main pieces of information that you will specify for this lookupField is the following:

viewFieldName: this is the name of the field in the reference resource.

name: this is the name of the field that will be used by the primary resource. There cannot be any spaces in this name and it must be unique with the primary resource.

A/R Customers Terms Description Example

This example demonstrates how you would define your A/R Terms Code and Customers resources so that the A/R Customers resource could include the “Terms Code Description” field from the A/R Terms Code resource in the Customers resource as a “lookup field”.

Beware: WordPress changes dash-dash-greaterthan to long-dash-greaterthan which then messes up the ends of the comments. Also beware that it changes regular double quotes into 66 and 99 styles quotes.

A/R Terms Code Resource Mapping

<?xml version=”1.0″ encoding=”UTF-8″?>
<resource name=”arTermsCode” description=”AR Terms Codes” >
<viewID>AR0016</viewID>
<pluralName>arTermsCodes</pluralName>
</resource>

A/R Customers Resource Mapping

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<resource name=”arCustomer” description=”AR Customers”>
<viewID>AR0024</viewID>
<pluralName>arCustomers</pluralName>
<serviceMapFile>customerServices.xml</serviceMapFile>
<optionalFields viewID=”AR0400″ location=”0″ />
<referencedResources>
<referencedResource name=”arTermsCodes”>
<referencedKeyFields>
<!– The referenced resource is referenced by key (fields of this resource map to key fields of that) –>
<!– with the field (Sage 300 ERP field ID) CODETERM being the only key field –>
<referencedKeyField viewFieldName=”CODETERM” />
</referencedKeyFields>
<lookupFields>
<!–  TEXTDESC in the termsCodes will be included in the customers resource as the read-only field “Terms Description” –>
<lookupField viewFieldName=”TEXTDESC” name=”TERMSDESCRIPTION” />
</lookupFields>
</referencedResource>
<referencedResource name=”txTaxGroups”>
<referencedKeyFields>
<referencedKeyField viewFieldName=”CODETAXGRP” />
<!– There is no field to match the TTYPE; instead we specify the value 1 (Sales) –>
<referencedKeyField fixedValue=”1″ />
</referencedKeyFields>
<lookupFields>
<lookupField viewFieldName=”DESC” name=”TAXGRPDESCRIPTION” />
</lookupFields>
</referencedResource>
</referencedResources>
</resource>

Recommended Best Practices

The application of referenced resources for lookup fields should be considered carefully with performance implications in mind. Application developers and QA testers may have to explore this thoroughly. For example, looking up a description field in another Sage 300 ERP View may imply a table scan in the corresponding view table.

SData related resources

In SData there is the concept of related resources; resource kinds that are shared between resources. For example many A/R customer resources share the same A/R payment terms resource. The referenced resource definition describes how they are related. As in the “lookup fields” case there is a foreign key relationship between these 2 views, between the Terms Code (CODETERM) field in the A/R Customers view and the primary key of the A/R Payment Terms view. Therefore, the referencedResources section can be added to the A/R Customers resource view mapping to support including the A/R Payment Terms in the A/R Customers SData resource. The 2 views are related thru the CODETERM field in the A/R Customers view and the primary key of the A/R Payment Terms view. An example of how this would be represented in the resource view mappings of the 2 resources is shown below.

Note that if you already have a relationship defined between the views due to “view composition”, then you do not need to define this relationship in the referencedResources section. The Resource View Mapping xml already supports defining header/detail relationships using multi-level resources sections in the xml.

Configuration

These are the details of the sections of the resource file.

referencedResources Section

The referencedResources section must come between the Optional Fields (if present) and the IncludedFields or ExcludedFields, whichever is used.

The section can contain multiple referencedResource sections, each of which describes a resource that you want to link to the primary resource.

referencedResource Section

This section contains the information for an additional, referenced resource that will be linked to this resource. It has three attributes:

  • name, which is the name of another SData resource (for which there is a separate resource view mapping). The name is the plural name of the resource as found in the classmap.xml file.
  • property, which is the property name that this will have in the referencing resource (e.g. “terms” in our example)
  • description, an optional description that can be used as the label for the property (e.g. “Payment terms”)

Within this section is the additional ‘referencedKeyFields’ section which describe the way in which this referenced resource is related to the primary resource.

referencedKeyFields

This section contains a referencedKeyField item for each field in the defining key of the referenced resource. It will normally be a field in the primary resource whose value will be used to “look up” a value in the reference resource. Alternatively a fixed value might be necessary.

Note: any fields that you specify in this section will automatically be tagged as “autoPostback” – so that whenever their value is changed on the client, the change will be immediately submitted to the server so that the referenced fields can be recalculated.

The main information that you normally need to specify for this referencedKeyField is the viewFieldName, which is the name of that key field in the primary resource.

However if this reference actually uses a fixed value instead of the value of a field then use the fixedValue to specify that value.

The number of referencedKeyField items that you define will depend on the number of fields that are in the index that is being used by the referenced resource. The index that is being used by a resource is defined by the definingIndex section of the resource definition. If this is not specified in the resource definition, then the index will be assumed to be the primary key (index 0).

The order of these items is important: they must match the order of the key field(s) in the referenced resource’s index.

A/R Customers Terms Related Example

This example demonstrates how you would define your A/R Terms Code and Customers resources so that the A/R Customers resource could include the A/R Terms Code resource in the Customers resource as a related resource.

A/R Terms Code Resource Mapping

<?xml version=”1.0″ encoding=”UTF-8″?>
<resource name=”arTermsCode” description=”AR Terms Codes” descriptorField=”TEXTDESC”>
<viewID>AR0016</viewID>
<pluralName>arTermsCodes</pluralName>
</resource>

A/R Customers Resource Mapping

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<resource name=”arCustomer” description=”AR Customers” descriptorField=”NAMECUST”>
<viewID>AR0024</viewID>
<pluralName>arCustomers</pluralName>
<serviceMapFile>customerServices.xml</serviceMapFile>
<optionalFields viewID=”AR0400″ location=”0″ />
<referencedResources>
<referencedResource name=”arTermsCodes” property=”terms” description=”Invoice terms”>
<referencedKeyFields>
<!– The referenced resource is referenced by key (fields of this resource map to key fields of that) –>
<!– with the field (Sage 300 ERP field ID) CODETERM being the only key field –>
<referencedKeyField viewFieldName=”CODETERM” />
</referencedKeyFields>
</referencedResource>
<referencedResource name=”txTaxGroups” property=”taxGroup” description=”Tax group”>
<referencedKeyFields>
<referencedKeyField viewFieldName=”CODETAXGRP” />
<!– There is no field to match the TTYPE; instead we specify the value 1 (Sales) –>
<referencedKeyField fixedValue=”1″ />
</referencedKeyFields>
</referencedResource>
</referencedResources>
</resource>

Recommended Best Practices

Including related resources should be considered carefully with performance implications in mind. Application developers and QA testers may have to explore this thoroughly. For example, looking up a related resource in another Sage 300 ERP View may imply a table scan in the corresponding view table.

Summary

Perhaps, related resources might seem like a bit of an esoteric SData topic, but we are excited about it for a couple of reasons. This is the first time where the Sage 300 ERP API has included explicit relational information about how all the various tables are inter-realted. This is a key enabler for making creating UIs easier via SData, since now UI tools can automatically know how to create finders and to find related information (automatic drill down). Here we see SData become much more than just a RESTful Web Services interface to Sage 300 ERP; we now see more and more API capabilities going beyond what we have in our COM, .Net and Java APIs.

About these ads

Written by smist08

July 7, 2012 at 4:47 pm

11 Responses

Subscribe to comments with RSS.

  1. Thanks smith. Excellent..

    When will development partner get updated sdk 6.1 and start upgrading in existing 5.x modules. Because there is no sdk update after June 29, 2011 (dpp wiki). Eagerly awaiting…

    Rengarajan

    July 9, 2012 at 6:44 pm

    • We did update the SDK sometime in April, 2012 which was our Alpha release for Sage 300 ERP 2012. We weren’t planning on releasing another SDK for beta since nothing has changed in the SDK. Beta notices should be going out in a day or two.

      smist08

      July 9, 2012 at 11:30 pm

      • Hi: There is a Sage 300 prospect that inquires as to the data storage, not simply language packs for screen presentation: Does the software have complete support for UTF-8? (required) In other words, will the software store in the same character set as the users using it? Cantonese users would then store transactions and customer records in the UTF-8 equivalent as well as use the appropriate language pack. Apparently Great Plains does NOT and was ruled out as a potential solution for them.

        Mark Bradtke

        February 6, 2013 at 11:06 pm

      • Hi Mark,

        Sorry but Sage 300 does not support UTF throughout. It uses double byte characters and is limited to using one system locale at a time.So you can store Cantonese and English data in the same database, but wouldn’t be able to add say Japanese.

        smist08

        February 6, 2013 at 11:17 pm

      • Thanks Stephen. I was hoping you could clarify. Are you referring to co-mingling locales? I don’t understand why you state Cantonese and English would work but not Japanese…since there is no Japanese language pack or because we went from two to three locales? Also, could custom language packs ever be created? What if each locale ran as separate entities (SQL databases) and we used Insight to consolidate financials and did not co-mingle data?

        Mark Bradtke

        February 7, 2013 at 12:22 am

      • Really you only get one system locale but often there are a few combinations that work together. Most include the standard latin characters, just not all the accented characters. But then you can have two main double byte languages like Cantonese and Japanese.

        smist08

        February 7, 2013 at 3:26 am

  2. [...] Sage Advisor Update Sage Advisor PEP Sage 300 ERP 2012 Payment Processing Sage 300 ERP Inquiry Queries for 2012 Value Added Application Features for Sage 300 ERP 2012 Sage Visual Process Flows What’s New in the Sage CRM Integration for Sage 300 ERP 2012 SageCRM 7.1 Released Sage 300 on Windows 8 Sage 300 ERP 2012 Supported Platforms SData Enhancements for Sage 300 ERP 2012 SData Referenced Resources [...]

  3. In extending out further the search for a prospect needing a five user accounting solution that is fully UTF-8 compliant, I am getting mixed feedback and thought I would pass it back to you in the hopes of gaining better understanding and the potential for sales of Sage products where such a need is expressed. I made an inquiry of whether any of the Microsoft’s offerings would be compliant and got feedback from them claiming that UTF-8 only relates to the “movement” of data, not the storage. It is most often an XML coded entry and by default, a product such as Microsoft AX omits any entry making it fully compliant from the start. Microsoft further went on to state that most any product should be compliant from their viewpoint, especially those using the Microsoft stack.

    Could you possibly indicate how this may be either realistic or that there are other architectural issues with Sage 300 (or the family of Sage products since Sage has ruled out any of their offerings as being candidate solutions for this prospect) as I am concerned that a misunderstanding could lead to loss of opportunities for Sage partners?

    Mark Bradtke

    February 19, 2013 at 8:06 pm

    • The problem is that many Microsoft technologies and stack elements use double byte characters, so even if you set SQL Server to use UTF8 then chances are the strings will be converted somewhere using the system locale which is what causes the problems. Converting all these components to components that handle UTF8 properly is a large job and we haven’t completed this yet.

      smist08

      February 20, 2013 at 4:02 am

  4. Hey Stephen,

    Can sData be used for Timberline 9.5? We’re trying to find a way to integrate with Timberline, and can’t determine if we can use sData or not. If not, any suggestions on how to best approach integration with Timberline?

    Any help is appreciated. Cheers,

    Paul

    Paul

    October 30, 2013 at 4:12 pm

    • SData isn’t available for Timberline today. They do have an SDK and that is probably your best route to integration. Down the road you would be able to integrate to the Sage Data Cloud which can synchronize to the on-premise database.

      smist08

      October 30, 2013 at 5:54 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 247 other followers

%d bloggers like this: