Excepted Batch do not contains any data

Blackbaud CRM 

I made a custom batch and the good thing was that it was running fine without any errors, At one day, the client complained that when he got some exceptions in the batch at the time of commit then he was not getting any data in the excepted batch. We all know that whenever any batch committed then if that batch contains exception in any row then that row would not commit and only those rows committed which are successfully pass the validation test. Now, if you would get exception then another batch created and all those rows which contains any error then that row would jump into the excepted batch. 

So my client told me that he was not getting any data in the excepted batch, this was serious concerned for me. So tried to made my research on this issue and somehow with few tricks, i achieved this. 

First you need to create additional parameter in all the Add,Edit and Commit spec which is

"@ORIGINATINGBATCHROWGUID uniqueidentifier = null" . You would also create a formfield in all the three spec

<common:FormField FieldID="ORIGINATINGBATCHROWGUID" DataType="Guid" Hidden="true" />

Then load all the spec. You don’t need to add any business logic based on this ORIGINATINGBATCHROWGUID. Also you need to create this variable in staging table as well, please see the below mentioned line

<ComputedColumnField Description="Originating Batch Row GUID" Name="ORIGINATINGBATCHROWGUID" Expression="ID"/>

Now load all the spec, this ORIGINATINGBATCHID basically contains in OOB BATCH table, please see the below screen shot taken from SQL SERVER .

Picture (A)

 

If you see after adding “@ORIGINATINGBATCHROWGUID” parameter, you would see the above column ORIGINATINGBATCHID gets populated in OOB BATCH table. See the above highlighted rows . First highlighted row identified that this batch row contains exception and this ORIGINATINGBATCHID contains the below highlighted row BATCH ID. This is fine up till now.

We have staging table shown below

Picture (B)

So we need to pick that BATCH ID from PICTURE(A) which contains ORIGINATINGBATCHID not null, because this row contains the exception. So pick that BATCH ID and replace this in staging table so that whenever user will opened the excepted batch then it will open that batch which contains exception. Please see the below trigger which is the deciding point of this complex issue, with the help of this batch, I replaced the BATCH ID in staging table.

create trigger [dbo].[TR_BATCH_COMMITMENT_EXCEPTION_UPDATE] on [dbo].[BATCH] after insert not for replication    as     begin                         if exists (Select 1 from INSERTED where  ORIGINATINGBATCHID is not null)             begin                         declare @NEWBATCHID uniqueidentifier = (select ID from INSERTED);                         declare @ORIGINATINGBATCHID uniqueidentifier = (select ORIGINATINGBATCHID from INSERTED);                                         if exists(select 1 from dbo.USR_BATCHCOMMITMENT where BATCHID = @ORIGINATINGBATCHID)                 begin                                     declare @TempTbl table (                                         [ID] uniqueidentifier)                                                         insert into @TempTbl                                     select BC.ID from dbo.USR_BATCHCOMMITMENT  BC                                     inner join dbo.USR_BATCHCOMMITMENTBATCHSYSTEMMESSAGES BCM on BC.ID = BCM.USR_BATCHCOMMITMENTID                                     where BATCHID = @ORIGINATINGBATCHID                                                                         update dbo.USR_BATCHCOMMITMENT                                     set BATCHID = (select ID from INSERTED)                                     where ID = (select ID from @TempTbl)                         end                            else if  exists(select 1 from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT  where BATCHID = @ORIGINATINGBATCHID)                         begin                                     declare @TempTbl1 table (                             [ID] uniqueidentifier)                                 insert into @TempTbl1                         select BC.ID from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT BC                         inner join dbo.USR_BATCHCOMMITMENTPROGRESSREPORTBATCHSYSTEMMESSAGES BCM on BC.ID = BCM.USR_BATCHCOMMITMENTPROGRESSREPORTID                         where BATCHID = @ORIGINATINGBATCHID                                                 update dbo.USR_BATCHCOMMITMENTPROGRESSREPORT                         set BATCHID = (select ID from INSERTED)                         where ID = (select ID from @TempTbl1)                                                 end             end               end

In above trigger, you would see 2 staging table highlighted in yellow color because I had this issue in 2 custom batches. So I used the checks on this trigger.

