How can I tie a recurring instance to the instances that result from it?

[former_member184995]

I have seen this question a couple times during my tenure here and it came up again today.

The customer was interested in finding the SI_ID of a recurring instance that he just created. When I inquired as to why he said he would like to be able to find the recurring instance and any instances that result from it.

There are a couple things that you need to know when trying to do this.

People have a misconception that a recurring instance is kind of like a parent object and that each time the instance is scheduled to run it creates "child" instances that are tied to it.  This is not the case. 

A recurring instance is just a pending instance that has the unique ability to create a new instance when it runs.  The result of this is that when you create a recurring instance the SI_ID of that recurring instance will end up being the SI_ID of the next instance it creates and NOT the SI_ID of the recurring instance through its lifetime.  This means that after each run the recurring object has a different SI_ID and nothing ties the recurring instance to the instances that resulted from it in the past.

In the BO Enterprise product as it currently sits there is no definitive way of finding which instances were created by a specific recurring instance. You can get a pretty good guess by looking at schedule times and such, but due to the potential volume of recurring instances and objects it would make it almost impossible to do easily.

I went over this with the customer (my thanks to Yuri Goron at APOS Systems!) and we discussed a way to get around this by using a custom property. This works quite well and in the following code I show how to accomplish this.

How to tie a recurring report with the resulting instances in VB.NET
Imports CrystalDecisions.EnterpriseImports CrystalDecisions.Enterprise.DesktopPartial Class _Default    Inherits System.Web.UI.Page    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        Dim boEnterpriseSession As EnterpriseSession        Dim boEnterpriseService As EnterpriseService        Dim boSessionMgr As New CrystalDecisions.Enterprise.SessionMgr()        Dim boInfoStore As InfoStore        Dim boReportObjects As InfoObjects        Dim boReportObject As InfoObject        Dim boReport As Report        Dim boSchedulingInfo As SchedulingInfo        Dim query As String        'Log into BusinessObjects Enterprise        boEnterpriseSession = boSessionMgr.Logon("administrator", "", "localhost", "secEnterprise")        'Create the infostore object        boEnterpriseService = boEnterpriseSession.GetService("", "InfoStore")        boInfoStore = New InfoStore(boEnterpriseService)        'Create query to get desired report        query = "Select * From CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report' AND SI_NAME='World Sales Report' AND SI_INSTANCE=0"        boReportObjects = boInfoStore.Query(query)        'check for returned reports        If boReportObjects.Count > 0 Then            boReportObject = boReportObjects.Item(1)            boReport = CType(boReportObject, Report)            'Create an interface to the scheduling options for the report            boSchedulingInfo = boReport.SchedulingInfo            'create the parameters for our recurring schedule            'run first instance 1 minute from now to allow us to create the new custom property            Dim currentTime As Date = Now.AddMinutes(1)            'recurrance lasts for 10 min            Dim endTime As Date = currentTime.AddMinutes(10)            'Set the report to run on a recurring basis every minute            'running for 10 minutes            With boSchedulingInfo                .BeginDate = currentTime                .EndDate = endTime                .IntervalHours = 0                .IntervalMinutes = 1                .Type = CeScheduleType.ceScheduleTypeHourly            End With            'Schedule the report            boInfoStore.Schedule(boReportObjects)            'Get the SI_ID of the resulting scheduled object            Dim id As Integer = boReportObject.Properties("SI_NEW_JOB_ID").ToString            'Create query to get scheduled report            query = "Select * From CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report' AND SI_ID=" & id            boReportObjects = boInfoStore.Query(query)            boReportObject = boReportObjects.Item(1)            'Adding a custom property to put the SI_ID of the original recurring instance            'into to tie all of the instances to the recurring instance            'Now you can query for the recurring object and grab the OriginalRecurringID and use it for            'your instance queries            boReportObject.Properties.Add("OriginalRecurringID", id)            'Commit to the infostore            boInfoStore.Commit(boReportObjects)            Response.Write("The '" + boReport.Properties("SI_NAME").ToString + "' report will run every minute for 10 minutes")        Else            'no objects returned by query            Response.Write("No report objects found by query ")            Response.Write("Please click here to return to the logon page.")        End If        'Logoff of Enterprise        boEnterpriseSession.Logoff()        boEnterpriseSession.Dispose()    End SubEnd Class

When you use this method you can then query with something like

"Select si_name , si_mycustomproperty from ci_infoobjects where si_recurring = 1"

 to get the recurring instances and their custom property value.  You then query for

"Select * from ci_infoobjects where si_recurring = 0 and si_instance = 1 and si_mycustomproperty =  'the value from the previous query'"

This will return all of the instances that were created by the recurring instance.

I hope this was helpful and good luck with your projects!

Jason