Mashup – SQL Wildcard / DataGrid with sorting / Copy to Clipboard

Hi,

It’s been a while since I shared something now… I primary spent my time working with mashup combined with SQL… and when I first started there was no turning back.     The same about the DataGrid, its tooo powerful!

Today i’ll share one of probably many ways, how you can perform a wildcard search from a mashup.  I’ll also drop you a few tips when using the DataGrid regarding copying and sorting.

– SQL – WildCard

This solution I provide here now, should give pretty much the exact same results as searching with standard M3.

The use provides an input to a TextBox.

You need to add the following to your mashup to be able to use the IsNotNullOrEmptyConverter

xmlns:utils=”clr-namespace:Mango.UI.Utils;assembly=Mango.UI”

We then use a DataTrigger to provide us a default value if the TextBox is blank, in this case the % to be used in the SQL LIKE(‘%’)  NB: This is a hidden TextBox

<TextBox Name="ItemWhsCheck" Visibility="Hidden">
     			  <TextBox.Style>
      			 <Style x:Key="ItemWhsCheck" TargetType="{x:Type TextBox}" BasedOn="{StaticResource {x:Type TextBox}}">
         			 <Setter Property="Text" Value="{Binding ElementName=ItemWhs, Path=SelectedValue}" />
	        			  <Style.Triggers>
	            		 	<MultiDataTrigger>
	               		 	<MultiDataTrigger.Conditions>
	                   			<Condition Binding="{Binding ElementName=ItemWhs, Path=SelectedValue, Converter={StaticResource IsNotNullOrEmptyConverter}}" Value="False" />
	                			</MultiDataTrigger.Conditions>
	                			<Setter Property="Text" Value="%" />
	            			</MultiDataTrigger>
         		 		</Style.Triggers>
      				 </Style>
    				</TextBox.Style>
  </TextBox>

We should then be able to bind the correct values to the provide it to the SQL.

	<mashup:Parameter TargetKey="ListItems1.MLWHLO" Value="{Binding ElementName=ItemWhsCheck, Path=Text}" />

In the LWS Designer the “?” will be the Input from the mashup.

SELECT MMITNO,MMFUDS from MITMAS  WHERE MMCONO=1  AND TRIM(MMFUDS) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), ‘*’, ‘%’)

This will perform a search which allows exact hits, or multiple hits and the use of multiple wildcards.

Search: O-RING*  Search: O-RING, BS-204 VITON 90  Search- O-RING*204*90

All will provide the following result.  Result: O-RING, BS-204 VITON 90

You can also combine multiple of these.

AND TRIM(MMFUDS) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), ‘*’, ‘%’)  AND TRIM(MMITNO) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), ‘*’, ‘%’)  AND MMITTY LIKE(CAST( ? AS VARCHAR(1000)))  AND TRIM(LISERN) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), ‘*’, ‘%’)  AND MLWHLO LIKE(CAST( ? AS VARCHAR(1000)))  AND TRIM(MLWHSL) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), ‘*’, ‘%’)  AND MLCAMU LIKE(CAST( ? AS VARCHAR(1000)))

DataGrid – Copy to Clipboard

In this example I’m adding a ContextMenu to the DataGrid to allow to copy the line infomration to mail/excel, but you could just aswell use a button .  I added th following extra properties:

The SelectionMode is set to Exteded to allow multiple selections .  The ClipBoardCopyMode is set to IncludeHeaders

<DataGrid Name="dataGrid1" ItemsSource="{Binding ResultSetCollection}" Style="{DynamicResource styleDataGrid}" SelectionMode="Extended" ClipboardCopyMode="IncludeHeader" AutoGenerateColumns="False" SelectedIndex="0" Visibility="{Binding ElementName=SerialRadio, Path=IsChecked, Converter={StaticResource BooleanToVisibilityConverter}}">
			<DataGrid.ContextMenu>
				<ContextMenu>
					<MenuItem Header="Copy to clipboard" Command="ApplicationCommands.Copy" CommandTarget="{Binding ElementName=dataGrid1}" />
				</ContextMenu>
			</DataGrid.ContextMenu>

You should now be able to use the Copy to Clipboard, if using the standard DataGridTextColumns

However, if you would like to use the DataGridTemplateColumn you need to add ClipboardContentBinding or you will be copying blank values.

When using the DataGridTempalte, you also will be losing the standard sorting options, unless you add CanUserSort and SortMemberPath.

The function of this TemplateColumn is just to colour the text red if the overdue value = 1

<DataGridTemplateColumn Header="Next test date" CanUserSort="True" SortMemberPath="NEXTDATE" ClipboardContentBinding="{Binding NEXTDATE}">
					<DataGridTemplateColumn.CellTemplate>
						<DataTemplate>
							<StackPanel Orientation="Horizontal">
								<TextBlock MinWidth="80" MaxWidth="0" Text="{Binding NEXTDATE}">
									<TextBlock.Style>
										<Style TargetType="{x:Type TextBlock}" BasedOn="{StaticResource {x:Type TextBlock}}">
											<Style.Triggers>
												<MultiDataTrigger>
													<MultiDataTrigger.Conditions>
														<Condition Binding="{Binding Overdue}" Value="1" />
													</MultiDataTrigger.Conditions>
													<Setter Property="Background" Value="Red" />
													<Setter Property="Text" Value="{Binding NEXTDATE}" />
												</MultiDataTrigger>
											</Style.Triggers>
										</Style>
									</TextBlock.Style>
								</TextBlock>
							</StackPanel>
						</DataTemplate>
					</DataGridTemplateColumn.CellTemplate>
				</DataGridTemplateColumn>
