Scenario to use a temporary table with Connectivity Studio

Link

Price exports with discounts per customer and product can end up in complex queries or limitations. To make it possible to export your data, you can also use a temporary table and develop your calculations as usual in the AOT.

Introduction

For this scenario we will fill a temporary table with 2 records and export that to an XML file. To export the prices using a temporary table we need to setup:

  1. Create a message using the Wizard
    1. Create the source document (Dynamics AX) and connector
    2. Create the target document (XML) and connector
  2. Extend the document class to fill data in the temporary table
  3. Run the message

Create a message using the wizard

When we select the project we can create a message using the wizard: In the start screen you can see the required setup. When we move to the next page we can start with the setup of the message: As we want to export we need to specify a source application (dynamics AX) and a target application (ERP).

  • Source connector: Existing “Active company”
  • Source document: New of type Microsoft Dynamics AX
  • Target connector: Existing “Folder”
  • Target document: New of type XML

Now we can start to define which table we want to export to xml. You can now specify the temporary table: TmpSalesItemReq and select the fields SalesPrice, SalesUnit and ItemId. The message is created with a default mapping for the selected fields.

Extend the document class to fill data in the temporary table

Now we need to create data in this table and make sure our source document can use it. The easiest way is to use the “Extend” button in the action pane. It’s easy to give it a different name and declase the temporary table. Now we can add data to the temporary table and use that in the message. To read the entity set for the query we can call the next method and construct it as below: Be aware that the “Data” method is used to break the link with the query by creating a copy of the record. Now we can link the new class in the source document:

Run the message

The setup is finished and now we can run the message and see the history. The result in XML contains now the fields as defined in the XML document.

That’s all to export a temporary table using Connectivity studio. As you can see it’s pretty easy to extend Connectivity studio with code if required to support custom processes.

Do you hate personal tasks lists as much as I do? Focus on team results!

Personally I hate task lists and classify them as unusable. It doesn’t help, it doesn’t fit, and it’s demotivating. In my opinion you cannot always know how much time it takes to finish, when you will have time to do it. Sometimes you need motivation, you need passion, some ideas before you can start and you cannot plan motivationJ.

Changing the culture can help to get better team results like I described in a previous blog http://www.van-veldhuizen.eu/blog/2014/12/23/you-want-more-business-think-about-holacracy-in-your-organization . But how can you implement it?

When you work with others you need to assign tasks to different team members and you need to finish it at some time. When you talk with planners there is one thing important: You should have a task for everything you do and there is nothing more important than posting your hours immediately.

Is it possible to make task lists useful? I think so.

Focus on what is important

It’s very important to make sure that you do the right things. There are different theories available and one quadrant what describes it very well.



Like everyone I work on things which are not important, I just like it! On the other hand I should focus on important things. And here it comes. How can you make sure you work on important things? Hmm you need a task list at least to remember it.

One of the challenges is to prevent you from urgent and important tasks. It’s more effective to work on prevention.

Take the time to improve your processes and your software! The words: “No thanks, we are too busy” is very commonly used. It sounds like a joke, but it isn’t. You should take the time to find the issues which are slowing down your processes, which are blocking creativity, which are ensuring low quality.

Focus on team results

In our team we use Team Foundation Server to track the team tasks. The team tasks do bring us:

  • Predictability to our planning department
  • Trust in the team but also to other teams
  • Getting input from other teams
  • Getting feedback from our customers and do something with it

The question is how to get the focus on important things. In our team we discussed about what should always be done. We found three goals:

  • Do development / maintenance to work on the future
  • Support customers with sharing knowledge and experiences. We do that with blogs, help and training materials.
  • Service customers to make sure that we can learn and practice our own products.

In our task list we try to make sure we always have attention for these three areas. In practice we simply divide our work week in 2 days service, 2 days development and 1 day support per team member. We can change this of course, and can move days around between team members.

