Calling M3 Web Services with SQL adapter in Smart Office Mashup

Once in a while I receive this question of how to call an M3 Web Service (MWS) with SQL adapter in a Mashup for Infor Smart Office. As a reminder, MWS has three adapters: M3 API, M3 Display Program (MDP), and SQL (JDBC). Each will return a different SOAP response, thus each will need a slightly specific XAML. The trick with the SQL response is the new0Collection.

Here is my sample SQL:

SELECT OKCUNO, OKCUNM
FROM MVXJDTA.OCUSMA
WHERE OKCONO=? AND OKSTAT=?

1b

Here is the web service in MWS Designer (note the new0 result set in the output):
4_
6b

Here is the Web service test tool in Smart Office (note the new0Collection):
8_

Here is the resulting Mashup:
10b

And here is the final XAML source code with the new0Collection binding highlighted:

<Grid xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:ui="clr-namespace:Mango.UI.Controls;assembly=Mango.UI" xmlns:mashup="clr-namespace:Mango.UI.Services.Mashup;assembly=Mango.UI">
	<Grid.Resources></Grid.Resources>
	<Grid.ColumnDefinitions>
		<ColumnDefinition Width="*" />
	</Grid.ColumnDefinitions>
	<Grid.RowDefinitions>
		<RowDefinition Height="*" />
		<RowDefinition Height="Auto" />
	</Grid.RowDefinitions>
	<mashup:DataListPanel Name="CustomerList" Grid.Row="0">
		<mashup:DataListPanel.Events>
			<mashup:Events>
				<mashup:Event SourceEventName="Startup" TargetEventName="list" />
			</mashup:Events>
		</mashup:DataListPanel.Events>
		<mashup:DataListPanel.DataService>
			<mashup:DataService Type="WS">
				<mashup:DataService.Operations>
					<mashup:DataOperation Name="list">
						<mashup:DataParameter Key="WS.CredentialSource" Value="Current" />
						<mashup:DataParameter Key="WS.Wsdl" Value="https://host:26108/mws-ws/SIT/TestSQL?wsdl" />
						<mashup:DataParameter Key="WS.Address" Value="https://host:26108/mws-ws/SIT/TestSQL" />
						<mashup:DataParameter Key="WS.Operation" Value="LstCustomers" />
						<mashup:DataParameter Key="WS.Contract" Value="TestSQL" />
						<mashup:DataParameter Key="LstCustomers1.CONO" Value="750" />
						<mashup:DataParameter Key="LstCustomers1.STAT" Value="20" />
					</mashup:DataOperation>
				</mashup:DataService.Operations>
			</mashup:DataService>
		</mashup:DataListPanel.DataService>
		<ListView Name="Customers" ItemsSource="{Binding new0Collection}" Style="{DynamicResource styleListView}" ItemContainerStyle="{DynamicResource styleListViewItem}">
			<ListView.View>
				<GridView ColumnHeaderContainerStyle="{DynamicResource styleGridViewColumnHeader}">
					<GridView.Columns>
						<GridViewColumn Header="Customer" DisplayMemberBinding="{Binding Path=OKCUNO}" />
						<GridViewColumn Header="Name" DisplayMemberBinding="{Binding Path=OKCUNM}" />
					</GridView.Columns>
				</GridView>
			</ListView.View>
		</ListView>
	</mashup:DataListPanel>
	<ui:StatusBar Name="StatusBar" Grid.Row="1" />
</Grid>

That’s it!

Published by

thibaudatwork

M3 Technical Consultant

15 thoughts on “Calling M3 Web Services with SQL adapter in Smart Office Mashup”

  1. Hi Thibaud,

    your collection will get exactly the name of the resultset that you define in LWS/MWS; if you don’t define a name, if will be “new0” by default. If you call it “Result”, it will be “ResultCollection” for your binding. So defining a meaningful name in LWS/MWS when you create your web service enables you to use a meaningful name in your Mashup 🙂

    /Heiko

    Like

  2. sorry ,, i put that question in another wrong place .. it would be Logic to be here

    thanks again and again for these helpful Posts ,,, i want to know how to Call WS (which retrieves one row and two columns as a result ) with a button and then write the first Column in a Text Field on the Grid… here is my code
    i tested the WebService with the Tool and it is Working well

    when i write that Code …
    Label Grid.Row=”2″ Grid.Column=”1″ Name=”result” Content=”{Binding Path=Data, ElementName=detail}”

    the label Takes value with the click ” getItemNameResponseType”


    if we wanted to apply that on the Example above ,,, i want to know how to Display only the First Row from the result of the Web Service Using DataPanel Not DataListPanel ,,to put these Values in a text Fields on the Grid

    Thanks again and regards

    Like

    1. Hello Zaher, thank you for the feedback on the blog. It looks like your code got stripped away by WordPress, you have to escape your XML code before posting it in WordPress. As for your question, you have to use XPath to get the desired value. Use the Web Service Tool in Smart Office to test it. /Thibaud

      Like

  3. UPDATE: I didn’t copy/paste the usual disclaimer about executing SQL against M3 database: 1) executing SQL INSERT/UPDATE/DELETE will void your Infor Support warranty, 2) executing SQL SELECT will cause locking issues and will race with the SmartCache. I forgot #3.

    Like

  4. Hi,
    I would after Add !

    but the box is displayed only the result of webservice is selected. Its possible to Display on TextBox, the result directly
    thanks ; for your response

    Like

    1. Yes you can show the result in a TextBox right away, but only if the result is a single record, or if you hard-code which record, otherwise the Mashup can’t tell which of the records you want unless you select it.

      Like

  5. Excuse me , the
    TextBox Name=”CUST_Text” Text=”{Binding ElementName=Customers, Path=SelectedItem.OKCUNO}”

    Like

  6. Hi all. I am looking for a method of writing the results of a web service call into a custom list. I am convinced it can be done, and am sure I have done it before, but I cannot find my code. I can product the results in a list, but I am looking to use the functionalities of the custom list (standard functions/related options). Thanks.

    Like

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s