Smart Notifications merged rows and merged columns

make sure the datatype of the two linking columns are the same and that the column names are the same, note this is case sensitive.

Thanks for the reply Matthew.
So if one infoset (mssql data source) has 1700 records of just usernames and another infoset (oracle data source) has 5 records with username, processlevel, department, last_name, first_name, emp_status, can I merge the two infosets together to pull any of the 5 records where their username is in the 1700 usernames? Note that all 5 records might not be in the 1700 record infoset.

Ive actually never tried to use merge in this way but ii dont think this is going to do what you want. This will essentially take the matching rows and join the columns into one dataset. Its equivalent to a SELECT * with an INNER JOIN. I believe. be curious to hear what you discover.

I am trying to do something very similar but can't figure out how to combine columns from 2 different infosets onto 1 line.

I have an infoset with employee, employee email, supervisor ID, supervisor email (built using SQL). (1700 employee records)
I have a 2nd infoset built using DME that finds employees who have a Commercial Driver License that will expire in 60 days or less. (16 employee records)

I want to combine information from the 2 datasets to get a listing of the employees whose drivers license will expire in 60 days, consisting of employee, license number, expiration date, employee email, supervisor name, supervisor email. This should be 16 lines.

I can build a notification that shows all of the employees with an expiring license (16 lines) then below that shows all of the employees , email, supervisor, etc. (1700 lines)

How do I create a notification with 1 line for each employee with an expiring license along with email and supervisor info? (16 lines total).

David/Glenn,

I havent actually tried the merged columns but the concept is the same with Merged Rows and the trick with the latter was always to be sure the linked columns have the same data type and column names are the same. Also, trimming can be a problem. Im not sure there is a function in DME that will allow you to do this but be sure to do your trimming in your SQL statement.

What results are you seeing when you do it now?

If this isnt working there are two alternate ways you can accomplish this, its a bit advanced so let me know if you need clarification.

The first is done using the Notification (it also tends to be difficult to implement).

>In Step 2 of your InfoSet assign a category to your EmployeeID. Id suggest not using Employee/Login as this is used for filtering if your LBI installer created the EMPLOYEE.LOGIN Personal Data Value. You can use it just dont be worried when you see EMPLOYEE.LOGIN as a selectable value when defining your Muli-Fact in the Notification and be sure NOT to select it. Keep in mind Categories are empty shells so it doesnt matter which one you select but rather how you use that Category in your notification that defines its usage.
>Repeat the above for your 2nd InfoSet. Im making the assumption that Employee is the joining field.
>Go to Step 1 of your Notification
>Edit your 1st Multi-Fact, under the Employee field, tick the checkbox to define Employee as a Parameter
>Select and Add your Multi-Fact
>Repeat the 2 previous steps for your 2nd Multi-Fact
>Go to Step 5 of you Notification, Click "More Details"
> You should see two rows under "Parameters"
>In the drop down for "Supplied By" of the first Multi-Fact, you should see the name of the other InfoSet. Select it.
>Save and test

Same principals apply here. Data Type and Column name of the joining field must be the same.

The third option is the one that I prefer because its a much more controlled and documented solution. However, it kind of subverts the SN infrastructure by leveraging undocumented functionality.

>On Step 4 of the InfoSet check "This is a large data set (>20000 records). In effect this will create a physical table in your SmartNotifications repository
>Create a Data Source that connects to your SN repository (most likely LawsonSN database)
>In "Working with InfoSets" and click on the URLs link next to each of the infosets. There is a listing of URLs and in each is a 4 digit number. Copy this number and be sure you know which belongs to which InfoSet
>Open each InfoSet and take note of the column order in Step 2. Converting columns to a 0-based index
>now using the afore mentioned Data Source to the SN repository, you can create an InfoSet and query your other InfoSets using SQL. A basic query would look something like this:

SELECT COLUMN_0, COLUMN_1, COLUMN_2 FROM INFOSET_1010

>Now you can join your two InfoSets using SQL. Even if you dont end up using this solution it can also serve as a way to trouble shoot how the data is being translated into SN and perhaps you can find the issue of why the first two solutions arent work.

hth,
Matt

Matt

Thanks for the methods above. They work great. I am hoping to build a "library" of infosets that our power users could use to construct Notification without my having to write custom SQL for each notification.

I was able to use the "Supplied By" parameter in your first suggestion to pull the correct rows from both infoset. However I end up with 2 separate lists on the note. I need to end up with a singe row for each enmployee with the appropriate data from both infosets on the row.

I tried the LawsonSN method and got exactly what I want. It will still require a little bit of SQL work on my side, but no where near the amount required to construct a custom query for each notification from the ground up.


Below are the steps I went through:



DataSources are:

JDBC connection to Lawson
DME connection to Lawson
JDNI connection to LawsonSN (determined name from SmartNotes Config Assistant->Repository->JNDI Name)

InfoSets

> Name: CDL Renewal 30-60 days out
>Type: Lawson S3 DME
>Data Source Name: COHP9 DME
DME Query: http://server/cgi-lawson/...ODE~CDL%26RENEW-DATE>=[date=+DS30 fmt=yyyyMMdd]%26RENEW-DATE<[date=+DS60 fmt=yyyyMMdd]%26EMPLOYEE.EMPLOYEE!=&OUT=CSV&NOHEADER

>Step 4 of the InfoSet check "This is a large data set (>20000 records).
>URL: id=1109

> Name: Supervisor Email COHP9
>Type: Relational Database (JDBC)
>[Check]Use Predefined Data Source
>DataSource Name: COHP9 JDBC
>Step 4 of the InfoSet check "This is a large data set (>20000 records).
>URL: id=1105

>Query String:
select emp.employee empl,emp.first_name emplfirst,emp.last_name emplast,emp.email_address emplemail,emp.supervisor emplsuper,sup.employee supempl,
supemp.employee supempempl,supemp.first_name supempfirst,supemp.last_name supemplast,supemp.email_address supempemail
from employee emp, hrsuper sup, employee supemp
where
emp.supervisor = sup.code
and supemp.employee = sup.employee
order by emp.employee


> >Name: CDL Expiration w Empl and Super email
>Type: Relational Database (JDBC)
>[Check]Use Predefined Data Source
>Data Source Name: LawsonSN

>Query string:
select code.COLUMN0 Employee ,code.COLUMN1 License,code.COLUMN2 Expiration,code.COLUMN3 Number, empl.COLUMN1 First, empl.COLUMN2 Last, empl.COLUMN3 Email, empl.COLUMN6 SuperID, empl.COLUMN7 Super_First, empl.COLUMN8 Super_Last, empl.COLUMN9 Super_Email
from infoset_1109 code, infoset_1105 empl
where code.column0 = empl.column0


Created notification using InfoSet: CDL Expiration w Empl and Super email as Range: Selected All for all fields.

 

In order to make the Supplied By solution work, in your template youd need to suppress one of the tables or select Do Not Show on Step 4 for the one you didnt want the recipient to see but as you pointed out, you are trying to give your end users the ability to create their own Notifications and this would really complicate things. Its really cool that youre using SN to allow your users to create their own content. SN was actually first created with this idea in mind as well as subscription based content for users. However, in my 7 years of working with SN, I think Ive come across one client that is doing what youre doing and not one has ever attempted subscription services. Which is not to say it cant be done its just requires a more savvy end user base and a enterprise adoption of SN as a tool set.

Good luck and let me know how it works out.