When the task cannot be classified it’s not important for us as team! Sometimes we have time, but it’s a low priority. In our team we try to focus on important activities which are not urgent yet. For each goal we check what is most important and also we discuss if there are services, issues which can become urgent and how we can prevent that.

To identify our most important value stream we used the business canvas. One of my colleagues knew this and it adds a lot of value when you can identify the important streams in your team. The benefit is that you align and discuss and you can always refer back.

Covey has a different way to look at it with his priority schema. It’s up to you which methodology you prefer.

I believe that you cannot work without any task list. You need to align with others and to discuss about priorities and about what is important. Commitment is resulting in trust. We do that every day in our startup meeting and once in the two weeks in our planning / scrum meeting. Everyone should align the team tasks with his personal schedule. And of course, when you hate details in your task list, you can still left it out. That’s a team decision, the outcome must be clear, the how is up to you.

When you like to improve yourself and / or your team I have must reads for you:

  • The 7 Habits of Highly Effective People
  • First Things First

Please feel free to ask me about improving teams and results. It’s one of the things I like as well!

Johan

 

 

You want more business? Think about holacracy in your organization!

Last time I was traveling by taxi from the airport in Seattle. In the taxi we discussed about traveling and business and I think the taxi driver had some bad experiences with bosses and companies. He argued that everything was about money, money and money. And I think he showed that by adding a nice gift to his price without asking for confirmationJ. As I was there to discuss with others about our work, how to do things better and to learn, I didn’t agree to that. It’s not that I hate money, but I don’t think that business is all about money. I’m passionate about my job.

One of the things I really like is creativity. It’s my job, it’s my passion. But when I look back, schools didn’t value it, didn’t like it. Schools are focused on things we can measure like mathematics and languages; it’s not about creativity, it’s not about people. I think it’s a bit explained in this great TED talk. It’s funJ.

http://www.ted.com/talks/ken_robinson_says_schools_kill_creativity

And still organizations are valuing it the same way. Look at how you can achieve a bonus (that’s true for most companies I talked with):

  • Based on added revenue
  • Based on how many services you did
  • Based how much overwork you performed (over peformance)
  • Based on how much you or your sales department sold?
  • Company results based on Ebitda?

That is all about money. But is that the best way to manage a company? Personally I’m sure, it’s not. Guys who can negotiate get the biggest bonus. When I discuss with friends and colleagues about our job we may discuss about salaries but mostly we discuss about what will drive us in our jobs, in our live. And the cool thing is, that’s not money. Most of the time we talk about passion, about technology and that in the end it’s all about people, helping people and how to team together!

Currently most of the companies have a hierarchical structure. Sometimes it’s a combination of a hierarchical structure with a more business oriented structure.

As the CEO is valued about money, all his managers will be valued about money, all the people will be valued about money. But is there no other way to organize your company? I recently learned more about a concept called “Holacracy”. http://en.wikipedia.org/wiki/Holacracy. Personally I really like this and let me explain why.

I believe that it’s very important that a team owns their product or service. It’s very important that people will take their own responsibility. I believe that when people are responsible, they will feel responsible, they will be responsible. I believe that responsibility goes hand in hand with ownership. When you are responsible for your product or service and customers, you will make sure that you help them the best way you can. It’s not your company’s customer anymore, it’s your customer.

In addition to that it is very important that you can influence the teams or departments around your team as well. If you don’t work together, you are not really responsible. For example, when you work together with the support team, you should team up together to get the best results. The same counts for sales, marketing and other teams as well. The company needs to stimulate that teams work together, as that’s the only way to improve your internal processes and work towards happy customers.

A long time ago I read a book about the results when you can do the things you like. You should focus on the things you like as that is most the time something you can do best! That means, in a team you don’t need to split your work based on roles and responsibilities but based on skills and expertise. Marcus Buckingham has written a nice book about: “Now, discover your strengths.” What is important, what do people see? He’s explaining it with a small sample. Your son will comeback from school with his report. On a rate of 1 (bad) to 10 (excellent) he shows his report:

Mathematics

9

