InfoPath 2010–How to Concatenate Multiple Fields, some of which are Blank?
- I have the name of a person in 5 separate fields (Yeah, this is usually the case in the Arab World as there are names that may include spaces). FirstName, SecondName, ThirdName, FourthName and FamilyName
- First, Second and Family names cannot be blank, whereas Third Name and Fourth Name can be blank.
- Need to concatenate all these fields into a single field in InforPath 2010. If the field is not blank, we need to include it and follow it with a space. If the field is blank, we don’t want to include it and we don’t want to add a space.
- I don’t want to use code, as the form needs to run in a browser with as low privileges as possible.
- Although InfoPath provides a Calculated Value control, that control’s XPath properties do not provide an IF or IIF statement, nor an ISBLANK or ISNULL function that we can use to check whether a field is blank.
In order to reach a no-code solution, here’s what I thought I should do:
Add a Calculated Value control to the form.
Right-Click on the control and select Calculated Value Properties.
In the XPATH field enter the following (the code may scroll to the next line. Copy and paste it in a text editor to avoid copying errors):
translate(concat(FirstName, " ", SecondName, " ", xdMath:Nz(my:ThirdName), " ", xdMath:Nz(my:FourthName), " ",FamilyName), "0", "")
Make sure that Format as Text is selected. Click OK. Now your form includes a control that will achieve the above results.
Explanation & Assumptions:
- We’re using the nz() function to replace every null field with a ‘0’. This will make sure that if ThirdName or FourthName are blanks, they will be replaced with a ‘0’.
- Since names cannot include numbers, it is safe to assume that by replacing ‘0’s in the text with blanks will not lose any data.
- We’re also using the translate() function to replace every ‘0’ (if any) with a null string.
Worked for me. If you have a better suggestion, please post. Otherwise, please feel free to use this post.