InfoPath Dev

I have a data connection for an email submit. Below is the formula I have in the subject line. The Field that says "Value" is a list filed that is populated on the form by choosing a check box and the "Journal Accounting Date" is a calculated field. These should up correctly in my SharePoint List, but nothing shows up for these 2 fields in the subject line of the email that is generated, all other fields display correctly.

 

concat(RegionContractGroup, " ", Value, "EMEA Journal Entry ", Journal Accounting Date, " ", JobDescription)

I'd love to see a copy of your form if possible - you can attach it under the options tab in a reply. One way to test this would be to put a calculated value on the form with the same formula so you can see the result prior to submitting to email.

Hi, Thanks for responding.

 

The fields are on the form and populating.

 

I attached the form.

 

Tony 

Looks like the Value is from a multi-select. You might try adding a filter to that - see the attached screenshot - so that instead of getting the first value, which is what your formula currently does, you get the first non-blank value. I also see the formula refers to the Journal Accounting Date, not the Journal Date. You might want to temporarily put the Journal Accounting Date field on the form so you can see the value. Since it is calculated, and this is a list form, I think there is a chance the calculation isn't occurring until the data is in the list...

 ok the filter worked, except that I just grabs the 1st choice if there are two or more they do not show. is that an easy fix?

for the accounting date . In the form I am using a date picker, but I need the date to show as MMMM YYYY, because the day of the month chosen isn't relevant and we don't want it shown on the list or the email subject. but that isn't a format that is available for share point lists which is why a created the calc field accounting date.

 

 

 

You can use what is called a double eval formula to concatenate all of them with a divider. You can use substring formulas for the date - but you won't be able to get MMMM format, just the numbered month.... of course, that's easy enough to fix with a secondary data source that has the month names & numbers.... I'll put together a sample for you later today that demonstrates that. Here are some links: http://tinkerersjournal.blogspot.com/2013/01/codeless-double-eval.html for double eval, https://blogs.elon.edu/techgeeks/2013/08/15/infopath-extract-day-month-or-year-in-infopath-from-a-date-picker-field-or-today/ for date parts, though I would likely skip the number function on these.

 ok, thanks and so very much appreciated!

Ran out of time today on the month / year sample - I'll do that tomorrow, I promise. Were you able to try the double eval for your value field?

See attached, save locally, right click and select Design, then you can preview

Got the eval to work except that I want "&" instead of | | and I only need the & between the 2 not after 

here is the formula I have so far without any date 

concat("LEASE JE_", JobDescription, "_", RegionContractGroup, "_Portfolio(s):", eval(eval(Value[. != ""], 'concat(., " & ")'), "..")) 

 this is the output

 LEASE JE_Monthly Journal Batch_EMEA\Equipment and Fleet_Portfolio(s):EMEA & EMEA FTPC &

 

Name[Number = substring(field1, 6, 2)]

having trouble with the above.  I've got the "journal date" as the field name for where the date value lands when you chose a date with the picker and I have the calculated field "journal accounting date" to show the date as "MMMM YYYY"

 

how do I write your formula with those field names? right now your formula only shows MMMM, can I add YYY?. what do the 1,6,2 numbers represent in your formula?

 

 

 I'm using a substring formula to get back just the month from the date so that I can match that to the month number in my XML data source to return the name of the month. If I want to get the year from a date field, I can do that with a formula like this:

 substring(field1, 1, 4)

 So, if I wanted the Month name and the year, something like this works:

  concat(Name[Number = substring(field1, 6, 2)], " ", substring(field1, 1, 4))

so I substituted field 1 with Journal Date since that's the name of my field with where the date picker holds data. what do I use for Name[Number? what do the 6,2 and 1,4 represent in your formula? concat(Name[Number = substring(Journal Date, 6, 2)], " ", substring(Journal Date, 1, 4))

In my form there is a secondary data connection to an XML file that has the Month names. If you look at the formula with the XPath showing, you'll see:

xdXDocument:GetDOM("month")/Months/Month/Name[../Number = substring(xdXDocument:get-DOM()/my:myFields/my:field1, 6, 2)]

Attach a copy of your form to a reply if you'd like help setting that up. 

I have attached the form. Thanks.

Try this version.