Change database field names?

API Q&A
1

We have two related companies, with much of the administration shared between them. And for a variety of good reasons they have two different Infusionsoft databases. We are working on automating some of our work practices using the Infusionsoft API. It’s reasonably practical to have two instances of the automation process, with different Infusionsoft keys and URLs; but it would need two versions of the software if it had to cope with different field names - and that’s something we have to avoid.

Unfortunately our two databases were set up by different people, and fields that have the same titles and do the same things in each database have different underlying names.

It’s technically possible to change a database field name: How do I rename a column in a database table using SQL? - Stack Overflow . I assume we would need to change the corresponding entry in the “field-name-map-to-title-and-position” table.

Is it possible to do this in Infusionsoft?

  • Charles
2

Hi @Charles_Weir,

It’s more like table name masking but the only place there is a way for an app owner to do this is found under Admin → Settings and then clicking on application:

image
image934×561 45.3 KB

3

Thanks for the response, but alas I think that’s a misunderstanding of the requirement. I’m hoping to change the database field name for user-defined fields (e.g. from ‘_nameOfContactsDog’ to ‘_nameOfDog’), not the field titles (‘Name of dog’). Is there a way to do that?

  • Charles
4

We do not support currently column renaming in the Infusionsoft Application.

I would suggest an intermediary mapping layer so that you can abstract the column names and access them based on the system in question, rather than accessing the column by name directly.

1 Like
5

Ah, ok so you mean custom field specifically.

The DataFormField table may be modified to accomplish that:

In the table schema docs the DataFormField will allow editing of existing custom fields and there is an api method for adding new fields.

https://developer.infusionsoft.com/docs/table-schema/

6

Ah - thank you @John_Borelli and @TomScott. I see in the DataFormField table that the Name field is read-only, so this wouldn’t support changing it. And indeed it wouldn’t be any use to do so unless we could do
ALTER TABLE Contacts RENAME COLUMN _nameOfContactsDog TO _nameOfDog;
(or an equivalent for the correct type of SQL DB) as well.

So that sounds like a no.

But we could create the new fields, use a script to copy all the values over from the old ones, and then delete the old fields. In SQL the copy script would be merely:

UPDATE Contacts SET _nameOfDog = _nameOfContactsDog;

Is it possible to ask Infusionsoft admins to run a set of such scripts?

  • Charles
7

Hi @Charles_Weir, unfortunately renaming the database column or mass updating data is not something we’re able to do for you. However, you could use a combination of DataService.query and DataService.update to pull the values from your current custom field, change the field name, and update those same records to put the value in the new custom field.

Alternatively this could also be achieved in the UI by using the option to modify existing records found under Admin > Data Cleanup. You could export your contacts with the existing custom field, update that column in the CSV to be the new custom field name, and then use this tool to mass update your contacts.

8

Brilliant! Thank you @Nicholas_Trecina!