Pages

2013/11/21

Linked Saved Searches: Many-To-Many Relationships and Results Drilldown

NetSuite offers powerful and easy-to-use search tools. Whether saved searches are used to generate custom reports, list views or even portlet views, NetSuite's sophisticated filtering criteria and results formatting options are only available and tailored to be performed on a single record type. 

Unfortunately for some users this restriction might pose a constraint since a search can only be performed on a particular record and one level joins. For those that are more familiar with SQL, this will translate to having the ability to perform Cross Joins, Natural Joins, Inner Joins, Left (Outer) Joins and Right (Outer) Joins but being constrained to One to One and One to Many Relationships relational database models.

Although at first glance it might seem like Many-To-Many relationships are not supported using the standard searching tools, there is always the option of re-creating this relational database model using advance scripting techniques, which provide an effective and an alternative to this limitation. However, for some users this is not an option, and luckily there is alternative to this solution, which only requires advance and intermediate SQL Query Techniques. The following blog post will guide you through a quick example which will allow you to achieve a Many-To-Many Relationship report using Saved Searches and Formula Fields.

For the purpose of this example, we will be following the use case below and we will only be covering 2 saved searches drill down.

Use Case:
Mary, a system administrator has been asked to produce a report that shows the number of times her company employees have edited/created/deleted transaction records. Mary's boss has asked her boss to make sure the transaction types are grouped as well as the employees as this report will be used in a dashboard view. Can Mary accomplish her required tasks using Saved Searches?

Step 1
Identify your tables/records 

The most important step is to identify the records (tables) that you will be searching. Once you have listed these, the next step is to determine the link (secondary key) in each table. Tip: Creating an ERD to depict the relationships can come in handy!

Our use case states that we will need to be looking at the following records:

Employee : To retrieve a list of all the employees that have made modifications to a transaction(s).

Transaction : To retrieve all the transactions in the system to be reported on.

System Notes : Although this is not a visible record, this record can be access through saved searches as it allows the system to log all actions performed to other records.

(Note: Keep in mind that special permissions are required to perform a search on system notes. See your system administrator if you do not have this permissions).

Step 2
Identify the entity-relationship model and map this relationship out



First start by identifying the relationship with the highest hierarchy. In other words, the record/table that contains the link between both of your records. This record will be the one that will define your Saved Search Type.

Once that we have established the relationship between these entities, we need to now break down this many to many relationship before we start creating our first saved search. Remember, we need to find a starting point therefore we need to create a "Cross Reference Table/Junction" table to break down the relationship between these two.

Step 3
Create your "Cross Reference/Junction Table" to break down the many to many relationships.




Identify the primary keys and then proceed on creating a new table containing both of your Many-To-Many Tables Primary Keys (Main Identifiers). 

Step 4
Create your saved searches and link them together!

1 - Define your saved search search criteria

Once you have identified the records you will be using, identify the number of searches that you will need to create in order to produce your desired output. In this case, if we take a look at the use case and the screenshot, the total number of required drill downs 4; therefore, the number of saved searches that you will be creating will be equal to 4.
Saved Search #1 - Start Point -  Main Saved Search (Grouped by User)
This saved search will be the starting point which will contain all the users in the system.
Saved Search # 2 - User Drill Down View (Grouped by Transaction Type)
This saved search will show all the transactions grouped by type given the user selected in Level 2.
Saved Search # 3 - Transaction Drill Down By Type and ID
This saved search will show a specific transaction type and its related records given the user selected in Level 2 and the transaction type selected in Level 3.
Saved Search # 4 - Final Transaction Drill Down Details
This level shows a specific transaction record and a summary of all the changes logged by the system by the user selected in level 2.

Once you have created all your saved searches your next step will be to link each of them with a formula text.

Step 5
Create your concatenated string to be inputted as a formula field in your saved searches!

Start from the lowest level to the highest. Run your last saved search, and copy the url found in your browser. This URL will be inputted as a formula text in the preceding saved search. In this case Saved Search #3.

You will get something like this:
https://system.na1.netsuite.com/app/common/search/searchresults.nl?searchtype=Transaction&Transaction_TYPE=VendBill&SN_SystemNote_DATEmodi=WITHIN&SN_SystemNote_DATErange=ALL&SN_SystemNote_DATEfrom=&SN_SystemNote_DATEfromrel_formattedValue=&SN_SystemNote_DATEfromrel=&SN_SystemNote_DATEfromreltype=DAGO&SN_SystemNote_DATEto=&SN_SystemNote_DATEtorel_formattedValue=&SN_SystemNote_DATEtorel=&SN_SystemNote_DATEtoreltype=DAGO&SN_SystemNote_DATE=ALL&SN_SystemNote_NAME=2555SN_SystemNote_TYPE=ANY&sortcol=SN_SystemNote_RECORD_raw&sortdir=ASC&csv=HTML&OfficeXML=F&pdf=&style=NORMAL&report=&grid=&searchid=Your_3rd_Search_Id

If we separate each URL component and only pick the one we are interested on, in this case our employee name we will obtain the following:
SN_SystemNote_NAME=2555

Now the next step is to point this parameter to the primary key (field name) instead of pointing it to a specific employee id. See the example below:

SN_SystemNote_NAME=', CONCAT({name.id}, '

Your now that you have edited your URL, the next step is to create a string that will create a hyperlink to link Search #3 to Search #4. Your final result should look like this:


CONCAT('<a href="https://system.na1.netsuite.com/app/common/search/searchresults.nl?searchtype=Transaction&Transaction_TYPE=&SN_SystemNote_DATEmodi=WITHIN&SN_SystemNote_DATErange=ALL&SN_SystemNote_DATEfrom=&SN_SystemNote_DATEfromrel_formattedValue=&SN_SystemNote_DATEfromrel=&SN_SystemNote_DATEfromreltype=DAGO&SN_SystemNote_DATEto=&SN_SystemNote_DATEtorel_formattedValue=&SN_SystemNote_DATEtorel=&SN_SystemNote_DATEtoreltype=DAGO&SN_SystemNote_DATE=ALL&SN_SystemNote_NAME=', CONCAT({name.id}, '&SN_SystemNote_TYPE=ANY&sortcol=Transction_FORERIC14_raw&sortdir=ASC&csv=HTML&OfficeXML=F&pdf=&style=NORMAL&report=&grid=&searchid=Your_3rd_Search_Id">View Detail</a>'))

You will need to repeat this step for every saved search, keep in mind that you will be linking these searches together so that the end result will follow this structure:
Saved Search #1 - Start Point -  Main Saved Search (Grouped by User)
- Will contain a link to saved search #2

Saved Search # 2 - User Drill Down View (Grouped by Transaction Type)
- Will contain a link to saved search #3

Saved Search # 3 - Transaction Drill Down By Type and ID
- Will contain a link to saved search #4

Saved Search # 4 - Final Transaction Drill Down Details
-Will contain the final output to be shown.


Step 6
Create your first saved search

Now that we have defined our start point, we can proceed on creating our first Saved Search, which will very similar to the table created in Step 3. This Saved Search will also be performed on System Notes, as we cannot create a Saved Search without specifying a record type.
NOTE: You can also create a custom record to map this table

Step 7
Add your saved search to your Dashboard!


No comments:

Post a Comment