Passing default values to data form add In

Blackbaud CRM 

In one of my project, I wanted to add a custom action “Add membership” in action group in left hand 

side, see below image

 If you see in above image, first thing to take the values from this OOB Opportunity page to my Add Membership form. First thing, I had made a expression view data form spec to get the fields from Opportunity page. The values which I needed to pass from this page to Add Membership Add In forms are mentioned below in a form of form field.

<common:FormMetaData>

    <common:FormFields>

      <common:FormField FieldID="PROSPECTID" Caption="Prospect Id" Description="Constituent ID" DataType="Guid"  />

      <common:FormField FieldID="RESPONSEDATE" Caption="Response Date" DataType="Date" Description="Opportunity Response Date"  />

      <common:FormField FieldID="MEMBERSHIPPROGRAM" Caption="Membership Program"  DataType="Guid" />

      <common:FormField FieldID="OPPORTUNITYLEVEL" Caption="Opportunity Level"  DataType="Guid" />

      <common:FormField FieldID="ASKAMOUNT" Caption="Ask Amount "  DataType="Money" />

    </common:FormFields>

  </common:FormMetaData>

  <AlterExistingPages xmlns="bb_appfx_pagedefinition">

    <AlterPages>

      <AlterPage PageID="8b97a047-e267-4e9f-822d-f74cbf1447e5">

        <AddPageExpressionForms>

          <PageExpressionForm DataFormInstanceID="87b33727-dcd1-4dfc-a4da-bad2440adcd3"/>

        </AddPageExpressionForms>

       </AlterPage>

    </AlterPages>

  </AlterExistingPages>

  <common:DataFormExtension DataFormInstanceID="fb0f139d-5242-4375-a579-fbb829e0515f"  RenderStyle="AfterParent" TabCaption="Parent Information" />

If we see in above lines, there are 5 fields included in the form fields which I wanted to pass into Add Membership form and after this I have added Alter existing page in which I have added this expression view data form into Opportunity page and also made this expression view data form as an extension of OOB expression view data form.

After this, next step is to pass these values in Membership Add form. So I used below mentioned code

<AlterPage PageID="8b97a047-e267-4e9f-822d-f74cbf1447e5">

        <AlterPageActionGroups>

          <AlterPageActionGroup PageActionGroupID="08ce3a00-5bc8-475e-bd68-5c5216993845">

            <AlterActions>

              <AddAction>

                <Action ID="14430078-F027-4E5E-8943-A89B24204768" Caption="Add membership" ImageKey="RES:newitem">

                  <common:ShowAddDataForm DataFormID="2852e35e-9b16-4f98-906c-51b7740e1ab4">

                    <common:PostActionEvent>

                      <common:GoToPage>

                        <common:SpecificPage PageID="51dec945-6a5f-4c40-ad4a-dc3ff36cddb4" />

                      </common:GoToPage>

                    </common:PostActionEvent>

                    <common:FormDefaultValues>

                      <common:DefaultValue FieldID="DATE">

                        <common:Value>

                          <common:PageExpressionField>RESPONSEDATE</common:PageExpressionField>

                        </common:Value>

                      </common:DefaultValue>

                      <common:DefaultValue FieldID="BILLTOCONSTITUENTID">

                        <common:Value>

                          <common:PageExpressionField>PROSPECTID</common:PageExpressionField>

                        </common:Value>

                      </common:DefaultValue>

                      <common:DefaultValue FieldID="MEMBERSHIPAMOUNT">

                        <common:Value>

                          <common:PageExpressionField>ASKAMOUNT</common:PageExpressionField>

                        </common:Value>

                      </common:DefaultValue>

                      <common:DefaultValue FieldID="COMMENTS">

                        <common:Value>

                          <common:PageContextID/>

                        </common:Value>

                      </common:DefaultValue>

                    </common:FormDefaultValues>

                  </common:ShowAddDataForm>

                </Action>

              </AddAction>

            </AlterActions>

          </AlterPageActionGroup>

        </AlterPageActionGroups>

      </AlterPage>

