Get Error Logs of Process Instance from K2 DB

Currently in K2 5.3, when you are opening the error logs in management site, you might found that you no longer have the search bar to check or search on a specific folio / workflow instance. This could be a daunting tasks if you got a lot of errors from various processes or multiple folio in error state in your environment. You can use the below query to get the details of the error directly from K2 database. Just replace the folio part with your own folio name.

use [k2]
declare @instanceID int = 0
select top 1 @instanceID = ID from  [server].[procinst] where folio = 'MY-Module-20200806-0001'
print @instanceID
select * from [Server].[ErrorLog] where procinstid = @instanceID
Posted in K2, SQL, Workarounds and Tricks | Leave a comment

Update SharePoint SuitBar Text with Powershell

When you open a page in SharePoint 2016, you most likely will see the word SharePoint on the top left of the page. What if you want to change the text to something else?

suitenavbarandingtext1

You maybe use the following SharePoint Powershell sample:

$webApp = Get-SPWebApplication http://sp2016:12121
$webApp.SuiteNavBrandingText = “YoYo”
$webApp.Update()

suitenavbarandingtext2

Posted in SharePoint | Leave a comment

SQL LAG and LEAD Examples

In short: LAG to get previous row data, LEAD to get next row data.

When to use LAG? Let say you have the data of accumulated value from an organisation up to that particular month and you would like to get the amount that the organisation have for each of those months. The function of LAG will get you the accumulated value of previous month for the row of the targeted month. Here’s the sample data:

lagexampletable

Table that show accumulated sales up to that particular month / date

In the sample table shown, you can see that the company and posting date are sorted properly so that you can have a clear picture about it. In real world, you may / have to do your own sorting. Here’s the sample on how you can use LAG function to get previous month accumulated value:

select CompanyName, PostingDate, AccumulatedSales,
LAG(AccumulatedSales, 1, 0) Over (Partition by CompanyName order by CompanyName, PostingDate) as PreviousMonthSales
from LagExample

In the sample query, partition will be responsible to group the value by Company and it is important to know how you data should be sorted especially in the Order By part.

lagresult1

In the first month of every company, the value is 0  because it is default to 0 and no previous month data.

As you can see from the result above, you can compare total of current month with total of previous month which should give you the idea on how to get the amount for this month. Here’s the full query on how to do it together with result after that.

Select *, (AccumulatedSales - PreviousMonthSales) as CurrentMonthSales
from
(select CompanyName, PostingDate, AccumulatedSales,
LAG(AccumulatedSales, 1, 0) Over (Partition by CompanyName order by CompanyName, PostingDate) as PreviousMonthSales
from LagExample) as a
lagresult2

Final result


 

When to use LEAD? Let say you have the data for each month and you would like to show next month data as well in same row for comparison. The LEAD function will get you the data from the next row. Here’s the sample data:

leadexampletable

Table that show monthly allocation

In the sample table shown above, the data this time not properly sorted to reflect the real world scenario. Here’s the sample on how you can use LEAD function to get next month’s allocation and its result:

select CompanyName, BudgetDate, MoneyAllocated,
LEAD(MoneyAllocated, 1, 0) Over (Partition by CompanyName order by CompanyName, BudgetDate) as NextMonthAllocation
from LeadExample
leadresult1

As you can see, you can get the numbers for the following month when properly sorted

 

Posted in SQL | Leave a comment