Invalid Column Headers from Infusionsoft

[dheighton]

There is an open issue (#3) that we (cmwilliamg and I) submitted 2 years ago along these same lines, we used a temporary shunt in the CFML XMLRPC conversion to handle it at the time, but we have now moved to using the Apache XMLRPC jars, and this is no longer an issue for us.

To give a brief overview of the issue, some of the columns in saved reports in Infusionsoft have invalid characters in them for Coldfusion Query objects. This includes numbers and dashes. Not all of these reports can have the names changed inside of Infusionsoft to fix this.

The biggest one for us is the "A/R Aging Summary Report", found in E-Commerce -> Reports. As far as I understand the column names can't be modified here. They include different brackets for ages, "0-30", "31-60","61-90","91+". Coldfusion query columns can not start with a number, nor contain dashes or pluses.

I was going to fix this in the QueryHelper.cfc and submit a pull request (following the proper style of API code this time, heh), but quickly got to a point that I wasn't sure that this was the best place to actually fix this, as anything done on line 18 to fix queryNew had to be taken care of at line 32 when it sets the values.

The gist of what I was doing to fix it was to use a regex expression to replace every non-valid character in the column name with an underscore:

rereplace(loc.fieldsModified,'[!@##$%^&*()/+-]+',"_","ALL");

Then, I would replace every field that started with an underscore, or a number with custom_field

rereplace(loc.fieldsModified,',(\d+)|,_',",custom_\1","ALL");

Thoughts, solutions, ideas? Am I missing something on my mind to resolve this quickly? If you need I will attempt a fully functional fix and submit a request, but like I said not sure what the best way to handle because of how you need to create the query object and then set the values later.