In the past I’ve seen a few threads asking about creating custom dropdown lists using information from Excel tables.
None of the queries I saw quite gave the full answer though, so I thought I’d give a brief explanation of the steps I went through to do this.
My starting point was some information from a variety of sources - a list of names of UK local authorities and their websites. I aimed to combine the name and website address into a single list item for easy reference.
First of all I created the dropdown with a small number of items in to define the format to be copied.
After a bit of searching I located the two files that held the text I needed to add to in: suitecrm/custom/include/language
The files in my case were:
en_GB.lang.php
and
en_us.lang.php
If you use other languages in your setup, then this may vary.
Copy these files to a local location and make sure you have a backup of them.
The data was fairly easy to locate and the structure of it was fairly simple, with each line representing one row of data. I used Notepad++ to edit the data, but for this exercise any text editor will do, although if you have one with syntax highlighting turned on, it may help you spot errors.
Before I could combine the data in Excel, I noticed a few problems with special characters, in particular apostrophes such as in King’s Lynn. As it was a fairly small set of data, I could have fixed this manually, but I prefer to create a solution that I can re-use for other purposes in future if required, so created two new columns to represent the sanitised text for the labels and the descriptions (D & E in the screenshot).
For the name, I wanted to remove any non alphanumeric characters and replace the spaces with underscores as well as capitalising initial letters as this is how I have formatted other lists that I previously entered manually.
There is no way to quickly replace multiple characters by formula in Excel, so I instead used a VBA routine that I came across previously.
So create a VBA module, press + in Excel, which opens the project editor. Scroll down to your file and right click and choose “insert module” from the list.
Paste the following into the window that appears.
Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, toStr As String) As Variant
' Created by Juan Pablo González
' with ideas from Aladin Akyurek
'toStr is assumed to be the same length of frStr. If not, the remaining characters
'will be considered as null ("").
'Note that this function IS case sensitive. To replace all instances of "a" you need
'to use "a" AND "A"
'You can't replace one character with two characters. This
'=MSUBSTITUTE("This is a test","i","$@")
'will result in this:
'"Th$s $s a test"
Dim iRow As Integer
Dim iCol As Integer
Dim j As Integer
Dim Ar As Variant
Dim vfr() As String
Dim vto() As String
ReDim vfr(1 To Len(frStr))
ReDim vto(1 To Len(frStr))
For j = 1 To Len(frStr)
vfr(j) = Mid(frStr, j, 1)
If Mid(toStr, j, 1) <> "" Then
vto(j) = Mid(toStr, j, 1)
Else
vto(j) = ""
End If
Next j
If IsArray(trStr) Then
Ar = trStr
For iRow = LBound(Ar, 1) To UBound(Ar, 1)
For iCol = LBound(Ar, 2) To UBound(Ar, 2)
For j = 1 To Len(frStr)
Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j))
Next j
Next iCol
Next iRow
Else
Ar = trStr
For j = 1 To Len(frStr)
Ar = Application.Substitute(Ar, vfr(j), vto(j))
Next j
End If
MSUBSTITUTE = Ar
End Function
Once I’d done this, I created a formula in D4:
=SUBSTITUTE(PROPER(msubstitute(B4,"(){}[]|\-'_!@#$%:;^&/.<>*#.",""))," ","_")Msubstitute removes any special characters. Proper changes the initial letter of each word to uppercase. Substitute swaps the spaces for underscores.
The description column was easier - in E4 I used the formula:
=SUBSTITUTE(B4,"'","\'")&" - "&C4This adds a slash before the apostrophes to make it valid in PHP code and then adds a hyphen to the end of the name before adding the website URL.
Finally, I needed to combine the two together to replace the existing PHP code.
In F4 I entered the formula:
=" '"&D4&"' => '"&E4&"',"If you want to make sure you get it correct, you can paste in the elements surrounding the text from the original PHP file itself into the formula.
Fill these three columns down, then select the contents of the final column and past it into the file in your text editor in the relevant position where the dropdown list is.
Upload these files to their original location on the website.
That’s it.
Hopefully it helps someone else in future.