Dutch

8

English

5

Painting

10

Music

7

 

What is most likely what people will say? That 9 is very good, but what about the 5? How is that possible? And of course his teachers will argue that he needs extra lessons in English as that is very important.

What are his strengths? Where do you think he will perform? Where do you want to challenge him? Based on this list I would guess he likes mathematics and painting and that’s where you can challenge him.

And his languages? I agree it must on an acceptable level as he will never be the best.

Doing more business? Focus on strengths, trust people, give responsibility and make sure that everyone is responsible for something he owns and fire all managers who don’t believe in strengths, in people.

Do we need companies without management? Maybe, but we always need people who brings us somewhere. Do we need only leaders? I don’t think so either. For each team you need someone with leadership skills and someone with management skills. You need to motivate people and tell them about the journey, you also need to facilitate them as well in the journey. It’s great if you can combine it in one person as that is the cheapest option. You definitely need both type of skills.

There are some cons as well by implementing the anti-hierarchical model with good reason as well. See this readable post from Schumpeter

http://www.economist.com/news/business/21606267-latest-big-idea-management-deserves-some-scepticism-holes-holacracy

In a company you will see people with different personalities. The holacracy will motivate people who are autonomic. Is that true? Does that bring us to company organization models per personality? Hybrid models?

It’s clear that everyone needs direction and frames, but also freedom, creativity and passion… As there are pros and cons for hierarchical and holacracy models, I’m very interested in ideas to get the best of both.

Feel free to drop me an email or add comments with your suggestions,

Johan

Johan @ van-veldhuizen.eu

Synchronize customers to Microsoft CRM using Connectivity Studio

Contoso Entertainment Systems (West) have implemented Microsoft Dynamics AX 2012 where they manage their master customer data. To optimize their sales and support processes they are implementing Microsoft CRM as well. It’s very crucial to have the correct customer data available in CRM and in their ERP application. Therefore they decided to maintain the customer data in Microsoft Dynamics AX and update CRM accordingly.

As it is crucial to have correct data in both applications they cannot enter the data manually and they want to automate the process. They will synchronize the customer with related information:

  • Customer addresses
  • Contact information

Why should you try to automate the synchronization processes? It saves a lot of manual entry. Data is always consistent. It lowers the risk. It saves a lot of time. It’s real time or close to real time. It’s very useful in scenarios where a huge amount of data is involved.

Of course you can think about other scenarios like:

  • Integrations to CRM with customers, leads, addresses, contact information and more
  • Integrations to Product Lifecycle Management (PLM) systems
  • Integrations for e-Commerce
  • Integrations for Product Information Management systems
  • Integrations for warehouse management operations
  • Integrations for price lists
  • Business Intelligence (BI) for the transactions
  • Integrations to POS systems for retail

How can Connectivity Studio help them?

Using Connectivity Studio an integration is made using the following steps:

  1. Define which data should be exported
  2. Configure the document definition for the XML file
  3. Configure the message and create the mapping from the query to the XML document
  4. Configure the events which should trigger the message with the changes

Define which data should be exported

You can configure the query for the data which should be exported. For each table you need to select the fields which are required for the synchronization. In this definition we used a view for the address and a view for the contact information. In addition we made sure that the records which will be used to track changes are available in this definition.

You can specify ranges to limit the export.

Configure the document definition for the XML file

The XML definition is very similar to the query definition. One of the differences is that the organization level is merged to the customer XML node.

Configure the message and create the mapping from the query to the XML document

For the message the source and target document are defined. When this is done you can define the mapping from the source to the target records. In the sample below you can see that we map the accountnum from the custtable and the name from the dirOrganization together to the customer XML node.

Configure the events which should trigger the message with the changes

Based on the query for Microsoft Dynamics AX we can now setup events. This can be done in the header tab. For each record in the source document you can define which event should be used.

For each record you can define if you want to synchronize when the customer is inserted, updated or deleted. Using the check field option it will verify if one of the fields in the source document is changed.

