Will do
:
Great! Glad it worked.
If you have issues with your new challenge, it's best to create a new
thread, since it's unrelated to your Excel formula issue, which I assume has
now been resolved. Good luck!
---
S.Y.M. Wong-A-Ton
:
That worked.
My next challange is to populate a new instance of the from with data from
the SP list. Then allow the user to update the data and save the updates as
a new record.
:
If the data type of BrdMtgDate is date, use
BrdMtgDate.SetValue(scndTuesdayDate.ToString("yyyy-MM-dd"));
If the data type of BrdMtgDate is dateTime, use
BrdMtgDate.SetValue(scndTuesdayDate.ToString("yyyy-MM-ddThh:mm:ss"));
For an explanation, see
http://enterprise-solutions.swits.net/infopath2003/date-time-basics-infopath.htm
---
S.Y.M. Wong-A-Ton
:
I have a new issue. As you can see I set value of he field BrdMtgDate to the
scndTuesdayDate which is a string. I want the BrdMtgDate to be formated as a
date. Any suggestions?
:
Here is the code that i used:
EventManager.XmlEvents["/my:ExpRFR/my:Contract/my:BrdMtgYear"].Changed +=
new XmlChangedEventHandler(BrdMtgYear_Changed);
EventManager.XmlEvents["/my:ExpRFR/my:Contract/my:BrdMtgDate"].Changed += new
XmlChangedEventHandler(BrdMtgDate_Changed);
}
public void BrdMtgYear_Changed(object sender, XmlEventArgs e)
{
//// calculate the 2nd Tuesday of the month
// based on the user entering the month and year numerically
//Set Variables
//These variables will be input from the form
XPathNavigator BrdMtgMonth =
this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgMonth", NamespaceManager);
XPathNavigator BrdMtgYear =
this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgYear", NamespaceManager);
XPathNavigator BrdMtgDate =
this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgDate",NamespaceManager);
//int intMonth = 6;
//int intYear = 2008;
//These variables are used in the code
int intDay = 1;
int tuesdayCount = 0;
DateTime scndTuesdayDate = DateTime.MinValue;
//Loop until we have found the second Tuesday
while (tuesdayCount < 2)
{
//Create our test date -- this starts at the beginning of
the month
scndTuesdayDate =
Convert.ToDateTime(Convert.ToString(BrdMtgMonth) + "/" +
Convert.ToString(intDay) + "/" + Convert.ToString(BrdMtgYear));
//See if our test date is a Tuesday
if (scndTuesdayDate.DayOfWeek == DayOfWeek.Tuesday)
{
//If our test date is a Tuesday,
//then up the count of the number of Tuesdays we have
found
tuesdayCount = tuesdayCount + 1;
// and increase the date by a week to find the next
Tuesday
intDay = intDay + 7;
}
else
{
//If out test date is not a Tuesday,
//then increase the date by a day and check it again
//in order to find the first tuesday
intDay = intDay + 1;
}
}
{
//scndTuesdayDate is your return value for the second tuesday of
the month
}
BrdMtgDate.SetValue(scndTuesdayDate.ToShortDateString());
}
public void BrdMtgDate_Changed(object sender, XmlEventArgs e)
{
// Write your code here to change the main data source.
}
}
}
:
No worries. I'll monitor this thread until you solve your issue.
---
S.Y.M. Wong-A-Ton
:
Thanks. You are a great help. I'll digest this and let you know how it
turned out.
:
First you need to retrieve the values from the month and year fields. Check
out this article
http://enterprise-solutions.swits.n...faq.htm#how-to-retrieve-field-value-from-code
if you need code to do it. Here field1 would be either the month or year
field. You can quickly find out what the XPath expression for the month or
year is by going to the "Data Source" pane, right-clicking on the month or
year node, and selecting "Copy XPath".
I don't know if this is the best way, but what I would do is use the values
from the month and year fields to determine how many days are within the
month. You can use DateTime.DaysInMonth (see
http://msdn2.microsoft.com/en-us/library/system.datetime.daysinmonth.aspx)
for this. Once you know how many days are within the month, you can create a
for-loop that loops over the amount of days in the month. For each day in the
loop you can then construct a DateTime object and use its DayOfWeek property
(see
http://msdn2.microsoft.com/en-us/library/system.datetime.dayofweek(VS.80).aspx)
to check whether the day is a Tuesday. If it is, you can increase a counter
(declare and initialize the counter outside the loop). Once the counter has
been increased twice, you must save the last date you checked, break the
loop, and then you have the date for the second Tuesday of the month.
Again, there might be a better/quicker way of calculating it. It is best to
post your question in one of the .NET development newsgroups if you want to
know what the best way is.
---
S.Y.M. Wong-A-Ton
:
I've decided the best route is to use event handler to perform the
calculation that the SP list is being used for. I have a excel formula that
calculates the second tuesday of the month when the user enters the month
(numeric) and the year.
=DATE(Year,Month,1+((2-(2>=WEEKDAY(DATE(Year,Month,1),2)))*7+(2-WEEKDAY(DATE(Year,Month,1),2))))
Do you know the best way to convert this formula to c# with the "Year" and
"Month" being selections from a dropdown or text field?
Alan
:
Someone in the group once proposed a trick to get calculated fields appear in
InfoPath. I myself have never tried it out, but you can give it a go. Read
this thread:
http://groups.google.com/group/micr...=calculated+field+sharepoint#cd6b89a1b037be0d
---
S.Y.M. Wong-A-Ton
:
I already have the calulation working in a SP list. The dropdown list is
pulling it's values from a SP list. In the SP list there are month, year and
a calulated date columns. The calulated date column contains the formula. So
the SP list contains two years worth of calulated dates.
So what I need is to populate a field on the infopath form (or a column in
the form library), with the calulated date, associated with the row
month-year row that the user selected. Any suggestions?
:
Excel is "smart"; InfoPath is not that far yet. It is best to re-create this
formula by writing code, because there is no weekday function in InfoPath and
trying to recreate this formula with what's currently available in InfoPath
would become too complex. Alternatively, if you do not want to write code,
and you have MOSS with Excel Services, and want to re-use formulas in Excel
workbooks, you can do something like I did in this article:
http://enterprise-solutions.swits.n...-difference-infopath-rules-excel-services.htm
---
S.Y.M. Wong-A-Ton
:
I have an excel formula that calulates the second tuesday of the month based
on the user selecting the month (numericly) and the year from a drop down
list.
Use the WEEKDAY and DATE functions as shown in the following formula:
=DATE(B2,A2,1+((1-(2>=WEEKDAY(DATE($B$2,A2,1),2)))*7+(2-WEEKDAY(DATE(B2,A2,1),2))))
I have tried to get the formula to work without success. Does anyone know
of a way to do this.
