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.

ItemSearch

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

6 thoughts on “Mashup – SQL Wildcard / DataGrid with sorting / Copy to Clipboard”

  1. Hi Ken Eric, thank you for sharing your solution. I already discussed this with you offline, and I add it here for our readers: I advise our readers that as a general rule we don’t do direct SQL SELECT on the M3 database (as a reminder we NEVER do SQL CREATE/UPDATE/DELETE), we prefer calling M3 APIs. Unfortunately, sometimes the only solution is to do direct SQL SELECT. In that case, we must take some precautions. 1) We must use indexes to avoid performance penalties. 2) Also, we must use NO LOCK isolation level to prevent blocking M3 tables and freezing/crashing M3 programs. 3) Also, using the LIKE % predicate does a full text scan of the table regardless of the indexes used, which is a huge performance hit. So use it sparingly. In summary, what you do might work in a test database with few rows, but it might get worse over time as the tables grow. So do SQL SELECT thoughtfully. Thanks Ken Eric, and keep sharing your ideas! /Thibaud

    Like

  2. Hi Thibaud, I share some of your worries, but only some of them. Of course SQL statements have to be build very carefully and need to follow existings access paths. And I agree, it may be a difference if you have 100.000 or 100.000.000 records. So a statement like Ken Eric’s may be a problem if the amount of data is very high. But carefully built SQLs are as fast as API’s or other M3 BE programs and don’t cause system performance. Here are some additional thoughts:
    1) It is Infor’s policy to allow read SQLs to the database, but use API’s to write/update/delete.
    2) My experience is that JDBC/ODBC/OLEDB Clients will always get a default isolation level of “Uncommited read” and will never produce locks, if you don’t override the settings in the driver or data source or connection; but I know that 5250 interactive SQL behaves different.
    3) Don’t underestimate the optimization features of modern dbmses which are beeing executed in the background. Often things work fast where you expect it should cause time.
    4) A LIKE operation will never trigger a full scan of a table except you have the wildcard at the first position. If the wild card is somewhere in the middle, the system will use the characters before as selection and then scan only the remaining records. The perfomance depends on the number of records which have to be scanned. If only a few records match the fixed portion of the search string, the LIKE selection can be very fast.
    /Heiko

    Like

  3. Hi everyone,

    Could you please point me to right direction?
    How do I execute SQL in mashups?
    I can’t find any code examples,

    Thanks

    Kirill

    Like

    1. Hi Kirill, sorry I missed your comment. To do SQL in Mashups, you have to wrap it in a REST or SOAP web service; M3 Web Services (MWS) provides that ability. Then use the built-in REST/SOAP web service client in Mashup; the Mashup Designer has a sample. –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 )

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 )

w

Connecting to %s