Result

When I update the name of the customer 3002 from Contoso Retail Seattle and add “Sync” as a suffix it is added to the event log.

When the event is processed it has generated the XML file with all the related information as defined in the message!

As you can see it is very easy to synchronize data between different applications!

 

Connectivity Studio helped to export the customers in XML format to Microsoft CRM. It secures data consistency and compliance. Instead of spending weeks on this integration it could be setup in a day.

Hope you like this and feel free to contact me or follow me on twitter: jvan_veldhuizen

Happy daxing,

Johan

Retail categories and item creation

Creating a product and item can be a challenge in Microsoft Dynamics AX 2012. There are a lot of properties possible. Using a nice feature in the Retail module you can create items a lot faster! The reason I like to share this, as it can save you a lot of time and money but also give you way more control and better quality.

You can also use this in Connectivity Studio for Microsoft Dynamics Ax 2012. That can be very useful in integrations to a web shop to optimize your e-commerce process.

How can Retail help to create items?

In the Retail module we can setup default values per item category. When you select this category, you will get a complete item.

How can we use the Retail categories to create items?

The Retail categories are linked to the product categories. Retail should be enabled to use this.

  1. Create product hierarchy
  2. Enable the product hierarchy for Retail
  3. Setup your default values per company
  4. Create an item

Create product hierarchy

In the product information management module you can setup the category hierarchies.

You can setup and group al the categories and attributes.

Enable the product hierarchy for Retail

To enable the new or existing hierarchy you only need to specify the category hierarchy type for “Retail product hierarchy”. Per type you can only select one hierarchy.

Setup your default values per company

When you now navigate to the Retail module, you can specify per company all the default values for each category. That means, you can setup all the values according per category!

Create an item

When this is setup you can now start to create a new item. When you navigate back to the product information management module you can now create a new item. When you specify the Retail category, you get all the default values for free!

And when you confirm the settings it will create an item

Item synchronization using Connectivity Studio

And using Connectivity Studio we can use the same setup to create items! At the moment we create an item, we check if we have a category hierarchy type for Retail. If so, we check if we have a category linked to the product and use that category to initialize the item with all default values.

Hope you like this and feel free to contact me or follow me on twitter: jvan_veldhuizen

Happy daxing,

Johan

Unretrieved value from SQL in Microsoft Dynamics AX 2012

Problem description

For one of our products we use table inheritance for some tables. This works fine, but adding fields is not always working. The synchronization to the SQL database doesn’t work correctly. This results in the issue that when you select the record with all the fields, the new field is not included! So you cannot update via a job or update via a for as the field is not editable.

Solution direction

You can run a query on the SQL database to initialize the field. In the current scenario I didn’t have access to the SQL database so I made an SQL script in Microsoft Dynamics AX. As you may have noticed, since R2 it’s storing all the values in 1 table when you use table inheritance. So for this scenario I have used the base table to initialize the field. You can of course add constraints using the instance relation type to make sure you update only the required records.

The syntax is plain and simple:

‘UPDATE yourtable SET yourfield = 0′

I had to make a static method on a class as you can only run the direct query with server access.

static server void updateField()

{

str queryString = @’UPDATE BisDocumentTable SET PreValidateImport = 0 ‘;

Connection con = new Connection();

Statement statement;

//Resultset resultSet;

int result;

;

statement = con.createStatement();

new SqlStatementExecutePermission(queryString).assert();

result = statement.executeUpdate(queryString);

info(int2str(result));

}

 

Thanks to some others which helped me to fix this quickly:

http://www.axstart.com/unretrieved-values-on-the-companyinfo-table-in-ax-2012r2/

http://www.artofcreation.be/2010/06/21/executing-direct-sql-statements/

Have a good day,

Johan

 

FAQ: debug and Extend connectivity with code

Business Integration Solution

Introduction

In some cases it is required to extend the Connectivity process when the configuration cannot solve your issue. There are many scenarios possible where this is required. In this document we describe some of them. Checkout the debug options to detect and find the issue.

