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=?
Here is the web service in MWS Designer (note the new0 result set in the output):
Here is the Web service test tool in Smart Office (note the new0Collection):
Here is the resulting Mashup:
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!
M3 ideas 
Note: Also, you should use the technique described in this other blog post to get the Base URI to MWS: https://thibaudatwork.wordpress.com/2012/03/19/how-to-get-the-url-to-lawson-web-services-in-a-mashup/
LikeLike
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
LikeLike
Thank you Heiko!
LikeLike
Hi Heiko. Yes indeed, thank you for the reminder.
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Excuse me , the
TextBox Name=”CUST_Text” Text=”{Binding ElementName=Customers, Path=SelectedItem.OKCUNO}”
LikeLike
If you want to display right-away, without having to select a record, don’t use SelectedItem, use the direct XPath.
LikeLike
Thanks
LikeLike
I forgot to mention the XPath is given by the Web Service test tool in Smart Office. You can also determine the XPath yourself, manually or with any other tool. It’s all standard XML.
LikeLike
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.
LikeLike
Hello,
I’m using a WS SOAP generated by Talend in a mashup ISO M3. The result is a collection, when I tested the WS in SOAPUI, I got the list, but when I used it in the mashup, I got an empty list with this message as a suggestion “if this list has no data then perhaps the resulting object has public fields and no properties ListView binding workes only with properties try and set WS.outputType to DataItem”.
I have tried multiple binding syntax but I couldn’t get the good result, I think that the issue is the declaration of the output as a private field and not as properties.
Did you get please a similar issue or have you an idea about it.
Thank you for your help
LikeLike