In above code, you could see that I have called showadddataform and this form is basically a Membership Add form data form instance id and using default value tags to pass the values. See yellow highlighted area above. I have used some fields from Membership add form like DATE field in Membership add form contains the RESPONSE DATE from opportunity expression view data form, also I passed PROPSECTID to membership add form into BILLTOCONSTITUENTID. I wanted to use similar field data type to pass the values.

Now its turn to discuss the Membership Add In spec, how these values receive at this level. So I made a Membership Add In spec.

<DataFormAddInSpec

       xmlns="bb_appfx_dataformaddin"

       xmlns:common="bb_appfx_commontypes"

       ID="39dfabf1-10d3-407f-961a-ddc9a4e9ca25"

       Name="Membership AddIn Custom"

       Description="An addin for the Membership Dues Add Data Form data form"

       Author="Blackbaud Professional Services"

       DataFormInstanceID="2852e35e-9b16-4f98-906c-51b7740e1ab4"

       >

       <DataFormAddIn AssemblyName="Blackbaud.CustomFx.EventCustomization.Catalog" ClassName="Blackbaud.CustomFx.EventCustomization.Catalog.MembershipAddIn" />

</DataFormAddInSpec>

In Add IN class, see the below code how I got these values.

'Add Membership form default values'

    'Mapping of DATE (Add Membership form) ---> RESPONSEDATEOpportunity page)

    'Mapping of BILLTOCONSTITUENTID(Add Membership form) ---> PROSPECTID(Opportunity page)

    'Mapping of MEMBERSHIPAMOUNT(Add Membership form) ---> ASKAMOUNT(Opportunity page)

    'Mapping of COMMENTS(Add Membership form) ---> ContextId (Opportunity page)

    Private Sub OnInit()

        ' Get Oppertunity Id from Comments field and set it on local variable opportunityId

        ' COMMENT field value set from default value tag and PageContextID set in COMMENT Field

        'which is on Opportunity page

        If Me.COMMENTS.Value <> String.Empty Then

            opportunityId = Guid.Parse(Me.COMMENTS.Value)

            Me.COMMENTS.Value = String.Empty

        End If

        LoadTrackField()

        If (Me.BILLTOCONSTITUENTID.Value <> Guid.Empty) Then

            LoadProgramAndLevel()

        End If

        'This method is called when the UI model is created to allow any initialization to be performed.

    End Sub

If we see above code, we found that based on Membership Add form fields, some business logic implemented. Please see the comments also in green above code snippet. So everything has been done on OnInit event.

For saving the data from Add In , I used below mentioned code

Private Sub MODEL_Saved(sender As Object, e As AppFx.UIModeling.Core.SavedEventArgs) Handles MODEL.Saved

        ' now we should save our custom field value to the database

        ' use Me.MODEL.GetRequestContext to send a request via theappfx

        ' web service to save our dataform extension

        If Me.TRACKTYPECODEID.Visible = True Then

            Dim connection = Me.MODEL.GetRequestContext().OpenAppDBConnection

            'Insert Comp Track Type code under Opportunity Id'

            USR_USP_INSERTMEMBERSHIPTRACKCODE.ExecuteNonQuery(connection, opportunityId, New Guid(Me.MODEL.RecordId), Me.TRACKTYPECODEID.Value, Me.TRACKCOMMENTS.Value)

        End If

    End Sub

    Private Sub MODEL_Saving(sender As Object, e As AppFx.UIModeling.Core.SavingEventArgs) Handles MODEL.Saving

        ' when the form is saving the field we added to the

        ' fields collection will raise an error as it does

        ' not exist in the form definition

        ' let's move the field value to a variable

        Try

            Me._tracktypecodeid.Value = Me.MODEL.Fields("TRACKTYPECODEID").ValueObject

            Me._trackcomments.Value = Me.MODEL.Fields("TRACKCOMMENTS").ValueObject

            ' now remove the field from the fields collection to

            ' prevent field not in form definition error message

            If (Not e.Cancel) Then

                Me.MODEL.Fields.Remove("TRACKTYPECODEID")

                Me.MODEL.Fields.Remove("TRACKCOMMENTS")

            End If

        Catch ex As Exception

        End Try

    End Sub

We would use “Model_Saved” event to save the data into the database, means after normal execution of Membership add form then it will enter into Saved event and we can manage our save criteria.