How to add filters to a ListView in a Mashup

What is the XAML code needed to add filters to a ListView in a Mashup in Lawson Smart Office? The solution is to put a StackPanel with a Label and a TextBox inside the GridViewColumn:

<GridViewColumn>
    <StackPanel>
        <Label Content="Location" Foreground="White" />
        <TextBox Name="WHSL" />
    </StackPanel>
</GridViewColumn>

Here is a screenshot of a desired M3 program (for example: MWS060/B) with filters in the list (in this case: Receiving number, Location, and Lot number); we want to reproduce that list and its filters as a Mashup:

And here is the resulting Mashup with the filters in the list; the result is surprisingly similar to the actual M3 panel:

The particular Mashup shown in the screenshot above calls a REST Web Service made with JSP and which is not provided here. The Mashup passes the values of the filters as parameters of the Web Service in the URL. The Web Service executes SQL against M3 using JDBC and returns the result set as XML. Finally, the Mashup displays the resulting XML in the ListView as data in columns and rows. Here’s the full XAML code of that Mashup:

<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" Margin="10">
	<Grid.Resources>
	</Grid.Resources>

	<Grid.ColumnDefinitions>
		<ColumnDefinition Width="*" />
	</Grid.ColumnDefinitions>
	<Grid.RowDefinitions>
		<RowDefinition Height="Auto" />
		<RowDefinition Height="*" />
	</Grid.RowDefinitions>

	<StackPanel Orientation="Horizontal" Grid.Row="0">
		<Label Content="Warehouse" VerticalAlignment="Center" Margin="3" />
		<TextBox Name="WHLO" VerticalAlignment="Center" Margin="3" />
		<Label Content="Item number" VerticalAlignment="Center" Margin="3" />
		<TextBox Name="ITNO" VerticalAlignment="Center" Margin="3" />
		<Label Content="Container" VerticalAlignment="Center" Margin="3" />
		<TextBox Name="CAMU" VerticalAlignment="Center" Margin="3" />

		<Button Content="Apply" IsDefault="True" VerticalAlignment="Center" Margin="3" Width="55">
			<Button.CommandParameter>
				<mashup:Event TargetName="WebService" TargetEventName="List">
					<mashup:Parameter SourceKey="WHLO" Value="{Binding ElementName=WHLO, Path=Text}" />
					<mashup:Parameter SourceKey="ITNO" Value="{Binding ElementName=ITNO, Path=Text}" />
					<mashup:Parameter SourceKey="CAMU" Value="{Binding ElementName=CAMU, Path=Text}" />
					<mashup:Parameter SourceKey="REPN" Value="{Binding ElementName=REPN, Path=Text}" />
					<mashup:Parameter SourceKey="WHSL" Value="{Binding ElementName=WHSL, Path=Text}" />
					<mashup:Parameter SourceKey="BANO" Value="{Binding ElementName=BANO, Path=Text}" />
				</mashup:Event>
			</Button.CommandParameter>
		</Button>
	</StackPanel>

	<mashup:DataListPanel Name="WebService" Grid.Row="1" Margin="3">
		<mashup:DataListPanel.Events>
			<mashup:Events>
				<mashup:Event SourceEventName="Startup" />
			</mashup:Events>
		</mashup:DataListPanel.Events>
		<mashup:DataListPanel.DataService>
			<mashup:DataService Type="REST">
				<mashup:DataService.Operations>
					<mashup:DataOperation Name="List">
						<mashup:DataParameter Key="REST.BaseAddress" Value="http://hostname/ItemSearchMashup.jsp?WHLO={WHLO}&amp;ITNO={ITNO}&amp;CAMU={CAMU}&amp;REPN={REPN}&amp;WHSL={WHSL}&amp;BANO={BANO}" />
						<mashup:DataParameter Key="REST.RemoveNamespace" Value="True" />
						<mashup:DataParameter Key="REST.XPath" Value="root/r" />
					</mashup:DataOperation>
				</mashup:DataService.Operations>
			</mashup:DataService>
		</mashup:DataListPanel.DataService>
		<ListView Name="Customers" ItemsSource="{Binding Items}" Style="{DynamicResource styleListView}" ItemContainerStyle="{DynamicResource styleListViewItem}">
			<ListView.View>
				<GridView ColumnHeaderContainerStyle="{DynamicResource styleGridViewColumnHeader}">
					<GridView.Columns>
						<GridViewColumn Header="Whs" DisplayMemberBinding="{Binding XPath=MLWHLO}" />
						<GridViewColumn DisplayMemberBinding="{Binding XPath=MLREPN}">
							<StackPanel>
								<Label Content="Recvng no" Margin="0" Foreground="White" />
								<TextBox Name="REPN" Margin="0" />
							</StackPanel>
						</GridViewColumn>

						<GridViewColumn Header="Item number" DisplayMemberBinding="{Binding XPath=MLITNO}" />

						<GridViewColumn Header="Name" DisplayMemberBinding="{Binding XPath=MMITDS}" />
						<GridViewColumn Header="Supplier" DisplayMemberBinding="{Binding XPath=LMSUNO}" />
						<GridViewColumn DisplayMemberBinding="{Binding XPath=MLWHSL}">
							<StackPanel>
								<Label Content="Location" Margin="0" Foreground="White" />
								<TextBox Name="WHSL" Margin="0" />
							</StackPanel>
						</GridViewColumn>

						<GridViewColumn DisplayMemberBinding="{Binding XPath=MLBANO}">
							<StackPanel>
								<Label Content="Lot number" Margin="0" Foreground="White" />
								<TextBox Name="BANO" Margin="0" />
							</StackPanel>
						</GridViewColumn>

						<GridViewColumn Header="On-hand" DisplayMemberBinding="{Binding XPath=MLSTQT}" />
						<GridViewColumn Header="Allocatble" DisplayMemberBinding="{Binding XPath=Allocatble}" />
						<GridViewColumn Header="Sts" DisplayMemberBinding="{Binding XPath=MLSTAS}" />
						<GridViewColumn Header="M dt" DisplayMemberBinding="{Binding XPath=LMMFDT}" />
						<GridViewColumn Header="Exp dt" DisplayMemberBinding="{Binding XPath=LMEXPI}" />
						<GridViewColumn Header="Sls dt" DisplayMemberBinding="{Binding XPath=LMSEDT}" />
					</GridView.Columns>
				</GridView>
			</ListView.View>
		</ListView>
	</mashup:DataListPanel>

