Here is the solution:
The requirement needs to be achieved only using Out of the Box solution. And SPD workflow does not give any feature to scan multiple list items.
Since MOSS 2007 SPD Workflow has very few actions, we can not achieve the whole solution using SPD Workflow. So we need to populate the List Item so that most of the calculation is done in the List Form instead of workflow.
ECM Master List:
- CType(Not able to store Type named column)
- Report Date
Note: Match String is a string by which I will check CType, Action, Month and Year at one shot.
Create Custom New Form which will auto populate the CurrentMonth, Prev1Month and Prev2Month.
- Create a Custom New Form: Go to SharePoint Designer and Epand the Lists > ECM Master List
- Copy the NewForm.aspx and Paste it in the same location and rename it as CustomNewForm.aspx
- Open CustomNewForm.aspx by double clicking on it.
- Select the Web Part and Delete it
- Now Go to Insert > SharePoint Controls > Custom List Form and Click it
- Select 'ECM Master List' and Content Type as 'Item' and Select 'New item form' and click on 'OK' button
- Now in the code find the oneIDListForm and put the following reference
- Now Select one of the OK Button. And put the following code on it's top
<input type="button" id="btnSave" value="Save" onclick="fnSave()" />
Need to populate the Script.js file
- Upload jquery-1.11.1.min.js and Script.js in the Shared Documents > Scripts. Scripts is a folder that needs to be created in Shared Documents. Script.js will contain the following code
//TO DO: Get the Report Date input ID from View Source
var tmpReportDate = $('#ctl00_m_g_158fbd09_e200_4454_b40b_8ce5475ff4fc_ff4_1_ctl00_ctl00_DateTimeField_DateTimeFieldDate').val()
var tmpDate = new Date(tmpReportDate.split('/'),tmpReportDate.split('/'),tmpReportDate.split('/'));
var prev1year = ((tmpDate.getMonth() - 1) = 10)
//Save in the Format Of: Year-Month-Type-Action
tmpCurrent = tmpDate.getFullYear()+'-'+tmpDate.getMonth()+'-'+tmpprevType+'-'+tmpprevAction;
//Prev 1 Month
tmpPrev1 = prev1year+'-'+prev1month+'-'+tmpprevType+'-'+tmpprevAction;
//Prev 2 Month
tmpPrev2 = prev2year+'-'+prev2month+'-'+tmpprevType+'-'+tmpprevAction;
//TO DO: Get the CurrentMonth input ID from View Source
//TO DO: Get the Prev1Month input ID from View Source
//TO DO: Get the Prev2Month input ID from View Source
//TO DO: Get the OK Action from View Source
if (!PreSaveItem()) return false;
WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions('ctl00$m$g_158fbd09_e200_4454_b40b_8ce5475ff4fc$savebutton2$ctl00$diidIOSaveItem', '', true, '', '', false, true));
- Now to put the actuaul values in the TO DO tagged element, browse
- Right Click on the page and click on the View Source
- Seacrh with CType and get the ID of the input CType and put it in the Script.js file.
- Get the ID for Report Date, Action, Risk, CurrentMonth, Prev1Month and Prev2Month, Put it in the Script.js
- Now Search with PreSaveItem and get the function and put it in the Script.js file. But replace the quot as shown in the Script.js above.
Go to SharePoint Desgner and Open the Site.
Click File > New > Workflow
Name the workflow and select the list as ECM Master List. Check the check boxes as shown below
Click Next button
Click on the 'Varaibles..' and add the following two variables
Click on the OK Button
Click Action > Set Workflow Variable
Set tmpPrev1ID to as shown below. I am setting the ID of that list item which's CurrentMonth is equal to tmpPrev1ID. If item is not available it will save 0 in the tmpPrev1ID.
Set tmpPrev2ID to as shown below.I am setting the ID of that list item which's CurrentMonth is equal to tmpPrev2ID. If item is not available it will save 0 in the tmpPrev2ID.
It will looks as follows:
Now add another step
Now add a condition as follows:
if tmpPrev1ID Not Equals o and if tmpPrev1ID Not Equals o
And in the Action create the item in your list.
http://<Site URL>/Lists/ECM%20Master%20List/CustomNewForm.aspx and create the items as follows: