InfoPath Dev

So I'm pulling in a standard SharePoint list with one of the fields containing a date. I also have a 'Unique ID', and what I am trying to do is display the date from a specific record (matching the Unique ID) - but with 30 days added to it.

So if I do 'addDays(MyExternalListDateField, 30)' for example, that works - but of course without a filter it's only going to call back the first item returned. So when I say it works, I mean that Infopath accepts it as a valid formula.

 If I then try 'addDays(MyExternalListDateField[. = UID], 30)' (obviously I'm putting this in pseudo-code here for clarity) then Infopath tells me that "Function 'AddDays' did not return a value, or it returned a value that cannot be converted to an XSL data type."

 Not being able to do this has put a complete spanner into the works for what I was trying to achieve! Any pointers/ suggestions on how to get around this?

 

Thanks.

Could you put MyExternalListDateField[. = UID] on a calculated value to see if the filter is returning the value you expect it to return?

Maybe there's something to fix in the filter?

Hi - thanks for responding.

I've tried that just now, and yes it returns the value I expected.

Worth noting that the error happens as I'm building the formula, rather than it simply not giving me what I want/expect at runtime.

Could it be related to the data type of the field? It's set in SharePoint as a date field, and within Infopath using just an expression box it gives the full date-and-time format, ie '2012-11-15T00:00:00' - could it be the inclusion of the time that's making it fail?

I'm thinking not, because if I just run addDays on the field without a filter, it correctly adds my 30 days.

Interestingly, elsewhere on the same form I am doing something similar - and it works! In other other scenario the key difference seems to be that I'm filtering based on the content of a local field in my form. So basically it;s like:-

addDays(ExternalDateField[NameField = LocalFormField]) --------and that works just fine. However if instead of comparing it to the content of a field I just put a text value of "Joe Bloggs", it brings back the afore-mentioned error.

I can usually figure out a workaround to things like this, but in this case I'm not sure if I'm dealing with something I'm doing wrong or something Infopath isn't doing right!

There are a couple of relevent factors I should mention.

The formula I am using is for a button label - although the same error occurs for a calculated value box.

Also this is inside of a repeating section bound to a sharepoint list data connection, and when it queries the date field, that's from a different sharepoint list data connection.

(Update: actually the same error occurs with a calculated value box outside of the repeated section as well)

So, I just tried this - I added a caclulated value on a form, and used addDays with the field I'm adding to from a secondary data connection (a SharePoint list). I added a filter - that another field in my SharePoint list must be equal to a field in my form. I did not get an error from the formula editor. But I think you are saying that your filter uses a field from the 2ds you are using for the formula, and then compared to a field in another 2ds? Can you tick the Edit XPath box and post the full XPath for the formula you are trying?

Oh wow - never mind what I just said - while the formula editor is not complaining, my form won't open in preview - when I preview I get the same error you were getting!

So - here's my workaround. Add a field to the main data source. Set its default value to a calculated value - that would be the filtered XPath - MyExternalField[. = UID] or what have you.

Then, in the calculated value I was testing with, I used addDays with my new field (the one with the default value).... does that work in your circumstances?

Hi Hilary - thanks for your time :)

Your workaround is exactly what I came up with too, but there's a slight problem with it, namely that I am trying to display a different value on every row of my repeating section.

In essence the external list is a list of meetings held. What I wanted to display is each meeting date plus a number of days - thus the 'next meeting due'.

So a field in the main data connection wouldn't help as I need to do the same calculation for each row.

 

What I tried instead was creating an additional collumn in my data source and using that field as a kind of dummy to hold my filtered value, and then as you suggested, use the addDays function on the ready-filtered value. The problem then is how to set it. I tried setting it as a rule on the section, so it will run as each row is added, but it doesn't seem to execute until I refresh the connection.

Oh, okay - these details are helpful. I think you need to repurpose another field in your secondary data connection - if you aren't using the ID from the list for anything, you could use it. Put a rule on that field to set a field's value, and use that rule to set each instance of your current dummy field in the repeating group to the filtered value. After your data loads initially (whether it is on a rule, form load, what have you) you can set ID to 0 - since ID will never be 0, this rule will set all instances to 0 (causing each instance to change values and fire the rule to set the dummy field with the filtered value). Does that make sense?

It makes perfect sense, and what's more it worked perfectly!! Thank you so much - I shall remember this technique!