<DataGridTextColumn Header="Service" Binding="{Binding NEXTSERVICE}" />

This is not the full .xaml ….  But it should contain alot of ideas  I hope its worth sharing.

Here is an example of an end result, and the speed is amazing.

Please take contact if you got any questions, or are willing to share some of your own ideas.
And I also need to thank Heiko for providing solutions when everything looks dark! 🙂

And since I haven’t seen many M3 SQL’s out there…. Here is a copy of the one behind this mashup, i’m no proffessional, so there might be minor errors.

select MMCONO,MMITNO,MMFUDS,MMITTY,LISERN,MLWHLO,MLWHSL,MLCAMU,MMITGR,LISTAT,
(Select A.MPPOPN from MITPOP A where A.MPCONO=1 and A.MPALWT=’4′ AND A.MPITNO=MMITNO fetch first 1 row only) as Partno,
(Select Case When Count(mlbano) > ‘0’ Then ‘*’ Else ” end from mitloc where mlcono=1 and mlcamu=lisern) as CONTAINED,
(Select Case When Count(asnhsn) > ‘0’ Then ‘*’ Else ” end from MROABS where ascono=1 and (asitno=mmitno and assern=lisern) OR (asnhai=mmitno and asnhsn=lisern )) as ATTACHED,

CASE
WHEN QOSTDT is null Then
CASE
WHEN HP.QHSTDT is null THEN NULL
ELSE HP.QHSTDT
END
ELSE
CASE
WHEN HP.QHSTDT is null THEN QOSTDT
ELSE
CASE
WHEN QOSTDT HP.QHSTDT Then HP.QHSTDT
END
END
END as NEXTDATE,

CASE
WHEN QOSTDT is null Then
CASE
WHEN HP.QHSTDT is null THEN NULL
ELSE HP.QHSUFI
END
ELSE
CASE
WHEN HP.QHSTDT is null THEN QOSUFI
ELSE
CASE
WHEN QOSTDT HP.QHSTDT Then HP.QHSUFI
END
END
END as NEXTSERVICE,

CASE
WHEN (days(current date ) > days (to_date(char(QOSTDT),’yyyymmdd’)))
THEN ‘1’
WHEN (days(current date ) > days (to_date(char(QHSTDT),’yyyymmdd’)))
THEN ‘1’
END AS “Overdue”,

(SELECT
CASE WHEN C.MLCAMU ” THEN C.MLWHLO
ELSE C.MLWHLO
END
FROM MITLOC C WHERE C.MLCONO=1 AND A.MLWHLO IN(‘191′,’291’) AND A.MLCAMU=C.MLBANO ) as ContWhs,

(SELECT
CASE WHEN B.MLCAMU ” THEN B.MLCAMU
ELSE B.MLWHSL
END
FROM MITLOC B WHERE B.MLCONO=1 AND A.MLWHLO IN(‘191′,’291′) AND A.MLCAMU=B.MLBANO ) as ContLoc

FROM
MITMAS
JOIN MILOIN ON LICONO=1 AND LIITNO=MMITNO
JOIN MITLOC A ON MLCONO=1 and LIITNO=MLITNO and LISERN=MLBANO
LEFT JOIN MWOPLP ON QOCONO = MLCONO AND QOFACI=’100’ AND QOPRNO = MLITNO AND QOBANO = MLBANO AND QOORTY = ‘SMA’ AND QOPSTS < 59
AND QOSTDT =(SELECT MIN(MP2.QOSTDT) FROM MWOPLP MP2 WHERE MP2.QOCONO = MLCONO AND MP2.QOFACI='100' AND MP2.QOPRNO = MLITNO AND MP2.QOBANO = MLBANO AND MP2.QOORTY = 'SMA' AND MP2.QOPSTS < 59)
LEFT JOIN MMOHED HP ON HP.QHCONO = MLCONO AND QHFACI='100' AND HP.QHPRNO = MLITNO AND HP.QHBANO = MLBANO AND HP.QHORTY = 'SMA' AND HP.QHWHST < 90
AND HP.QHSTDT =(SELECT MIN(HP2.QHSTDT) FROM MMOHED HP2 WHERE HP2.QHCONO = MMCONO AND HP2.QHFACI='100' AND HP2.QHPRNO = MLITNO AND HP2.QHBANO = MLBANO AND HP2.QHWHST < 90)

WHERE MMCONO=1
AND TRIM(MMFUDS) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), '*', '%')
AND TRIM(MMITNO) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), '*', '%')
AND MMITTY LIKE(CAST( ? AS VARCHAR(1000)))
AND TRIM(LISERN) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), '*', '%')
AND MLWHLO LIKE(CAST( ? AS VARCHAR(1000)))
AND TRIM(MLWHSL) LIKE REPLACE(CAST( ? AS VARCHAR(1000)), '*', '%')
AND MLCAMU LIKE(CAST( ? AS VARCHAR(1000)))

Fetch first 500 rows only

Thanks.

Regards

Ken Eric