Most of the things can be done without customization as the configuration is very powerful. So only use this when you really need special functionality.

Debug options

When you need to find an issue you can start the debugger on some places. Let me explain when you need which option and which variables to look at.

Reason

Class / method

Variables

Find the record with an issue

BisMessageRunDirect.writeFields()

  • _mappingTableObject: contains the mapping for the table
  • mappingFieldObject: Contains the mapping record, so you can see the target field
  • _sourceEntity, you can see the record and name of the source
  • _targetEntity, you can see the record and name of the target

Find if a field value is set or reset

BisMessageRunDirect.onWriteField()

  • _sourceEntity, you can see the record and name of the source
  • _targetEntity, you can see the record and name of the target
  • _value, is the value after the mapping is executed

Find the result after the record is inserted (fields which are changed)

BisMessageRun.onInsertEntity()

BisMessageRun.onUpdateEntity()

BisMessageRun.onDeleteEntity()

BisMessageRun.onKeepEntity()

These methods can be used to verify the result per record.

  • _sourceEntity, you can see the record and name of the source
  • entity, you can see the record and name of the target

Find a specific mapping result

Table: BisMappingField.getValue()

The structure for table inheritance is as below for each type

  • newValue, the value based on the mapping
  • valueStr, the value converted to a string value
  • _sourceEntity, you can see the record and name of the source
  • Value, input variable which can by anyType

 

Possible options to extend

In Connectivity we can extend the functionality in different places. Based on the requirement you can choose the best option. We have different places where we can extend with custom code. We can do that for a message, document and connector.

When we think that a method will be overridden a lot, we prefix it with “on”. E.g. “onRecordUpdate”.

Message

On the message we can extend the BisMessageRunDirect class. This can be done to get specific logic per message.

The methods are almost added in the sequence they are executed.

Method name

Goal

onOpen

This is the start of the process.

onReadEntitySet

This will return a new set of records from the source.

onCheckMappingCondition

In the mapping you can define a condition per record which is validated here. So custom checks can be added here.

onCheckMappingFieldCondition

In the mapping you can define a condition per field which is validated here. So custom checks can be added here.

onMappingTransactionBegin

Starts the transaction on the target document.

onStartMappingTable

This is before the mapping starts for a table

onFindRecord

Using the fields which are selected as a key field it will try to find existing records. This can be customized to apply template records if required.

onInitEntity

This will initialize the record when it is not found

onWriteField

This will write the field to the target

onDeleteEntity

Based on the mapping setting for a record, it will delete records.

onReplaceEntity

This will first delete the record when it exists, and always insert a new one.

onUpdateEntity

This will update and validate the entity.

onInsertEntity

Based on the mapping setting for a record, it will insert records.

onKeepEntity

When a record is found, it will return the entity else it will not insert a record but return a null value.

onErrorMappingTable

 

onFinishMappingTable

After finishing the mapping for a record you can do something. For example, updating docuref information.

onMappingTransactionCommit

Commits the transaction on the target document. This can be very useful for posting actions as all the lines are inserted or updated and committed to the database.

onMappingTransactionAbort

When the transaction fails the transaction needs to be aborted.

onClose

This method is the end of the process. It closes the transaction, the history, documents and connectors.

The debugger can be used in all of these methods to see what is happening. The central method is:

processEntitySet

This method loops through the source and processes it to the target.

Document

Each document can have specific functionality as well. In some cases you have general additions which is used for different messages.

For each type we have a different class. The hierarchy is shown below:

So basically we have document extending from Ax and documents extending from external sources. They are almost executed in the sequence below:

Method name

Goal

Open

This will open the connection

onTransactionBegin

This will start the transaction. This can be controlled by the transaction setting on the message (root level or document level)

onCreateEntity

This will return the entity for a record

onInitEntity

When the record is not found it will initialize

onWriteField

Write the value of a field

onWriteEntity

Writes the entity and define if it’s an update or insert

