Convert state codes to state names

[lavacano]

When attempting to map Contact Form 7 data to Odoo's res.partner model, the plugin is incorrectly submitting state codes (e.g., 'WA') instead of the expected state IDs (e.g., 'base.state_us_53') for the state_id field. This results in an "ARRAY types integer and integer[] cannot be matched" error in the Odoo database, preventing successful form submission and data integration.

Steps to Reproduce:

Install and activate the Odoo WordPress plugin. Configure the plugin to connect to your Odoo instance. Create a Contact Form 7 form with a field for the user's state. In the plugin settings, map the state field from Contact Form 7 to the state_id field in Odoo's res.partner model. Submit the form with a valid state abbreviation or name. Observe the error message in the Odoo server logs or the plugin's debugging output. Expected Behavior:

The plugin should correctly translate the state abbreviation or name from the Contact Form 7 submission to the corresponding state_id in Odoo, allowing the data to be inserted into the res.partner table without errors.

Actual Behavior:

The plugin submits the raw state code (e.g., 'WA') instead of the state_id, causing a data type mismatch error in the Odoo database.

Additional Information:

The issue might be related to the way the plugin handles state mapping or its interaction with Contact Form 7's data. The error message observed in the Odoo logs is: ERROR: ARRAY types integer and integer[] cannot be matched. The issue prevents successful integration of Contact Form 7 data with Odoo, impacting lead generation and customer management processes.

odoo.sql_db: bad query: INSERT INTO "res_partner" ("active", "calendar_last_notif_ack", "city", "color", "create_date", "create_uid", "customer_rank", "email", "invoice_warn", "is_company", "is_published", "lang", "message_bounce", "name", "phone", "state_id", "street", "street2", "supplier_rank", "team_id", "type", "tz", "user_id", "write_date", "write_uid", "zip") VALUES (true, '[timestamp]', '[city]', 0, '[timestamp]', [user_id], 0, '[email]', 'no-message', false, false, 'en_US', 0, '[name]', '[phone_number]', ARRAY[ARRAY[6,0,ARRAY[0]]], '[street_address]', '', 0, NULL, 'contact', NULL, NULL, '[timestamp]', [user_id], '[zip_code]') RETURNING "id"
ERROR: ARRAY types integer and integer[] cannot be matched
LINE 1: ... '[name]', '[phone_number]', ARRAY[ARRAY[6,0,ARRAY[0]]]...

Possible Solutions:

The plugin should include a mechanism to correctly map state names or abbreviations to their corresponding state_id values in Odoo. The plugin should validate and transform the state data before submitting it to Odoo to ensure data type compatibility. Workarounds:

Manually map state codes to state_id values in the Contact Form 7 submission data before sending it to Odoo. This might be the better solution as we will have to sanitize phone numbers, email addresses, and zipcodes.

[Jack-Dane]

@lavacano, thanks for the detailed issue report.

This is a known limitation of the plugin, you can see this example for relation fields uses the ids. If this was to be implemented it would be implemented for all relation fields not just the state.

I think it would be possible to compute the ids based on the names, but it gets more complicated with custom models. But I think there is one main limitation being the amount of requests needed to compute this information.

I think the plugin would need to:

1. Query the ir.model.fields models to get the meta data on the field and find the related model.
2. Make a query to the model to get the ID using the name (or potentially a custom set) field.

At minimum it would be a total of number of fields + 1 extra request to get the field meta data.

I don't know if the hook for contact form 7 that does the form submission runs as part of the main page load. If it does then this implementation could slow down the form submission leading to bad UX. This will have to be checked as part of the implementation.