</Grid>

That’s it!

Published by

thibaudatwork

M3 Technical Consultant

8 thoughts on “How to add filters to a ListView in a Mashup”

  1. Here’s the ItemSearchMashup.jsp used in this Mashup:


    [ANGLE_BRACKET]%@ page import="java.io.*, java.sql.*, javax.xml.parsers.*, javax.xml.transform.*, javax.xml.transform.stream.*, javax.xml.transform.dom.*, org.w3c.dom.*, org.xml.sax.*, org.xml.sax.helpers.*, javax.xml.parsers.*, javax.xml.transform.*, javax.xml.transform.stream.*, javax.xml.transform.sax.*" %>[ANGLE_BRACKET]%
    response.setContentType("application/xml; charset=utf-8");
    Driver d = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:sqlserver://hostname:1433;databaseName=M3EDBTST", "userid", "password");
    Statement stmt = con.createStatement();
    String sql = "";
    sql += "SELECT\n";
    sql += "MITLOC.MLWHLO,\n"; // Whs -- Warehouse
    sql += "MITLOC.MLREPN,\n"; // Recvng no -- Receiving number
    sql += "MITLOC.MLITNO,\n"; // Item number -- Item number
    sql += "MITMAS.MMITDS,\n"; // Name -- Name
    sql += "MILOMA.LMSUNO,\n"; // Supplier -- Supplier
    sql += "MITLOC.MLWHSL,\n"; // Location -- Location
    sql += "MITLOC.MLBANO,\n"; // Lot number -- Lot number
    sql += "MITLOC.MLSTQT,\n"; // On-hand -- On-hand balance approved
    sql += "(MLSTQT - MLALQT) AS 'Allocatble',\n"; // Allocatble -- Allocatable on-hand balance
    sql += "MITLOC.MLSTAS,\n"; // Sts -- Status - balance ID
    sql += "MILOMA.LMMFDT,\n"; // M dt -- Manufacturing date
    sql += "MILOMA.LMEXPI,\n"; // Exp dt -- Expiration date
    sql += "MILOMA.LMSEDT\n"; // Sls dt --Sales date
    sql += "FROM\n";
    sql += "MVXJDTA.MITLOC,\n";
    sql += "MVXJDTA.MILOMA,\n";
    sql += "MVXJDTA.MITBAL,\n";
    sql += "MVXJDTA.MITMAS\n";
    sql += "WHERE\n";
    sql += "MILOMA.LMCONO=MITBAL.MBCONO AND\n";
    sql += "MITBAL.MBCONO=MITLOC.MLCONO AND\n";
    sql += "MITLOC.MLCONO=MITMAS.MMCONO AND\n";
    sql += "MILOMA.LMITNO=MITBAL.MBITNO AND\n";
    sql += "MITBAL.MBITNO=MITLOC.MLITNO AND\n";
    sql += "MITLOC.MLITNO=MITMAS.MMITNO AND\n";
    sql += "MILOMA.LMBANO=MITLOC.MLBANO AND\n";
    sql += "MITBAL.MBWHLO=MITLOC.MLWHLO AND\n";
    sql += "MITLOC.MLWHSL=MITBAL.MBWHSL\n";
    // Warehouse
    String WHLO = request.getParameter("WHLO");
    if (WHLO != null && WHLO.trim().equals("") == false) {
    sql += "AND MBWHLO='" + WHLO + "'\n";
    }
    // Item number
    String ITNO = request.getParameter("ITNO");
    if (ITNO != null && ITNO.trim().equals("") == false) {
    sql += "AND MBITNO='" + ITNO + "'\n";
    }
    // Container
    String CAMU = request.getParameter("CAMU");
    if (CAMU != null && CAMU.trim().equals("") == false) {
    sql += "AND MITLOC.MLCAMU='" + CAMU + "'\n";
    }
    // Recvng no
    String REPN = request.getParameter("REPN");
    if (REPN != null && REPN.trim().equals("") == false) {
    sql += "AND MITLOC.MLREPN='" + REPN + "'\n";
    }
    // Location
    String WHSL = request.getParameter("WHSL");
    if (WHSL != null && WHSL.trim().equals("") == false) {
    sql += "AND MITLOC.MLWHSL='" + WHSL + "'\n";
    }
    // Lot number
    String BANO = request.getParameter("BANO");
    if (BANO != null && BANO.trim().equals("") == false) {
    sql += "AND MITLOC.MLBANO='" + BANO + "'\n";
    }
    ResultSet rs = stmt.executeQuery(sql);
    // convert to XML
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document doc = builder.newDocument();
    Element root = doc.createElement("root");
    doc.appendChild(root);
    Element node;
    while (rs.next()) {
    Element result = doc.createElement("r");
    node = doc.createElement("MLWHLO"); node.appendChild(doc.createTextNode(rs.getString("MLWHLO").trim())); result.appendChild(node);
    node = doc.createElement("MLREPN"); node.appendChild(doc.createTextNode(rs.getString("MLREPN").trim())); result.appendChild(node);
    node = doc.createElement("MLITNO"); node.appendChild(doc.createTextNode(rs.getString("MLITNO").trim())); result.appendChild(node);
    node = doc.createElement("MMITDS"); node.appendChild(doc.createTextNode(rs.getString("MMITDS").trim())); result.appendChild(node);
    node = doc.createElement("LMSUNO"); node.appendChild(doc.createTextNode(rs.getString("LMSUNO").trim())); result.appendChild(node);
    node = doc.createElement("MLWHSL"); node.appendChild(doc.createTextNode(rs.getString("MLWHSL").trim())); result.appendChild(node);
    node = doc.createElement("MLBANO"); node.appendChild(doc.createTextNode(rs.getString("MLBANO").trim())); result.appendChild(node);
    node = doc.createElement("MLSTQT"); node.appendChild(doc.createTextNode(rs.getString("MLSTQT").trim())); result.appendChild(node);
    node = doc.createElement("Allocatble"); node.appendChild(doc.createTextNode(rs.getString("Allocatble").trim())); result.appendChild(node);
    node = doc.createElement("MLSTAS"); node.appendChild(doc.createTextNode(rs.getString("MLSTAS").trim())); result.appendChild(node);
    node = doc.createElement("LMMFDT"); node.appendChild(doc.createTextNode(rs.getString("LMMFDT").trim())); result.appendChild(node);
    node = doc.createElement("LMEXPI"); node.appendChild(doc.createTextNode(rs.getString("LMEXPI").trim())); result.appendChild(node);
    node = doc.createElement("LMSEDT"); node.appendChild(doc.createTextNode(rs.getString("LMSEDT").trim())); result.appendChild(node);
    root.appendChild(result);
    }
    rs.close();
    stmt.close();
    // print XML
    javax.xml.transform.TransformerFactory tfactory = TransformerFactory.newInstance();
    javax.xml.transform.Transformer xform = tfactory.newTransformer();
    javax.xml.transform.Source src= new DOMSource(doc);
    StringWriter writer = new StringWriter();
    Result result = new javax.xml.transform.stream.StreamResult(writer);
    xform.transform(src, result);
    out.println(writer.toString());
    %>

    Like

  2. nice work and thanks for posting!

    it would be great if F4 functionallity was available in the Filters (Warehouse/Item/Container). Any idea of how to achieve this?

    thanks!

    Like

    1. Hi George. Thank you for the feedback. You can handle the F4 key with a script (easiest), or with Smart Office SDK, or perhaps also directly in XAML but I don’t know how to. There is an example of F4 script in the M3MashupBrowse script that is probably already available in your jscript folder, and also in the Infor Salesportal somewhere; otherwise just search on MSDN for handling key events.

      Like

  3. Hello, I have a simple CRS610 Mashup..I want to add a filter in my mashup to display only the Status “20” Customers. How do I do it?

    Justin Lopes

    Like

    1. Hi Justin. This blog post is about how to add filter on the user interface (XAML), with the assumption you already have a filter on the M3 program itself. In your case, I don’t remember if CRS610 already has a filter for status (STAT). If not, you will need to create a custom View (right-click on the View’s dropdown list), or query Infor Enterprise Search, then add the filter to the XAML. /Thibaud

      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 )

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