Sign in to follow this  
Followers 0
sr4uk

Last Month's Date in Array

8 posts in this topic

I have a code that takes InfoPath submitted data stored in a Sharepoint List and formats it in an easy to read Excel doc. The script runs a Sharepoint .iqy file that opens the stored list in Excel. From there I read the data into an array. Each line of the array contains 1 of our client accounts. I search the array to find which client is on which line and use that return to populate the data for that client from it's corresponding excel row. This worked flawlessly for month 1.

Now the .iqy file is pulling in last month's data as well as this months. So I need to add another check into my array search to return the corresponding row.

$Client = _ArraySearch($aArray, "Client") ;Find which line in array (excel) Client is
If $Client = -1 Then
$NonSubmit = $NonSubmit & "Client Name Here, "
Else
Some more code to do stuff here

The code above searches for the client name in the array, if it returns -1 then I know it does not exist and it updates my variable that keeps track of those clients who have not submitted. If it is found, then it returns the row in the array to the variable and I use that to populate the data.

I need to still do this search in the array but need to add additional criteria using date and I've looked in the help file but can't seem to come up with anything. The basic logic should look like this

_ArraySearch($aArray, "Client" and Date = This Month)

Thanks in advance!

Share this post


Link to post
Share on other sites



Here is a copy of the array. Client name is listed under client the date is listed under Created

Posted Image

C:Documents and SettingssrosenhoDesktopscreenshot.jpg

Share this post


Link to post
Share on other sites

$result = MyArraySearch($aArray, "Client1", "20120701174833")

Func MyArraySearch(ByRef $aArray, $client, $date)
For $i = 2 To UBound($aArray) - 1
If $aArray[$i][1] = $client And $aArray[$i][5] = $date Then Return $i
Next
Return -1
EndFunc

This will look at the day and time correct? I will have people submitting on any day from the 1st - 10th of the current month at different times during the day so I can't look at an exact date or time, it has to literally be "any time during the current month"

Share this post


Link to post
Share on other sites

Just do some customization for your needs.

I showed you just example ...

$result = MyArraySearch($aArray, "Client1", "201207")

Func MyArraySearch(ByRef $aArray, $client, $month)
    For $i = 2 To UBound($aArray) - 1
        If $aArray[$i][1] = $client And StringLeft($aArray[$i][5],6) = $month Then Return $i
    Next
    Return -1
EndFunc

Share this post


Link to post
Share on other sites

Just do some customization for your needs.

I showed you just example ...

$result = MyArraySearch($aArray, "Client1", "201207")

Func MyArraySearch(ByRef $aArray, $client, $month)
For $i = 2 To UBound($aArray) - 1
If $aArray[$i][1] = $client And StringLeft($aArray[$i][5],6) = $month Then Return $i
Next
Return -1
EndFunc

Ok thanks. I'll take a look.

Share this post


Link to post
Share on other sites

This did work. I just created a variable $date and gave it the following assignment $date = @year & @mon which is the format the array is in and it works great. Thank you so much for all of your help.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0