onUpdateEntity

Update the entity

onInsertEntity

Inserts the entity

onDeleteEntity

Deletes the entity

onTransactionCommit

Commits the transaction

onTransactionAbort

Aborts the transaction

onErrorEntity

When an error occurs for a source entity, this method will be called

Close

This will close the connection

 

Connector

For each connector type you can define custom functionality.

The structure for the connectors is:

For each type the class can be extended.

Method name

Goal

Open

This will open the connector

getNextInstance

This will return the sets of data. So for file it will return the next file

preAction

This are the actions when it’s a source connector

postAction

This are the action for the target connector

close

This will close the connector

 

File action

The file actions can be extended by extending the table. The structure for the tables is as below.

You can simply extend the table and implement your own functionality.

Method name

Goal

runSourceAction

This is the source action for the connector. E.g. unzip

runTargetAction

This is the target action for the connector. E.g. zip

Business Entities

The entities in the business integration platform can be extended to implement business logic from the user interface. The entity can be compared to a data source on a form.

Naming convention: When there is no entity available your class name has to be prefixed with “BIS{tablename}”. It will search the extended classes and find the latest one in the structure.

 

Scenario – post sales order packingslip after import

You can post a sales order directly after you import a sales order into Microsoft Dynamics Ax. When this is specific for this message, you can extend the message class: BisMessageRunDirect.

After the super call the sales order is posted. When it fails, the sales order is still imported into Microsoft Dynamics Ax.

Scenario – import from odbc database and update status

When a sales order is imported using an ODBC connection you may want to update the status back that the record is processed.

This can be done by extending the message class.

After this change, you can create an export message and this will be executed for the record in the context.

scenario – PLM integration using specific record templates

The PLM system has production items and buyer items. So based on the input it should use or the production or the buyer item.

When it cannot find the record it will initialize the entity.

When there is a bomUnitid is set, it should initialize using a template record. Don’t forget to change the item id and the record id as it should create a new record.

 

 

How to rename a root node in xml using XSLT and test it online

In some cases you can have an incoming xml file with different root nodes but with the same content. In the sample here I need to rename the root name into “xml”. In this sample I will use a small file for an order.

<order123>
<salestable segment="1">
<salesid>10</salesid>
</salestable>
<salestable segment="1">
<salesid>20</salesid>
</salestable>
</order123>

An XSLT can help to make it easier to import it using our Business Integration Solutions. You can define a unique root name which is used to validate the incoming xml. When this doesn’t match our solution interpreted this as invalid xml. To change the root name I’ve written an XSLT as below:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<!-- Copy all nodes that we dont want to handle specificly -->
<xsl:template match="/*">
<xml>
<xsl:apply-templates select="@*|node()"/>
</xml>
</xsl:template>

<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()" />
</xsl:element>
</xsl:template>

<!-- template to copy attributes -->
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="." />
</xsl:attribute>
</xsl:template>

<!-- template to copy the rest of the nodes -->
<xsl:template match="comment() | text() | processing-instruction()">
<xsl:copy />
</xsl:template>
</xsl:stylesheet>

You can also use an online tool when you don’t have Visual Studio available and you want to test an XSLT. This is possible on http://xslttest.appspot.com/. You can directly see the result.

<?xml version="1.0" encoding="UTF-8"?><xml>
<salestable segment="1">
<salesid>10</salesid>
</salestable>
<salestable segment="1">
<salesid>20</salesid>
</salestable>
</xml>

In this way it is pretty easy to verify your xml and your XSLT file.

Feel free to contact: jvveldhuizen@to-increase.com.

 

Remove all name spaces on incoming XML files using Connectivity studio

In some cases we import XML files where namespaces are implemented. This is complicating the setup on the XML document in Connectivity. So there is a generic XSLT file to remove the name spaces from the XML file and still keep the attributes available.

You can download the XSLT from here. This can be very helpful to simplify the XML setup.

Feel free to contact: jvveldhuizen@to-increase.com.