InfoPath Dev

Hey all,

First time poster long time reader.....I'm developing a capacity planning tool for MS and the date ISO that infopath submits to SQL is YYYY-MM-DD and i'm querying a fiscal year database that will allow me to enter a date from a date picker and return the month, week in the month, fiscal year and quarter. However the date format in the fiscal year database that i'm querying is in the format MM/DD/YYYY.

So when i query it, it returns nothing. I need to change the format that infopath submits the date into SQL so that i can pull out the required information?can someone save me?

Thanks guys

You should be able to change the format of the field. You can even do the conversion manually with string functions and two fields if neccessary.

If you are blocked you could buy a support credit and I am sure we could get it working. http://www.qdabra.com/products_all.asp?cat=12

Thanks for that Clay but got things sorted by doing the following.

I used the SQL CONVERT function to change the datetime format of the date in the Fiscal database in EDIT SQL on the connections wizard. I converted using the following

CONVERT(varchar(10), Perioddate, 120) this converted the date from 14/03/2008 00:00:00 to 2008-03-14 and i was able to query the data and populate the necessary fields.

I have run into some difficulty though cause when i select the date from the picker it populates the fields. Then when i select to create a new row in the repeating table it allows me to select a different date but puts the same values as the one above like its a duplicate field. I don't know how exactly to get around this. Any ideas?

req

That was the other option, conform SQL to the format you need.  Often other people are using the SQL data already and that luxury of changing SQL columns is harder to come by.

It sounds like you either have a default setup or a rule that is populating these date fields.

Check the properties on the repeating table and check the defaults and any rules.

yeah i have a rule in place that when you select the date from the date picker it populates the fields but i need this for every row of the repeating table however i need individual numbers in every row

so for e.g if they select 03/13/2008 it fills the field with qtr = 1 week =3 fiscal year =2008

then when i insert the next row if they select say 09/12/2008 the qtr = 3 week = 2 fiscal year =2009

but sadly it populates it with the first row results. is this not possible using rules?

I am not sure your layout but typically you would use filter to get the date values from a particular row or the current() function to reference the current row.

It is probably one of these two.

If you need specific, live help you can purchase support credits here. http://www.qdabra.com/products_all.asp?cat=12 

Used the current() formula to solve the problem thanks clay....it took a while but i got there